Contact US

Question

Formula Help

arvarr (MIS)

(OP)

24 Apr 20 07:15

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

https://files.engineering.com/getfile.aspx?folder=520249e3-e3aa-48a1-a308-6

SkipVought (Programmer) · Answer 1 · 2020-04-24 21:31:19

Hi,

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

arvarr (MIS) · Answer 2 · 2020-04-24 22:03:24

Hi SkipVought
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

SkipVought (Programmer) · Answer 3 · 2020-04-24 22:13:55

Well what about >=4 & <=4 etc???

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

arvarr (MIS) · Answer 4 · 2020-04-24 22:23:24

The month has <=3 or >=3
Count has <=4 or >=4

SkipVought (Programmer) · Answer 5 · 2020-04-24 22:27:51

That will give you ambiguous results when the Data sheet value is 4 or 3 respectively!

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

arvarr (MIS) · Answer 6 · 2020-04-24 22:31:43

As much as i would like to update the operator, there will be situations from the data table whereby month = 3 and count =4
What can we do to cater for this scenario>

SkipVought (Programmer) · Answer 7 · 2020-04-25 01:32:11

For instance...

Invoice	A2	<> A001	<=4	<=3	Outcome5
Invoice	A2	<> A001	>=4	>=3	Outcome9

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

arvarr (MIS) · Answer 8 · 2020-04-25 06:08:21

The criteria is from Column A to Column E in the "Table" worksheet.
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.

SkipVought (Programmer) · Answer 9 · 2020-04-25 15:25:44

Do you acknowledge that for the criteria from any row in Data

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

...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...

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

Do you acknowledge that for the criteria from any row in Data...

Type:       Payment	
Status:     N/A	
Department: 
Count:      
Month:      12.00

...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

SkipVought (Programmer) · Answer 10 · 2020-04-25 19:38:22

Here's a first swipe at this problem.
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,
_{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) · Answer 11 · 2020-04-25 19:39:29

Sorry, forgot to upload.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

https://files.engineering.com/getfile.aspx?folder=ffe4dc9b-f4b4-4c82-a97b-2

arvarr (MIS) · Answer 12 · 2020-04-25 22:34:34

Do you acknowledge that for the criteria from any row in Data
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

SkipVought (Programmer) · Answer 13 · 2020-04-25 23:29:25

Great. Now you have to figure out which where to put the EQUAL sign.

This might be a good example...

Quote:
The intent is to pick up if type = Payment, status = N/A and Month is within 12 Months or Outside 12 Months.

So does that mean...???

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

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...

Count  Month
<=4    <=3
<=4    >3
>4     <=3
>4     >3

...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

arvarr (MIS) · Answer 14 · 2020-04-25 23:51:14

I think this should do.
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.

arvarr (MIS) · Answer 15 · 2020-04-25 23:54:16

Option 1 should cut it.

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

SkipVought (Programmer) · Answer 16 · 2020-04-26 01:44:56

So I modified the Table and the List to correct the criteria.

Now when you put a criteria for Month, there are 2 possibilities...

Month		13		
Status				N/A
	Seq	Crit1	Seq	Crit1
=	0			
<	0			
>	22	>3	23	>12
<=	0		0	
>=	0			
<>	0

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

https://files.engineering.com/getfile.aspx?folder=873393dd-7d9f-4ca1-96bf-9

arvarr (MIS) · Answer 17 · 2020-04-26 03:42:31

What formula should be used on the Data worksheet so that on column H (Output), it will return the results.
Thanks.

SkipVought (Programmer) · Answer 18 · 2020-04-26 04:03:03

Well that's the next step. What I've done is like a proof of concept for getting from a value in the Data table to a criteria in the Table. Got to get the criteria for all 5 columns to get the Return Comment. That's your Tip.

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

arvarr (MIS) · Answer 19 · 2020-04-26 04:46:20

Cool, thanks for helping get to this stage.
Enjoy the weekend :)

SkipVought (Programmer) · Answer 20 · 2020-04-27 22:35:39

Your test Data doesn't correspond well with your Table of criteria.

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,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

https://files.engineering.com/getfile.aspx?folder=c8a0bbfa-2c0a-4e0c-bf76-a

arvarr (MIS) · Answer 21 · 2020-04-27 23:32:35

Thanks for your help so far SkipVaught.

Hopefully, someone else can provide further insights.

SkipVought (Programmer) · Answer 22 · 2020-04-28 21:07:42

This was bugging me and simmerin' on the back burner while I worked on other things. But I think this is basically solved, although much too knunky for my sensibilities.

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

https://files.engineering.com/getfile.aspx?folder=f6e86365-e314-4b47-bf4b-b

SkipVought (Programmer) · Answer 23 · 2020-04-28 21:35:09

