Automating conditional decision making

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
62
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

Well-known Member
Joined
Feb 24, 2013
Messages
946
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:

InvoiceCountry/regionDelivery termsCommodityWeightAmountNew
Amount
ILSI023449CZEFCA3402119090.70425.32425.32
ILSI023520DEUDAP2929900024,780.0036,564.8836,564.88
ILSI023536DNKDAP2920907024,980.0027,558.1527,558.15
ILSI023612BELDAP3911909914.502,580.502,580.50
ILSI023457IRLCPT34021190108.802,406.98562.74
ILSI023457IRLCPT34021190204.102,406.98951.12
ILSI023457IRLCPT34021300220.002,406.98893.11
ILSI023797ESPDAP3811900026,440.0042,983.7942,983.79
ILSI023527ITACPT340211903,628.0014,744.1414,744.14
ILSI023754ITADAP3811900066.001,993.751,993.75

<tbody>
</tbody>

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

Cheers
Sergio
 

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
62
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

Well-known Member
Joined
Feb 24, 2013
Messages
946
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)

InvoiceCountry/regionDelivery termsCommodityWeightAmountVAT ValueNew Amount
ILSI023449CZEFCA3402119090.7425.32425.32
ILSI023520DEUDAP292990002478035435.436564.88
ILSI023536DNKDAP292090702498027558.1527558.15
ILSI023612BELDAP3911909914.52580.52580.5
ILSI023457IRLCPT34021190108.8402.58562.74
ILSI023457IRLCPT34021190204.1650.67951.12
ILSI023457IRLCPT34021300220569.25893.11
ILSI023797ESPDAP381190002644040947.6342983.79
ILSI023527ITACPT34021190362814744.1414744.14
ILSI023754ITADAP38119000661993.751993.75

<tbody>
</tbody>

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

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
62
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

Well-known Member
Joined
Feb 24, 2013
Messages
946
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
 
Last edited:

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
62
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

Well-known Member
Joined
Feb 24, 2013
Messages
946
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
 
Last edited:

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
62
Re: Help needed in automating this process - conditional decision making

:eek:

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

Well-known Member
Joined
Feb 24, 2013
Messages
946
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

InvoiceCountry/regionDelivery termsCommodityWeightAmountVAT Value New Amount
ILSI023449CZEFCA3402119090.7425.32 425.32
ILSI023520DEUDAP292990002478035435.4 36,564.88
ILSI023536DNKDAP292090702498027558.15 27,558.15
ILSI023612BELDAP3911909914.52580.5 2,580.50
ILSI023457IRLCPT34021190108.8402.58 562.74
ILSI023457IRLCPT34021190204.1650.67 951.12
ILSI023457IRLCPT34021300220569.25 893.11
ILSI023797ESPDAP381190002644040947.63 42,983.79
ILSI023527ITACPT34021190362814744.14 14,744.14

<tbody>
</tbody>

I hope this helps
Sergio
 

Forum statistics

Threads
1,081,577
Messages
5,359,731
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top