Hi everyone, Please help with the attached problem.

[SP3000]

Hi everyone,

Please help with the attached problem. I have made 2 sheets in the attached file, one for the input and second sheet is the desired output sheet. 

Thank you in advance!!

[apathetichell]

Hi,

one quick clarification: as explained the only way anyone, ACE or otherwise can derive the proper AMT values is if there are static answers for the AMT for 1,2,3,4

As explained they are:

1 - 1000

2 - 500 or (invoice amount - 1000 if there are only two entries)

3 - 200 (or invoiced amount - 1500 if there are only 3 entries)

4 - invoiced amount - 1700

That is not the case. In one scenario two entries become 1000 and 500 and in another they become 1300 and 200. If you cannot explain how this allocation occurs I do not think anyone will be able to replicate your numbers in AMT.

[TheOC]

hey @SP3000 

just before i jump into this, i'm going to have to thank @mceleavey (for the grouped record id tool - and generally being awesome), @AngelosPachis for his answer here , and @danilang for thinking outside the box. I can only take a guilt-free 10% of credit for this one 😂

The key to this solution, was actually to bring in the xlsx file as a zip file - my mind was blown!

I bring the file in as both a zip and xlsx, and the zipped version actually has that formula:

so i strip that from this stream, and then join it back with the original data.

Then it was simply matching the supplier ID to the factors of the AMT. This was made easy by @mceleavey 's grouped record ID tool. I believe i'm using a slightly outdated version of this however, so do contact him if you're interested in this macro!


And now for the output:

Which i believe to be correct. I've attached this as an exported workflow, so it should run without any changes needed, but give me a shout if anything is wrong!


Hope this helps!
TheOC