...and these criteria are not present in your data

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

arvarr (MIS) · Answer 24 · 2020-04-29 01:26:05

Thank you so much Skip, let me run through the file and transpose to real data.

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.

SkipVought (Programmer) · Answer 25 · 2020-04-29 02:46:32

Notice on the Data sheet, there are some 25 columns of formulas. Plus the List table that interprets the criteria from the Table table.

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) · Answer 26 · 2020-04-29 02:49:49

...and BTW, just got notified that my granddaughter and her husband in Port Kembla just brought a little baby girl into the world down under, FYI!

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

arvarr (MIS) · Answer 27 · 2020-04-29 11:47:30

Congratulations. I bet you must be wrapped.
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.

arvarr (MIS) · Answer 28 · 2020-05-03 12:01:25

Hi Skip
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

SkipVought (Programmer) · Answer 29 · 2020-05-03 12:43:13

Please state ALL the data in Data table row in question.

Has anything changed in the Table or List tables? If so exactly what?

As to your specific question

Quote:
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.

...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...

Quote:
Department
criteria in Department, of NO VALUE, A001 or <>A001. Well NO VALUE is included in <>A001?

You answered...

Quote:
Nope, anything which is <>A001 will have a different value other than No Value. E.g it can be A002, A03, A004, etc

...and I took that to mean that ANY NO VALUE(empty) means NO VALUE ALLOWED.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

arvarr (MIS) · Answer 30 · 2020-05-03 14:22:22

What I meant to say was anything <>A001 in the department column can be a002, etc.

I have not changed anything just having a try at the file you sent.
It did not give error just incorrect outcome.

SkipVought (Programmer) · Answer 31 · 2020-05-03 14:34:22

Quote:
What I meant to say was anything <>A001 in the department column can be a002, etc.

That was clear. But I stated that three values exist in Department...
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,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

arvarr (MIS) · Answer 32 · 2020-05-03 14:38:51

If I have any values in other columns, the incorrect outcome is outcome 1.
Sorry for the confusion.

SkipVought (Programmer) · Answer 33 · 2020-05-03 14:53:45

Here's a new upload.

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

https://files.engineering.com/getfile.aspx?folder=9e52d6f5-cd9d-424e-8657-5

arvarr (MIS) · Answer 34 · 2020-05-03 14:59:18

Thanks Skip, will give it a shot tomorrow. Going to bed. Appreciate your help as usual.

SkipVought (Programmer) · Answer 35 · 2020-05-03 15:01:42

No problem.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

arvarr (MIS) · Answer 36 · 2020-05-04 03:36:52

Hi Skip

Quote (arvarr)

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

SkipVought (Programmer) · Answer 37 · 2020-05-04 04:36:23

Okay, let's start from the beginning.

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

arvarr (MIS) · Answer 38 · 2020-05-04 06:06:20

Okay, lets start from the beginning.

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

SkipVought (Programmer) · Answer 39 · 2020-05-04 15:52:37

Well this complicates the use of a formula with only certain combinations of fields testing certain types of data.

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

SkipVought (Programmer) · Answer 40 · 2020-05-04 18:37:46

I think you have 4 combinations, not 5...
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

https://files.engineering.com/getfile.aspx?folder=0a0a7236-c14e-4b91-b60b-b

arvarr (MIS) · Answer 41 · 2020-05-04 22:42:29

On the Data worksheet, these fields will always have data.
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.

Answer 42

[Post Deleted]

arvarr (MIS) · Answer 43 · 2020-05-04 23:30:26

My sincere apologies Skip. I should have been more cautious with providing a more complete set of data to enable you to help me out.
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

arvarr (MIS) · Answer 44 · 2020-05-04 23:37:28

Just tested the latest and prior files but still no go.

SkipVought (Programmer) · Answer 45 · 2020-05-05 03:28:24

Please upload the current Table and Data sheets that you are using and indicate the Output you expect on each row.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

arvarr (MIS) · Answer 46 · 2020-05-05 11:47:47

Hi Skip
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

arvarr (MIS) · Answer 47 · 2020-05-05 11:49:03

File as per attached.

https://files.engineering.com/getfile.aspx?folder=82aa6abe-7032-4737-9f89-c

SkipVought (Programmer) · Answer 48 · 2020-05-05 14:44:59

Help me out.

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

arvarr (MIS) · Answer 49 · 2020-05-05 14:54:43

The yellow ones in data worksheet are the ones which I have not yet determine what to do. Not sure those data needs to be reported. Hence, I have not included those in the criteria table.

SkipVought (Programmer) · Answer 50 · 2020-05-05 15:45:29

Well then is/are there any issue(s) not yet resolved?

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

