Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Automating conditional decision making

  1. #1
    Board Regular
    Join Date
    Jul 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Automating conditional decision making

    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=1u3...oWGCEGJZ7Aqpeu

    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!

  2. #2
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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:

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

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

    Cheers
    Sergio

  3. #3
    Board Regular
    Join Date
    Jul 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  4. #4
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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!AovCE1fDrrdSnVbl...AjQDd?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

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

  5. #5
    Board Regular
    Join Date
    Jul 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  6. #6
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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 by sergioMabres; Aug 28th, 2019 at 08:21 AM.

  7. #7
    Board Regular
    Join Date
    Jul 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!

  8. #8
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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 by sergioMabres; Aug 28th, 2019 at 11:08 AM.

  9. #9
    Board Regular
    Join Date
    Jul 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!

  10. #10
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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!AovCE1fDrrdSnVbl...AjQDd?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

    I hope this helps
    Sergio

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •