Manipulate complex excel

[aqiff]

I have a very complex output in Excel format. I am stucked.

Input

Output

Conditions:

• if invoicenocol meets amountcol, it means it is within the same page, else, there are two pages for it

• datecol needs to be the same for all rows within the same invoiceno

• containerno needs to be the same if there are two items for description and amount below the containerno

• Only take description and amount. Leave the description where the amount is empty

Below is the sample Excel
Invoice_Input.xlsx (10.2 KB)
Invoice_Output.xlsx (10.4 KB)

[Nithinkrishna]

Hey @aqiff

What’s the challenge you are facing here, please ?

Thanks
#nK

[aqiff]

I got confused in using the For each row data table, the confusion here is, how can I update the data table to be like the output ?

[Nithinkrishna]

Hey @aqiff

That will be pure logic.

For you it needs to conditional logic inside For-each with variables storing the data as you go through the iteration.

For example, Let’s say you are iterating through rows and you found a non-empty column you just add it to your new output data row and keep on doing the same. At the end of iteration you check if your output data row is all filled and if yes move to next row and continue iteration else simply continue iteration.

Hope this helps.

Thanks
#nK

[aqiff]

I tried to develop but it does not come out as what I have intended

[ashwin.ashok ((TheCodingTheory))]

Hi @aqiff ,

I’m not entirely sure if this works, but could you give it a try?

FormatComplexExcelData.xaml (20.7 KB)
Invoice_Input.xlsx (12.1 KB)

Kind Regards,
Ashwin A.K

[aqiff]

Wow this is what I want ! I have tried my solution, but using a lot of if statement here. However, the output is closed but not exactly what I want. But your solution is exactly like what I want !

[saurabhB]

Hi @aqiff , can you try if this is giving correct results, (for sample input output its working fine)
Thank you,
Sb
Main.xaml (27.1 KB)

[aqiff]

hi @saurabhB this is working too !