Formula Help
Formula Help
(OP)
Hi
Needing help with a formula.
Table worksheet is the combination of requirements.
Need a formula in the Data worksheet to return the outcome.
Sample file attached.
Thanks,
Arv
Needing help with a formula.
Table worksheet is the combination of requirements.
Need a formula in the Data worksheet to return the outcome.
Sample file attached.
Thanks,
Arv
RE: Formula Help
Just a few general comments regarding the criteria in the Table sheet.
If you are attempting to construct a table that yields a single row for any set of criteria, you have problems when any single two criteria in Count or Month for instance are >=4 and <=4 because in either criterion, the value of 4 would be TRUE, yielding 2 rows ambiguously.
Rather, >=4 and <4 or >4 and <=4.
In the Month column you also have >12 & <12, which would exclude 12, which is not a logic error if you intended to exclude 12.
Then you have criteria in Department, of NO VALUE, A001 or <>A001. Well NO VALUE is included in <>A001???
Regarding a formula, I think "yer up the crik." IMNSHO, you'll need a VBA routine that can check for the existence of some OPERATOR text so that the proper OPERATOR can be used in an equality. I'd write a User Defined Function. Maybe I'll work on that tonight, but I'll need clarification on the ALL logic problems before a solution can be ventured.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Count is the number of transactions incurred by the customer.
Month is the aging period.
In the month column, the reason why there is >12 & <12 is because of the need to differentiate those agreement status with N/A but falls within 12 months and those agreement status with N/A but falls outside 12 months. Based on the return comments, I will then need to do a sumif as they each will have a different course of action.
Department
criteria in Department, of NO VALUE, A001 or <>A001. Well NO VALUE is included in <>A001?
Nope, anything which is <>A001 will have a different value other than No Value. E.g it can be A002, A03, A004, etc
Thanks,
Arv
RE: Formula Help
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Count has <=4 or >=4
RE: Formula Help
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
What can we do to cater for this scenario>
RE: Formula Help
Count of 4 and a Month of 3 will ONLY return Outcome5.
If the row containing Outcome9 were sorted to appear before Outcome5 then Count of 4 and a Month of 3 will ONLY return Outcome9. Just want this to be clear.
And, once again to be clear, Month of 12.00 will return no outcome for Type Credit Note or Payment.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
There are multiple combinations with Type, Status, Department, Count and Month.
There are 3 criteria as well as 4 and 5 criteria.
If:
If Type = Credit Note and Agreement Status = N/A and Month >12, then Outcome 18
If Type = Credit Note and Agreement Status = N/A and Month <12, then Outcome 19
If Type = Payment and Agreement Status = N/A and Month >12, then Outcome 20
If Type = Payment and Agreement Status = N/A and Month <12, then Outcome 21
If the row containing Outcome9 were sorted to appear before Outcome5 then Count of 4 and a Month of 3 will ONLY return Outcome9. Just want this to be clear. > Sorry, not too sure what you meant by this.
Thanks.
RE: Formula Help
...that there are, according to the criteria in Table, 3 Return Comments that match, only the first of which can be returned.
Those 3 are:
...because the proper way of coding an expression is Either...
<=x or >x
Or
<x or >=x
But in no case is it proper to put the EQUAL into both the GREATER and LESSER! That introduces AMBIGUITY!
ONE way of correcting this might look like this...
Do you acknowledge that for the criteria from any row in Data...
...that there are, according to the criteria in Table, 0 Return Comments that match. This may be exactly what you intend. I only bring this up, because of the error you introduced and continue to ignore in the Count and Month above.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
I created a List sheet that has each unique occurrence of each criteria in Table, broken out into data parts. This is in order to identify the Crit1 value to lookup in Table criteria.
It also identified the ambiguities as you supply values in the YELLOW shaded cells.
As I stated, first step toward an approach to solving your question.
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Type: Invoice
Status: A2
Department: A002
Count: 4
Month: 3.00
...that there are, according to the criteria in Table, 3 Return Comments that match, only the first of which can be returned.
Those 3 are:
Type Status Department Count Month Return Comments
Invoice A2 <> A001 <=4 <=3 Outcome5
Invoice A2 <> A001 >=4 >=3 Outcome9
Invoice A2 <> A001 <=4 >=3 Outcome13
Damn it, this is much clearer now. All 3 criterias produces the same outcome based on the example from data that you provided.
Including criteria from column A to C, the aim is to include:
- Count More than 4 or less than 4
- Transactions that are more than 3 months or less than 3 months
Not sure how to handle if we have the following situation whereby Count = 4 and Month = 3.
Should we include another criteria with only "=" instead of <= or >= ?
ONE way of correcting this might look like this...
Type Status Department Count Month Return Comments
Invoice A2 <> A001 <4 <=3 Outcome5 - What happens when the transaction = 4
Invoice A2 <> A001 >=4 >3 Outcome9 - What happens when Month = 3
Invoice A2 <> A001 <4 >3 Outcome13 - What happens when Month = 3
Ah.....The month criteria should be <=12 or >=12. The intent is to pick up if type = Payment, status = N/A and Month is within 12 Months or Outside 12 Months.
This will apply to Row 19 to 21 on the Table worksheet.
Type: Payment
Status: N/A
Department:
Count:
Month: 12.00
RE: Formula Help
This might be a good example...
So does that mean...???
If you take this phrase, "get this job done within a month" that means between the first and last day of the month. So "within 12 month" would include the 12th month. So the way I'd interpret "within 12 months" is <=12 and then "outside 12 months" would exclude month 12 and thereby be >12.
Now you might apply a similar logic ...
Count: <=4 or >4
Month: <=3 or >3
These criteria would give you unambiguous results
As I studied your Table, I surmised (given the 3 sets you posted) that you might have 4 sets of criteria in Count & Month like thus...
...maybe?
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
If you take this phrase, "get this job done within a month" that means between the first and last day of the month. So "within 12 month" would include the 12th month. So the way I'd interpret "within 12 months" is <=12 and then "outside 12 months" would exclude month 12 and thereby be >12.
Thanks.
RE: Formula Help
1. Up to and including 12 months Outside 12 months
<=12 >12
Or
2. Within 12 months 12 months Outside 12 months
<12 =12 >12
Or
3. Up to 12 months 12 months and greater
<12 >=12
RE: Formula Help
Now when you put a criteria for Month, there are 2 possibilities...
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Thanks.
RE: Formula Help
My approach would be to use those 5 Crit1 values in a SUMPRODUCT() on Table values, similar to what I did in List
I might be able to do more tomorrow, but I have on line church and then working on face masks for folks around me.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Enjoy the weekend :)
RE: Formula Help
I'm having trouble, I think, with EMPTY cells. But here's where I am. There are still lots of problems but I can't spend any more time on it. I'm part of the way there but I can't seem to find a composite solution.
Maybe someone else has a better approach. I might have blinders on. It's very klunky, but I can't see another way.
Play around with it.
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Hopefully, someone else can provide further insights.
RE: Formula Help
Again I stress that your Data does not test ALL the criteria AND your test Data has ILLEGAL combinations.
I had to ADD 4 new rows that were not accounted for where Count is >4 and Month is <=3.
Let me know what you think. As it continues to simmer in my skull, I may come upon a less cumbersome solution.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Outcome7
Outcome13
Outcome14
Outcome15
Outcome22
Outcome24
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Correct, data might not have tested all the combinations as i just pieced it together.
The primary objective was seeking assistance with the formulas based on the criteria.
I assume that your formula will incorporate those outcomes which were not in the data.
Thank again for your help, will revert back and let you know how i go.
RE: Formula Help
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Wow, you're great grandfather now :)
Thank you so much for looking into this and finding the solution. Yep, i noticed that there are 25 columns of formula, phew.............
From the bottom of my heart, You never stopped impressing me (not the first time that you have given me solutions).
Let me plug the numbers and formulas into real data and will keep you informed.
RE: Formula Help
I tried replicating formulas into my sheet then also have a go at your s/sheet.
To be honest, i have not yet tested all scenarios.
Say for one of the scenario, if type = Invoice and Status = N/A, it will give outcome 26.
But, if there are values in Department, Count or Month, it will give incorrect result.
Pls kindly advise.
Thanks,
Arv
RE: Formula Help
Has anything changed in the Table or List tables? If so exactly what?
As to your specific question
...that row in your Table only has Type and Status. No other Column values are defined. Therefore, you get an error. That's how it works. Earlier I asked you this question... You answered...
...and I took that to mean that ANY NO VALUE(empty) means NO VALUE ALLOWED.
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
I have not changed anything just having a try at the file you sent.
It did not give error just incorrect outcome.
RE: Formula Help
NO VALUE
A001
<> A001
...and from your reply it was clear that NO VALUE was "Nope," not included in <> A001. Therefore, I take that to mean that NO VALUE PERMITTED.
It might be helpful to disclose what the "incorrect outcome" was.
BTW, I see an Outcome26 on the Data sheet Workbook I uploaded last
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Sorry for the confusion.
RE: Formula Help
Here's the corrected formula for Data column Outcome
H3: =IF(SUMPRODUCT((tCRIT[Type]=J3)*(tCRIT[Department]=K3)*(tCRIT[Count]=L3)*(tCRIT[Month]=M3)*(tCRIT[Status]=N3)*(tCRIT[Seq]))=0,"",INDEX(tCRIT[Return Comments],SUMPRODUCT((tCRIT[Type]=J3)*(tCRIT[Department]=K3)*(tCRIT[Count]=L3)*(tCRIT[Month]=M3)*(tCRIT[Status]=N3)*(tCRIT[Seq]))))
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
RE: Formula Help
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
If I have any values in other columns, the incorrect outcome is outcome 1
On the Data worksheet, Invoice, Status, Department, Count and Month will always have a value.
On the Table worksheet, i left some of the Department, Count and Month fields empty because those are the only criteria that needs to be met to return the Return Comments.
Example - Row 26 - If Type = Payment, Status = N/A and Month <=12, then return Outcome 25 (irrespective of the values in Department, Count and Month).
Likewise with the other scenarios on the Table worksheet.
Is there a formula to tackle this or do i need to expand the criteria on the Table worksheet?
Apologies that i have been a bit ambigous with the information passed on to you.
Thanks,
Arv
RE: Formula Help
Please state the criteria rules that are presented in Table. In what cases is an empty cell a criteria or not.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Table worksheet:
- All the "empty" cells are not a criteria.
- I have not put in any values in those empty cells because there may be a mixture of values. As long as those non-empty cells criteria are fulfilled, it is to return the expected return comment
Thanks,
Arv
RE: Formula Help
Need a formula to...
Include (Type, Status, Department, Count, Month) When (Type, Status, Department, Count, Month) NOT ISBLANK Else
Include (Type, Status, Month) When (Type, Status, Month) NOT ISBLANK AND (Department, Count) ISBLANK Else
Include (Type, Status, Department) When (Type, Status, Department) NOT ISBLANK AND (Count, Month) ISBLANK Else
Include (Type, Department) When (Type, Department) NOT ISBLANK AND (Status, Count, Month) ISBLANK Else
Include (Type, Status) When (Type, Status) NOT ISBLANK AND (Department, Count, Month) ISBLANK
Its just tedious work to get it right. You have to get the AND of the BLANK right with the NOT BLANK so you Include the NOT BLANKs in Each SUMPRODUCT. The objective is to get all that into one garhunkulous formula that you can COPY into each row of Output.
But...what I stated in my first post, "I'd write a User Defined Function," at this juncture of complexity, seems the odds on favorite IMNSHO. I mean I wore myself out just writing the logic in this reply and I'm pushin' 80. Gotta catch my breath and do some Omphaloskepsis.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
25 Type, Status, Month
17 Type, Status
31 All 5
3 Type, Department
Notice I have 6 new columns on Data to help with which cells have data or not.
Observations:
Type is the only field that has data in every row.
Count only occurs when all other fields have data.
Status N/A only has Month data or not.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Date Type Amount Department Count Month Status
Apologies that i have omitted them.
For simulation, you can add any values in those empty cells.
There are also 5 combinations.
Refer to Outcome 4 to Outcome 19.
Type Status Department Count Month
Thanks.
RE: Formula Help
I do totally appreciate your help with this matter.
Thank you so much. Let me have a go with the latest file that you sent through.
Regards,
Arv
RE: Formula Help
RE: Formula Help
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
See attached. I have tried to go through line by line to ensure that table and data provided is as accurate as can be.
I have gone through the full set of data which i didnt previously (laziness).
There are some scenarios which i have yet to determine where / how to categorize.
(highlighted in yellow on the Data worksheet). Do i have to categorize them now or would it be difficult be add them back in later?
I.e.
If Type = Credit Note or Payment or Refund or NULL
And Department = NULL
And Status = A1 Or A2 Or C Or S Or U
If the above has to be categorized now, let me know and I will double check the requirements for those.
Let me know if something is amiss.
Thanks again,
Arv
RE: Formula Help
RE: Formula Help
Type = Payment: the way I'd interpret that Status must be N/A. However, in Data, there are lots of yellow Payment with BLANK Comment and lots of other Status values.
???
Here's what is needed, for instance to be complete...
If Type = Credit Note or Payment or Refund or NULL
And Department = NULL
And Status = A1 Or A2 Or C Or S Or U
Then Outcome = x
Else If ....
And Status = N/A
And Month <=12
Then Outcome = y
Else If ...
And Month >12
Then Outcome = z
...for every category.
This is DEFINITELY something that needs a UDF in VBA. I would put the criteria into a table and interpret in VBA. You don't have the kind of logic that lends itself to a formula readily.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
RE: Formula Help
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Thanks.
RE: Formula Help
I have removed those yellow records. They should not exist.
Thanks,
Arv
RE: Formula Help
Is everything ok or not?
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Do you mean to put these data into your file?
Thanks.
RE: Formula Help
Hence, I have gone through line by line and manually inserting the expected outcome.
Let me know otherwise if i am to be using one of the files that you uploaded.
RE: Formula Help
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
H3: =Outcome(B3,D3,E3,F3,G3)
...and COPY/PASTE down.
CODE
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Thanks for the UDF. I have ran the formula through. Some of the output is incorrect.
I have attached the workbook for your review.
RE: Formula Help
Sorry!
Check out the corrected original. There were more than S. COPY n PASTE the whole thing.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
RE: Formula Help
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
RE: Formula Help
COPY n PASTE again. Refund & Invoice in the N/A part.
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
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,
RE: Formula Help
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
RE: Formula Help
RE: Formula Help
I finally managed to run through the scenarios and looks good so far.
I will try a few other scenarios
Thank you so much for your help with this matter. Never failed to provide a solution.
RE: Formula Help
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
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.
RE: Formula Help
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
RE: Formula Help
(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.
RE: Formula Help
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.
RE: Formula Help
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
RE: Formula Help
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ā¦
Edit: I'm struggling to attach a file as before, so here's the link I copied manually:
https://files.engineering.com/getfile.aspx?folder=...
RE: Formula Help
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 -->
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 -->
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 -->
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.
RE: Formula Help
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
RE: Formula Help
I went with Skips solution. Haven't had the chance to try out p45cal's solution. Will give that a shot too.
Lots of stars has been given. Not sure when you mention no stars?
Thanks everyone for helping out.
Much appreciated,
Arv
RE: Formula Help
"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
RE: Formula Help
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
RE: Formula Help
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,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Here's the file.
Ah...40 was just a random number. the latest copy that you send certainly was up to 29 outcomes.
I have inserted the column with amount and updated table criteria to outcome 30.
Thanks,
arv
RE: Formula Help
However, now you have undefined Status values that don't resolve to any outcome. Good luck!
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Formula Help
Regards,
arv