arvarr (MIS) · Answer 51 · 2020-05-05 22:36:07

Only the yellow ones. I will let you know today what needs to be done with those yellow ones.
Thanks.

arvarr (MIS) · Answer 52 · 2020-05-06 04:16:08

Hi Skip
I have removed those yellow records. They should not exist.

Thanks,
Arv

https://files.engineering.com/getfile.aspx?folder=71dec761-b3f1-4973-adf1-3

SkipVought (Programmer) · Answer 53 · 2020-05-06 04:28:02

???

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

arvarr (MIS) · Answer 54 · 2020-05-06 04:39:02

I have uploaded the latest file with expected outcome which i have gone through manually.

Do you mean to put these data into your file?
Thanks.

arvarr (MIS) · Answer 55 · 2020-05-06 04:42:13

Quote (SkipVought)

Please upload the current Table and Data sheets that you are using and indicate the Output you expect on each row.

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.

SkipVought (Programmer) · Answer 56 · 2020-05-06 04:47:44

Got it. I'll run it thru and see what results.

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) · Answer 57 · 2020-05-07 13:29:46

Put this code in a module and use it like a spreadsheet function in the Output column of your Data sheet like this

H3: =Outcome(B3,D3,E3,F3,G3)

...and COPY/PASTE down.

CODE

Function Outcome(sTyp As String, sDep As String, iCnt As Integer, nMon As Single, sSta As String) As String
    Select Case sTyp
        Case "Invoice"
            Select Case sSta
                Case "C": Outcome = "1"
                Case "E": Outcome = "2"
            End Select
            Select Case sDep
                Case "A001": Outcome = "3"
                Case Is <> "A001"
                    If iCnt <= 4 Then
                        If nMon <= 3 Then
                            Select Case sSta
                                Case "A1": Outcome = "4"
                                Case "A2": Outcome = "5"
                                Case "U": Outcome = "6"
                                Case "S": Outcome = "7"
                            End Select
                        Else
                            Select Case sSta
                                Case "A1": Outcome = "12"
                                Case "A2": Outcome = "13"
                                Case "U": Outcome = "14"
                                Case "S": Outcome = "15"
                            End Select
                        End If
                    Else
                        If nMon <= 3 Then
                            Select Case sSta
                                Case "A1": Outcome = "16"
                                Case "A2": Outcome = "17"
                                Case "U": Outcome = "18"
                                Case "S": Outcome = "19"
                            End Select
                        Else
                            Select Case sSta
                                Case "A1": Outcome = "8"
                                Case "A2": Outcome = "9"
                                Case "U": Outcome = "10"
                                Case "S": Outcome = "11"
                            End Select
                        End If
                    End If
            End Select
        Case "Refund"
            If sSta = "N/A" Then
                If sDep <> "A001" Then
                    Select Case nMon
                        Case Is > 12: Outcome = "20"
                        Case Is <= 12: Outcome = "21"
                    End Select
                End If
            End If
        Case "Credit Note"
            If sSta = "N/A" Then
                If sDep <> "A001" Then
                    Select Case nMon
                        Case Is > 12: Outcome = "22"
                        Case Is <= 12: Outcome = "23"
                    End Select
                End If
            End If
        Case "Payment"
            If sSta = "N/A" Then
                If sDep <> "A001" Then
                    Select Case nMon
                        Case Is > 12: Outcome = "24"
                        Case Is <= 12: Outcome = "25"
                    End Select
                End If
            End If
        Case "Invoice"
            If sSta = "N/A" Then
                If sDep <> "A001" Then
                    Select Case nMon
                        Case Is > 12: Outcome = "26"
                        Case Is <= 12: Outcome = "27"
                    End Select
                End If
            End If
        Case ""
            Select Case sSta
                Case "C", "E", "A1", "A2", "U", "S", "N/A"
                    If sDep <> "A001" Then
                        Select Case nMon
                            Case Is > 12: Outcome = "28"
                            Case Is <= 12: Outcome = "29"
                        End Select
                    End If
            End Select
    End Select
    
    If Outcome <> "" Then
    Outcome = "Outcome" & Outcome
    End If
End Function

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

arvarr (MIS) · Answer 58 · 2020-05-07 23:21:09

Hi Skip
Thanks for the UDF. I have ran the formula through. Some of the output is incorrect.
I have attached the workbook for your review.

https://files.engineering.com/getfile.aspx?folder=a3ad0ffa-9049-4c64-914e-c

SkipVought (Programmer) · Answer 59 · 2020-05-07 23:48:00

I'm gonna blame it on account of an alarming lack of caffeine at a time that used to be late for me, but is now early. How's that for passing the buck?

Sorry! blush

I couldn't run a check, which I should have done. blush

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

