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]

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,
[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]

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.

 

[arvarr]

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]

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

 

[SkipVought]

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

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

File as per attached.

 

[SkipVought]

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...
[tt]
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
[/tt]
...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,
[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]

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]

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

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]

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

 

[arvarr]

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

Thanks,
Arv

 

[SkipVought]

???

Is everything ok 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]

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]

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]

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

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]

Put this code in a module and use it like a spreadsheet function in the Output column of your Data sheet like this
[tt]
H3: =Outcome(B3,D3,E3,F3,G3)
[/tt]
...and COPY/PASTE down.

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,
[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
Thanks for the UDF. I have ran the formula through. Some of the output is incorrect.
I have attached the workbook for your review.

 

[SkipVought]

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! I couldn't run a check, which I should have done.

Check out the corrected original. There were more than S. COPY n PASTE the whole thing.

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]

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