Complex regex problem

[SP3000]

Please help with the attached problem. 

The logics are:

1) if there are 2 supplier ids and 2 distribution in the amt field then the first supplier id will get the first amt distribution and likewise for the second supplier id. And same for 3 supplier ids and 3 amt distributions then it will be updated as per the amt distributions. 

For eg. Supplier ids      Input amt               Output amt

              123                 =1000+500                 1000

               456                                                     500

2) If there are 2 supplier ids but 3 amt distribution then 1st supplier id should be updated with the first amt distribution and for the 2nd supplier id the amt will be the addition of the 2nd and 3rd amt distribution.

For eg. Supplier ids      Input amt               Output amt

              123                 =1000+500+200         1000

              456                                                     700

2) If there are 3 supplier ids but 4 amt distribution then 1st supplier id should be updated with the first amt distribution and for the 2nd supplier id the amt will the 2nd amt distribution and the 3rd supplier will be the addition of the 3rd and 4th amt distribution.

For eg. Supplier ids      Input amt                              Output amt

              123                 =1000+500+200+400             1000

              456                                                                  500

              890                                                                  600

Thank you in advance

[TheOC]

hey @SP3000 

I've fixed my original workflow, and added the workflow you sent attached. When adding a different excel sheet, make sure your configuration is set the exact same as it is for the zipped version currently:

Cheers,
TheOC

[SP3000]

Hi @TheOC ,

Please kindly bear my ignorance, as I am still unable to figure out how to bring data in as ZIP to keep the XML of the formula. I have uploaded a copy of your workflow which I am unable to run. If you can kindly provide the steps to do that or a short video on how to do it then I'll be very grateful. 

And also there is empty cell for def and kgf suppliers in the output. It would be great if you can also fix it as per the below output.

Supplier	Supplier ID	Amt
abc	123	1000
abc	789	500
xyz	455	2000
xyz	890	400
xyz	100	600
def	200	1000
def	310	500
kgf	144	1000
kgf	456	500
kgf	890	600

My sincere gratitude for your kind support.

Regards,

SP3000

[TheOC]

hey @SP3000 

no apologies needed, at all!

I am on UK time, do you have any time at all tomorrow/later this week to jump on a call? Happy to discuss this and get this working for you whilst sharing screens. Feel free to shoot me a private message (or email - coyle@bulien.co.uk), and we can arrange a quick call sometime!

If this isn't ideal, let me know and ill try to have another look at creating it in a way that can be transferred to you. This may be something convertible into a macro to make it so the input tool configuration does not have to be changed when changing the file.

Cheers,
TheOC