Automating conditional decision making

[TheRedCardinal]

Hi all,
I have hit a problem with my work project and it's one of those where I can't even begin to design the solution never mind implement it! I hope you guys can help!

I have provided a sample workbook here.

https://drive.google.com/open?id=1u30oLh9SavbDpc3YVfoWGCEGJZ7Aqpeu

The process works as follows (the spreadsheet has no macros on it I've prepared this manually but most of these steps are automated in the operating version.

The macro brings in two tables of data - one called Logistics and one called Sales. You can see these on the Raw Data sheet colour coded.
Then a pivot is made to identify differences between Amount and VAT value of each invoice listed. This is because the two systems report different values and the "VAT Value" is normally the correct one.

Regardless, the user is invited to determine the correct treatment but in general, where "VAT value" is higher than "Amount" we can assume VAT value is correct.

We then continue to the Logistics data which is replicated in Final Data tab. This contains more fields than value which need to be reported so can't be ignored.

I use a lookup to populate the field "Amount" with the value that the user has settled on in the Pivot tab.

The problem is, some invoices have more than one line because a key header, Commodity Code, varies on invoices. However as predicted, the Vlookup finds the invoice and returns the total value for each line. This is incorrect.

What I need my macro to do is:

- identify lines in the Final tab that are duplicated
- then identify the difference from the pivot table for that invoice
- if the difference is zero, simply import the amount from the original data table instead
- if the difference is not zero, apportion the difference by % of weight/total weight for each line

I have set out a working example in the attached workbook.

Is this something that can be automated?

Thanks!

 

[TheRedCardinal]

Re: Help needed in automating this process - conditional decision making

Sergio,
This works perfectly.
It also really helps with the sheet and the usability of it.

Now for the final stage - I need to get this into a Macro so that I can populate the column each time the data refreshes - there will be different numbers of rows each time, and our project aims are to get away from formulae and copy and pasting.

I wrote a small macro that uses a For/Next look, with a variable (LRow) to identify the last row of data (all variables have been defined):

 For i = 2 to LRow
x = Application.[FONT='inherit']IFERROR(ROUND(F2-(SUMIF('Raw Data'!A:A,A2,'Raw Data'!G:G)+SUMIF('Raw Data'!A:A,A2,'Raw Data'!F:F))*E2/SUMIF(A:A,A2,E:E),2),0)[/FONT]

Cells(i,Column).Value = x

Next i

Predictably it hasn't worked, giving me an error.

What would you think is the best way to do this?

 

[sergioMabres]

Re: Help needed in automating this process - conditional decision making

I do not have the needed context but I guess you can use the formula in VBA as:

For i = 2 To LRow
    Cells(i, Column).FormulaR1C1 = _
        "=IFERROR(ROUND(RC[-1]-(SUMIF('Raw Data'!C[-6],RC[-6],'Raw Data'!C)+SUMIF('Raw Data'!C[-6],RC[-6],'Raw Data'!C[-1]))*RC[-2]/SUMIF(C[-6],RC[-6],C[-2]),2),0)"
Next i

Where are you from Red, what country, for my statistics
Sergio

 

[TheRedCardinal]

Re: Help needed in automating this process - conditional decision making

Thanks Sergio - I managed a different solution perhaps not so elegant as that (I needed values not formulae in the cell).

From UK - what statistics are you collecting? If you want some comments or feedback leaving somewhere please just let me know, your help has been brilliant.

 

[sergioMabres]

Re: Help needed in automating this process - conditional decision making

People I help, I like to know, I am retired so I amuse myself with maps, good luck with Brexit!
Always glad to help
Sergio

 

[TheRedCardinal]

Re: Help needed in automating this process - conditional decision making

Hi Sergio,

Could I pick your brains on this again please? I have incorporated this successfully into the spreadsheet now, but one further variant has come up that wasn't predicted, and that is if the value if column A is not present in both the Logistics and Sales Data.
According to your formula, this finds a difference of the same value of column C, and then distributes it according to weight - outcome is we get a doubling of that value.

I amended the formula slightly, as follows (my addition in bold):

=[B]IF(SUMIF(A:A, A2, C:C) - SUMIF(A:A, A2, D:D) = C2[/B], 0,IFERROR(ROUND(C2-(SUMIF(A:A,A2,D:D)-SUMIF(A:A,A2,C:C))*B2/SUMIF(A:A,A2,B:B),2),0))

My logic is to find the sum of any invoice reference in column C, and then subtract the sum of that same invoice reference in column D - and if that difference equals the Value in column C, then you can assume it is not present in the financial data. It therefore posts 0 value in the column, which is correct.

However what I can't quite work how to make it work if there is more than one instance of the invoice number in column A from the logistics data, but none in the financial data. On this basis the SUMIF subtracting C from D won't give the value of C (it will give the value of C(1) and C(2) etc)

So how can I modify that statement?

 

[sergioMabres]

Re: Help needed in automating this process - conditional decision making

Hi Red,
I have truble figuring it out where you use your formula, but with this formula in column G in Final Data sheet (see the test file)
I have solved the double amount error and the 0 error when no invoice number

[COLOR=#444444][FONT=Calibri]=IFERROR(ROUND(F6-(SUMIF('Raw Data'!A:A,A6,'Raw Data'!G:G)+SUMIF('Raw Data'!A:A,A6,'Raw Data'!F:F))*E6/SUMIF(A:A,A6,E:E),2),0)
[/FONT][/COLOR]

Cheers
Sergio

 

[TheRedCardinal]

Thanks Sergio!
I fixed it myself too with a rather obvious change to my formula.

When comparing the values of the cells, I included an extra sumif instead of just a plain reference to a specific cell:

=IF(SUMIF(A:A, A2, C:C) - SUMIF(A:A, A2, D:D) = [B]SUMIF(A:A, A2, C:C[/B]), 0,IFERROR(ROUND(C2+(SUMIF(A:A,A2,D:D)-SUMIF(A:A,A2,C:C))*B2/SUMIF(A:A,A2,B:B),2),0))

This seems to work too, although yours looks much neater!

 

[sergioMabres]

Wonder full!