Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Formula Help 1
- Thread starter arvarr
- Start date
SkipVought
Programmer
Much less klunky.
I'd prefer to drive the code from data rather than having the data embedded in the code. I'm giving that some thought. If I get an epiphany, I'll post that approach.
Skip,
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my OLD subtlety...
for a NUance!![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
I'd prefer to drive the code from data rather than having the data embedded in the code. I'm giving that some thought. If I get an epiphany, I'll post that approach.
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
SkipVought
Programmer
I got more 
on face. No excuse for having a bad code
!
COPY n PASTE again. Refund & Invoice in the N/A part.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
on face. No excuse for having a bad code!COPY n PASTE again. Refund & Invoice in the N/A part.
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
- Thread starter
- #63
Agree if we can drive the code from the data rathern than embedded, that would be great.
I have tried the new code and this time, it has reduced the unmatched quite a bit. There are still some records that are still coming up with incorrect output.
Have attached the file.
Thanks,
I have tried the new code and this time, it has reduced the unmatched quite a bit. There are still some records that are still coming up with incorrect output.
Have attached the file.
Thanks,
SkipVought
Programmer
Got things cleaned up.
Be careful of making an seemingly empty cell with a SPACE. That's why the last two outcomes did not appear.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
Be careful of making an seemingly empty cell with a SPACE. That's why the last two outcomes did not appear.
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
- Thread starter
- #65
- Thread starter
- #66
![[2thumbsup]](/data/assets/smilies/2thumbsup.gif "[2thumbsup] [2thumbsup]")
SkipVought
Programmer
Great! Glad to help when I can.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
I realise this is 3 weeks late (I'm using your problem as practice), however the attached is your file, with an added table at cell K1 of the Table sheet. This table is essentially a copy of columns A to E of the same sheet with an added column headed Outcome which contains what I hope you're looking for.
Where there is a blank cell in any of the columns it means include any value from the same column of the Data sheet… except for the Type column which includes only blanks from the Data sheet in that column.
I've added a couple of bits of data to the Data sheet in cells F496 and F1045 to demonstrate what happens when more than one comment exists for a given set of criteria; they're listed.
All you need to do is right-click the new table and choose Refresh.
Where there is a blank cell in any of the columns it means include any value from the same column of the Data sheet… except for the Type column which includes only blanks from the Data sheet in that column.
I've added a couple of bits of data to the Data sheet in cells F496 and F1045 to demonstrate what happens when more than one comment exists for a given set of criteria; they're listed.
All you need to do is right-click the new table and choose Refresh.
SkipVought
Programmer
p45cal said:
The objective is to use the Table data to fill Outcome on the Data sheet, based on 5 criteria.
Is that what "Refresh" does?
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
Ha! No. I cocked up.SkipVought said:
![[rofl]](/data/assets/smilies/rofl.gif "[rofl] [rofl]")
I thought it was to do it the other way round!![[upsidedown]](/data/assets/smilies/upsidedown.gif "[upsidedown] [upsidedown]")
![[laughtears]](/data/assets/smilies/laughtears.gif "[laughtears] [laughtears]")
(The only good thing about it then, is that it could be used to check that the correct procedure or formula works properly!)
It was good educational though. If I get the inclination I'll try doing it the right way round.
![[blush]](/data/assets/smilies/blush.gif "[blush] [blush]")
In the attached, a new table at cell H1 of the Data sheet with the Outcomes column added. You just need to right click a cell in that table and choose Refresh.p45cal said:
I've removed the Comments column and annotations from the table on the Table sheet.
You can update/extend/amend either or both source tables before refreshing the results table at cell H1.
It takes a second or two to refresh and I think it can be speeded up, if so I'll update.
SkipVought
Programmer
Well, the objective is to fill the Data table Outcome column. Your solution, albeit generating correct data, does not fill the Outcome column in the Data table.
However, I'm interested in your solution, using Power Query. Could you explain the steps for arriving at your solution? I've never used this great feature of Excel.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
However, I'm interested in your solution, using Power Query. Could you explain the steps for arriving at your solution? I've never used this great feature of Excel.
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
Easy, see attached.SkipVought said:
However, I have a problem with that, to do with data integrity; The result looks linked because it's in the same block of cells, but these are in fact two independent tables. If you were to sort one, the other wouldn't sort in parallel. It get's worse - if you were to sort the Power Query table, with Excel's default settings for query tables, it would get sorted in the same way immediately after refreshing every time, so the data is not likely to correspond - you can switch that setting off by right clicking the table, choosing Table|Edit Properties, and unticking the box labelled Preserve column/sort/filter/layout, so it's all a bit of a faff.
With the passage of time…SkipVought said:
Edit: I'm struggling to attach a file as before, so here's the link I copied manually:
In the attached, if you go to the Data tab of the ribbon and click the Queries and Connections icon in the Queries and Connections section, a Queries and Connections pane will appear, then if you choose the Queries section of that pane you'll see 5 queries. Only the first 4 are needed.
The DataTable query is short and simple; if you double-click it, the Power Query Editor pops up, and in the Applied Steps section on the right usually the last step is selected. You can click on any step to see what's happened up to that step. The three steps are Source, which just grabs the table from the sheet, then Trimmed Text which removes any trailing and leading spaces from all the cells in all the columns (in the same way as the vba Trim command, it only removes those spaces and doesn't convert multiple spaces between words to single spaces as the worksheet function TRIM does), then the third step changes the data type of the columns to an appropriate type for later processing. That's the Data table spruced up.
Now to do the same, plus a bit, to the other table which I've called Categories. When you bring this up in the Power Query Editor, the third step is Split Column by Character Transition where I've chosen to split the contents of the Department column where the text changes from being any one '=','<','>' to not being one of those three characters and I've named the columns DeptOper and DeptValue for obvious reasons and to make it clearer to us humans what's in those columns when we refer to them later. You can probably gather from the formula/function bar (which you can expand and scroll through just like the regular Excel formula bar) that the bit which decides how the splitting is done is:
The next step, Trimmed Text, trims the text in the DeptValue column since the data in that column mostly had a space between the '<>' and the department name.
The next two steps do much the same thing to the Count and Month columns, while the final step changes the data type of several of the columns. So that's that.
I've put versions of these two tables on the sheet Sheet1 of the attached - that sheet isn't needed so can safely be deleted.
Skipping to the fourth query called Result, this is a very short one consisting of grabbing the earlier prepared DataTable query and invoking a custom function. Dead easy. Of course, the donkey work is carried out by that custom function, so let's have a look at it:
It's called fnFindOutcomes. If you double-click on it to bring it into the PQ Editor you'll be faced with an unhelpful Enter Parameters dialogue. If instead you right-click it and choose Edit you get a similar dialogue and a single step. This is because it's a function. You can see the entire code in the formula bar or you could click on Advanced Editor in the Home tab to see the steps there but you won't see the results of each step as a developing table. You will see the first line which is:
which is how the parameters are passed to this function.
In order to be able to see the developing steps I've duplicated the function in a query called DEVfnFindOutcomes, so if you bring that up in the PQ Editor instead you'll see all the steps. In fact you'll see more steps because the first 5 are just establishing the parameters/arguments manually.
At first opening you'll see just Outcome 29 because that's the final step of this query. If you click on any of the first 5 steps (all beginning 'my'…) you'll see the values used (some strings, some numbers) in the formula bar and you can edit them there, although it's not so obvious how to put an empty string in so it's safer to edit them in the first lines of the code manually by going into the Advanced Editor:
This is the sort of thing I did while developing the function.
The first real step after establishing parameter values is the Source step which just grabs the Categories table from the Categories query. The next ReplacedValue step does something I really should have done in the Categories query and brings in the value from the DeptOper column to the DeptValue column if the DeptValue column is null (there's only one row (3) this happens in).
The next 5 steps, all names ending in …'Filter', filter the table on the 5 parameters, selecting only rows from the table using the built-in function List.AnyTrue() [the StatusFilter step is an exception, it was too simple] which works by returning True if any or more of the elements in the list (in curly brackets{}) is True.
So in that list I put some tests, and if any of the tests return true, that row is kept. In the DeptFilter, CountFilter and MonthFilter steps I separated each element of the list with a carriage return to help me with the logic.
At the end of these filtering steps I may be left with a completely empty table, a table with one row (the ideal) or a table with several rows.
I remove all the other columns except for the outcomes column (called Column1) in step RemovedOtherColumns, convert that into a list in the Column1 step and finally concatenate that list using comma-space as a delimiter to create a string, being the output of the custom function.
The DataTable query is short and simple; if you double-click it, the Power Query Editor pops up, and in the Applied Steps section on the right usually the last step is selected. You can click on any step to see what's happened up to that step. The three steps are Source, which just grabs the table from the sheet, then Trimmed Text which removes any trailing and leading spaces from all the cells in all the columns (in the same way as the vba Trim command, it only removes those spaces and doesn't convert multiple spaces between words to single spaces as the worksheet function TRIM does), then the third step changes the data type of the columns to an appropriate type for later processing. That's the Data table spruced up.
Now to do the same, plus a bit, to the other table which I've called Categories. When you bring this up in the Power Query Editor, the third step is Split Column by Character Transition where I've chosen to split the contents of the Department column where the text changes from being any one '=','<','>' to not being one of those three characters and I've named the columns DeptOper and DeptValue for obvious reasons and to make it clearer to us humans what's in those columns when we refer to them later. You can probably gather from the formula/function bar (which you can expand and scroll through just like the regular Excel formula bar) that the bit which decides how the splitting is done is:
Code:
Splitter.SplitTextByCharacterTransition({"<",">","="}, (c) => not List.Contains({"<",">","="}, c))The next two steps do much the same thing to the Count and Month columns, while the final step changes the data type of several of the columns. So that's that.
I've put versions of these two tables on the sheet Sheet1 of the attached - that sheet isn't needed so can safely be deleted.
Skipping to the fourth query called Result, this is a very short one consisting of grabbing the earlier prepared DataTable query and invoking a custom function. Dead easy. Of course, the donkey work is carried out by that custom function, so let's have a look at it:
It's called fnFindOutcomes. If you double-click on it to bring it into the PQ Editor you'll be faced with an unhelpful Enter Parameters dialogue. If instead you right-click it and choose Edit you get a similar dialogue and a single step. This is because it's a function. You can see the entire code in the formula bar or you could click on Advanced Editor in the Home tab to see the steps there but you won't see the results of each step as a developing table. You will see the first line which is:
Code:
(myType,myDept,myStatus,myCount,myMonth)=>In order to be able to see the developing steps I've duplicated the function in a query called DEVfnFindOutcomes, so if you bring that up in the PQ Editor instead you'll see all the steps. In fact you'll see more steps because the first 5 are just establishing the parameters/arguments manually.
At first opening you'll see just Outcome 29 because that's the final step of this query. If you click on any of the first 5 steps (all beginning 'my'…) you'll see the values used (some strings, some numbers) in the formula bar and you can edit them there, although it's not so obvious how to put an empty string in so it's safer to edit them in the first lines of the code manually by going into the Advanced Editor:
Code:
let
myType="",
myDept="",
myStatus="N/A",
myCount=1,
myMonth=4.109,The first real step after establishing parameter values is the Source step which just grabs the Categories table from the Categories query. The next ReplacedValue step does something I really should have done in the Categories query and brings in the value from the DeptOper column to the DeptValue column if the DeptValue column is null (there's only one row (3) this happens in).
The next 5 steps, all names ending in …'Filter', filter the table on the 5 parameters, selecting only rows from the table using the built-in function List.AnyTrue() [the StatusFilter step is an exception, it was too simple] which works by returning True if any or more of the elements in the list (in curly brackets{}) is True.
So in that list I put some tests, and if any of the tests return true, that row is kept. In the DeptFilter, CountFilter and MonthFilter steps I separated each element of the list with a carriage return to help me with the logic.
At the end of these filtering steps I may be left with a completely empty table, a table with one row (the ideal) or a table with several rows.
I remove all the other columns except for the outcomes column (called Column1) in step RemovedOtherColumns, convert that into a list in the Column1 step and finally concatenate that list using comma-space as a delimiter to create a string, being the output of the custom function.
arvarr,
Was your issue resolved? Did p45cal's method work for your needs? Or did you go with anything else?
I got real curious on this one when I notice 74 replies and no stars given anywhere.
I know PowerQuery can be super powerful and the other PowerBI tools MS has introduced in recent years, but I've yet to do much with them, myself. I played around with them a little just before being laid off at a place where it made a good fit. Where I am now, it isn't quite as useful currently, but perhaps one day.
I'm still curious, though. Would like to know the final outcome on this one, and thanks to p45cal for sharing the examples via links for review. Will make it easier to glance over it.
"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
Was your issue resolved? Did p45cal's method work for your needs? Or did you go with anything else?
I got real curious on this one when I notice 74 replies and no stars given anywhere.
I know PowerQuery can be super powerful and the other PowerBI tools MS has introduced in recent years, but I've yet to do much with them, myself. I played around with them a little just before being laid off at a place where it made a good fit. Where I am now, it isn't quite as useful currently, but perhaps one day.
I'm still curious, though. Would like to know the final outcome on this one, and thanks to p45cal for sharing the examples via links for review. Will make it easier to glance over it.
"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
- Thread starter
- #76
- Thread starter
- #78
Hi Skip
Sorry, had to revisit this thread.
I need to incorporate an extra requirement.
There will be another column called Amount.
Extra Criteria:
If amount is >=-10 <=10, then Outcome 40.
(As long as amount is >=-10 and <=10, then give Outcome 40.
Thanks,
arv
Sorry, had to revisit this thread.
I need to incorporate an extra requirement.
There will be another column called Amount.
Extra Criteria:
If amount is >=-10 <=10, then Outcome 40.
(As long as amount is >=-10 and <=10, then give Outcome 40.
Thanks,
arv
SkipVought
Programmer
Please upload your latest copy. Don't know if I have it.
BTW, the latest copy I uploaded to you has 29 Outcomes. Where did 40 come from?
Might be better to start a new thread with all new information, explanations, examples.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
BTW, the latest copy I uploaded to you has 29 Outcomes. Where did 40 come from?
Might be better to start a new thread with all new information, explanations, examples.
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
- Thread starter
- #80
- Status
Similar threads
