Creating dynamic serial number

[SP3000]

Hi all,

Please help with the attached query. In the file, there are input and the desired output sheets.

Thanks,

SP

[atcodedog05 · Accepted solution]

Hi @SP3000 

You can do this by following the logic of the below workflow

1. using record ID to generate IDs

2. splitting values into rows using newline as delimiter

3. using mutirow formula to generate sub ID

4. using formula to generate serial number.

Hope this helps 🙂

[SP3000]

Hi,  

Thank you so much for your help. It works perfectly. 

I have another problem for which i couldn't find a solution. Can you please help? I really appreciate a lot!

[atcodedog05]

Hi @SP3000 

Is there way/logic how you are splitting the amount for each row. If you share the logic i can help you out.

[SP3000]

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

[suby]

Hi,

Could you please explain what the multirow formula does?

[SP3000]

Multirow formulae assigns unique serial numbers.