Formula Help 1

[arvarr]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

Thanks for your help so far SkipVaught.

Hopefully, someone else can provide further insights.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

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

Outcome7
Outcome13
Outcome14
Outcome15
Outcome22
Outcome24


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

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]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

...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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

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]

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]

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

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

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

You answered...

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

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]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

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

 

[SkipVought]

Here's a new upload.

Here's the corrected formula for Data column Outcome
[tt]
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]))))
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

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

 

[SkipVought]

No problem.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

Hi Skip

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]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

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]

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

Need a formula to...
[tt]
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
[/tt]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein