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!

 

[sergioMabres]

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

Hi Red,
Here is an idea: Instead of using a MACRO why don't you use a formula like

G2 is =SUMIFS('Raw Data'!F:F,'Raw Data'!A:A,'Final Data'!A2,'Raw Data'!E:E,'Final Data'!E2)-VLOOKUP(A2,Pivot!A:D,4,0)*E2/SUMIF(A:A,A2,E:E)

With this formula you get the same results that you are expecting:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Invoice[/TD]
[TD]Country/region[/TD]
[TD]Delivery terms[/TD]
[TD]Commodity[/TD]
[TD]Weight[/TD]
[TD]Amount[/TD]
[TD]New
Amount[/TD]
[/TR]
[TR]
[TD]ILSI023449[/TD]
[TD]CZE[/TD]
[TD]FCA[/TD]
[TD]34021190[/TD]
[TD]90.70[/TD]
[TD]425.32[/TD]
[TD]425.32[/TD]
[/TR]
[TR]
[TD]ILSI023520[/TD]
[TD]DEU[/TD]
[TD]DAP[/TD]
[TD]29299000[/TD]
[TD]24,780.00[/TD]
[TD]36,564.88[/TD]
[TD]36,564.88[/TD]
[/TR]
[TR]
[TD]ILSI023536[/TD]
[TD]DNK[/TD]
[TD]DAP[/TD]
[TD]29209070[/TD]
[TD]24,980.00[/TD]
[TD]27,558.15[/TD]
[TD]27,558.15[/TD]
[/TR]
[TR]
[TD]ILSI023612[/TD]
[TD]BEL[/TD]
[TD]DAP[/TD]
[TD]39119099[/TD]
[TD]14.50[/TD]
[TD]2,580.50[/TD]
[TD]2,580.50[/TD]
[/TR]
[TR]
[TD]ILSI023457[/TD]
[TD]IRL[/TD]
[TD]CPT[/TD]
[TD]34021190[/TD]
[TD]108.80[/TD]
[TD]2,406.98[/TD]
[TD]562.74[/TD]
[/TR]
[TR]
[TD]ILSI023457[/TD]
[TD]IRL[/TD]
[TD]CPT[/TD]
[TD]34021190[/TD]
[TD]204.10[/TD]
[TD]2,406.98[/TD]
[TD]951.12[/TD]
[/TR]
[TR]
[TD]ILSI023457[/TD]
[TD]IRL[/TD]
[TD]CPT[/TD]
[TD]34021300[/TD]
[TD]220.00[/TD]
[TD]2,406.98[/TD]
[TD]893.11[/TD]
[/TR]
[TR]
[TD]ILSI023797[/TD]
[TD]ESP[/TD]
[TD]DAP[/TD]
[TD]38119000[/TD]
[TD]26,440.00[/TD]
[TD]42,983.79[/TD]
[TD]42,983.79[/TD]
[/TR]
[TR]
[TD]ILSI023527[/TD]
[TD]ITA[/TD]
[TD]CPT[/TD]
[TD]34021190[/TD]
[TD]3,628.00[/TD]
[TD]14,744.14[/TD]
[TD]14,744.14[/TD]
[/TR]
[TR]
[TD]ILSI023754[/TD]
[TD]ITA[/TD]
[TD]DAP[/TD]
[TD]38119000[/TD]
[TD]66.00[/TD]
[TD]1,993.75[/TD]
[TD]1,993.75[/TD]
[/TR]
</tbody>[/TABLE]

Here is the test sheet: https://1drv.ms/x/s!AovCE1fDrrdSnVIvdq48RZ6QbXdO?e=FCyEV5

Cheers
Sergio

 

[TheRedCardinal]

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

Sergio,
This worked almost perfectly thanks. I'll incorporate it into the bigger spreadsheet.

However it was thrown up two anomalies when I reconciled the data:

If you look at the rows for ILSI023798, in the Pivot Table, both values match at 15,507.46.
There are ten lines with that reference number in the Final Data tab.
But the values pulled in come to 31,614.80 - which isn't even double the original amount, so I can't put it down to the calculation being duplicated.

The same issue happens for ILSI024045 which has a matched value of 66,264.99 in the pivot table, yet in the final data has come out at 165,211.33.

As the rest of the lines work so well, I can't figure out what is different about these two.

 

[sergioMabres]

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

Hi Red,
There was a problem with same weight, the problem was solve in this test file https://1drv.ms/x/s!AovCE1fDrrdSnVbl5DsisiaAjQDd?e=Z7guEz

I had to change the formula to sheet Raw Data column H