arvarr (MIS) · Answer 60 · 2020-05-08 00:34:16

Lol... thanks Skip. The UDF is great once we get it working. Less klunky :)

SkipVought (Programmer) · Answer 61 · 2020-05-08 00:47:26

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,
_{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) · Answer 62 · 2020-05-08 00:57:23

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

arvarr (MIS) · Answer 63 · 2020-05-08 01:22:10

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,

https://files.engineering.com/getfile.aspx?folder=bf33b233-a019-472b-ae8a-9

SkipVought (Programmer) · Answer 64 · 2020-05-08 02:48:35

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

https://files.engineering.com/getfile.aspx?folder=40721755-aa14-431c-812b-2

arvarr (MIS) · Answer 65 · 2020-05-08 03:31:38

Thanks Skip, let me run through the file.

arvarr (MIS) · Answer 66 · 2020-05-15 04:07:46

Hi Skip
I finally managed to run through the scenarios and looks good so far.
I will try a few other scenarios 2thumbsup

Thank you so much for your help with this matter. Never failed to provide a solution.

SkipVought (Programmer) · Answer 67 · 2020-05-15 05:04:37

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

p45cal (TechnicalUser) · Answer 68 · 2020-06-05 20:11:08

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.

https://files.engineering.com/getfile.aspx?folder=aef53318-c290-4d2e-80d6-3

SkipVought (Programmer) · Answer 69 · 2020-06-05 22:23:01

Quote (p45cal)
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.

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

p45cal (TechnicalUser) · Answer 70 · 2020-06-05 22:51:54

Quote (SkipVought)
The objective is to use the Table data to fill Outcome on the Data sheet, based on 5 criteria.

Is that what "Refresh" does?

Ha! No. I cocked up. rofl

I thought it was to do it the other way round! upsidedown

(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

p45cal (TechnicalUser) · Answer 71 · 2020-06-06 22:24:24

Quote (p45cal)
If I get the inclination I'll try doing it the right way round.

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.
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.

https://files.engineering.com/getfile.aspx?folder=bc2ccc3c-5508-4717-9625-2

SkipVought (Programmer) · Answer 72 · 2020-06-07 03:03:53

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

p45cal (TechnicalUser) · Answer 73 · 2020-06-07 10:00:59

Quote (SkipVought)
Your solution, albeit generating correct data, does not fill the Outcome column in the Data table.

Easy, see attached.

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.

Quote (SkipVought)
Could you explain the steps for arriving at your solution?

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=...

p45cal (TechnicalUser) · Answer 74 · 2020-06-07 14:29:52

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:

CODE -->

Splitter.SplitTextByCharacterTransition({"<",">","="}, (c) => not List.Contains({"<",">","="}, c))

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:

CODE -->

(myType,myDept,myStatus,myCount,myMonth)=>

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:

CODE -->

let
myType="",
myDept="",
myStatus="N/A",
myCount=1,
myMonth=4.109,

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.

https://files.engineering.com/getfile.aspx?folder=9576f469-760a-4265-81a5-b

kjv1611 (IS/IT - Management) · Answer 75 · 2020-06-19 13:47:06

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

arvarr (MIS) · Answer 76 · 2020-06-19 15:38:47

Hi kjv1611
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

kjv1611 (IS/IT - Management) · Answer 77 · 2020-06-19 17:23:55

Guess I missed that one.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

arvarr (MIS) · Answer 78 · 2020-09-08 12:13:56

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

SkipVought (Programmer) · Answer 79 · 2020-09-08 15:06:08

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

arvarr (MIS) · Answer 80 · 2020-09-08 23:39:53

Hi Skip
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

https://files.engineering.com/getfile.aspx?folder=ecf9d4f9-c5e3-4ab8-a5d2-e

SkipVought (Programmer) · Answer 81 · 2020-09-09 02:46:37

here's the modification. if you look at the VBA, you'll see that I check that Amount is between -100 and 100 and got a handfull of Outcome 30.

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

https://files.engineering.com/getfile.aspx?folder=26cb0f30-fa15-46c5-a6b5-f

arvarr (MIS) · Answer 82 · 2020-09-09 12:40:54

Yay...Thanks for updating the code 2thumbsup

. Gives me the outcome that i want.
Regards,
arv

Answer 83

[Post Deleted]

Answer 84

[Post Deleted]

Contact US

Come Join Us!

Posting Guidelines

Formula Help

Formula Help

Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

Quote:

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

Quote:

Quote:

Quote:

RE: Formula Help

RE: Formula Help

Quote:

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

Quote (arvarr)

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

Quote (SkipVought)

RE: Formula Help

RE: Formula Help

CODE

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

RE: Formula Help

Join Tek-Tips^® Today!