×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Formula Help

Formula Help

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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

RE: Formula Help

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
The month has <=3 or >=3
Count has <=4 or >=4

RE: Formula Help

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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>

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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.

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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.

RE: Formula Help

(OP)
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

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
Thanks for your help so far SkipVaught.

Hopefully, someone else can provide further insights.

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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

Outcome7
Outcome13
Outcome14
Outcome15
Outcome22
Outcome24

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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.

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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.

RE: Formula Help

(OP)
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

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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.

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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

RE: Formula Help

No problem.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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.

RE: Formula Help

(OP)
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

RE: Formula Help

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

RE: Formula Help

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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.

RE: Formula Help

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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

RE: Formula Help

???

Is everything ok or not?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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.

RE: Formula Help

(OP)

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.

RE: Formula Help

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

I got more 🍳 on face. No excuse for having a bad code🤒!

COPY n PASTE again. Refund & Invoice in the N/A part.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
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,

RE: Formula Help

(OP)
Thanks Skip, let me run through the file.

RE: Formula Help

(OP)
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.

RE: Formula Help

Great! Glad to help when I can.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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.

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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!upsidedownlaughtears
(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

RE: Formula Help

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.

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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

RE: Formula Help

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.

RE: Formula Help

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

RE: Formula Help

(OP)
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

RE: Formula Help

blush Guess I missed that one.

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

RE: Formula Help

(OP)
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

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Formula Help

(OP)
Yay...Thanks for updating the code 2thumbsup. Gives me the outcome that i want.
Regards,
arv

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close