Where H2 is =ROUND(F2-VLOOKUP(A2,Pivot!A:D,4,0)*E2/SUMIF(A:A,A2,E:E),2)

​

Invoice	Country/region	Delivery terms	Commodity	Weight	Amount	VAT Value	New Amount
ILSI023449	CZE	FCA	34021190	90.7	425.32		425.32
ILSI023520	DEU	DAP	29299000	24780	35435.4		36564.88
ILSI023536	DNK	DAP	29209070	24980	27558.15		27558.15
ILSI023612	BEL	DAP	39119099	14.5	2580.5		2580.5
ILSI023457	IRL	CPT	34021190	108.8	402.58		562.74
ILSI023457	IRL	CPT	34021190	204.1	650.67		951.12
ILSI023457	IRL	CPT	34021300	220	569.25		893.11
ILSI023797	ESP	DAP	38119000	26440	40947.63		42983.79
ILSI023527	ITA	CPT	34021190	3628	14744.14		14744.14
ILSI023754	ITA	DAP	38119000	66	1993.75		1993.75

<tbody>
</tbody>

No errors now for same weight in invoices (I hope)
I hope it helps
Cheers
Sergio

 

[TheRedCardinal]

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

I'll check it out now - thanks so much.

I notice you've put it in the Raw Data tab - does it belong in the "Final Data" tab instead? I am just trying to follow the workings through.

 

[sergioMabres]

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

No, I use Raw Data sheet to calculate New Amount (Amount+VAT), I was not able to use the Final Data sheet bc in Final Data you do not have the column amount by item that it is to say column F
Even more, I think with some working, you can do away with sheets Pivot and Final Data.
Cheers
Sergio

 

[TheRedCardinal]

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

Hi Sergio,
Really appreciate your time on this one.

I am not sure I understand what you mean though? The data in "Final Data" is a direct copy of the data in "Raw Data" - in fact it is copied in at the same time using the Macro.
Columns A:F are exactly the same in both?

I would be interested in how you could get rid of the Pivot Table, but the Final Data tab is needed because it is the one that is presented as part of the workings. You could of course use the Raw data but it contains extra data (the Orange section, with the value in column G).

As I look through the formula, I can't see why it wouldn't work in Final Data - I'll give it a go now myself, but it seems like you've already figured out why it doesn't work, even though in my mind the data is exactly the same (or should be!)

Thanks again!

 

[sergioMabres]

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

In Raw Data row 6 is
ILSI023457 IRL CPT 34021190 108.8 402.58
In Final Data raw 6 is
ILSI023457 IRL CPT 34021190 108.8 2406.98

As you can see column F for row 6 is in one case is 402.58 and in the other sheet is 2406,98
I need 402.58 to add the proporcional VAT to the item amount
Cheers
Sergio

 

[TheRedCardinal]

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



Of course it is different in Final Data - so sorry Sergio! The version I sent you contains the Lookup to the Pivot Table to replace the original data.
This is what leads to the error.

I will re-run the whole macro, so the data then matches in Final Data tab, then add your formula into a new column.
I expect this will work perfectly.

I am interested in how you think you could manage this without the Pivot Table - is that something that could be easily managed? The pivot table gives me cause for concern in the whole process once end users get their hands on it.

I also plan to drop your formula into VBA with a Value = Res function - I don't think this will be a problem? The size of the tables will vary month to month and I am trying to get away from my users copying and pasting formulae around etc.

Once again, your help here has been invaluable - thank you!

 

[sergioMabres]

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

Here it is without the Pivot sheet, it is gone, redundant, and of course I get the same results with this new formula, where we use two SUMIF to do the job of the pivot table, see:

H2 is =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)

I deleted the Pivot Sheet in my test file https://1drv.ms/x/s!AovCE1fDrrdSnVbl5DsisiaAjQDd?e=3IMdfv

Invoice	Country/region	Delivery terms	Commodity	Weight	Amount	VAT Value	New Amount
ILSI023449	CZE	FCA	34021190	90.7	425.32		425.32
ILSI023520	DEU	DAP	29299000	24780	35435.4		36,564.88
ILSI023536	DNK	DAP	29209070	24980	27558.15		27,558.15
ILSI023612	BEL	DAP	39119099	14.5	2580.5		2,580.50
ILSI023457	IRL	CPT	34021190	108.8	402.58		562.74
ILSI023457	IRL	CPT	34021190	204.1	650.67		951.12
ILSI023457	IRL	CPT	34021300	220	569.25		893.11
ILSI023797	ESP	DAP	38119000	26440	40947.63		42,983.79
ILSI023527	ITA	CPT	34021190	3628	14744.14		14,744.14

<tbody>
</tbody>

I hope this helps
Sergio