Modify Excel Input

[hurmet.noka]

Hello Guys,
I hope you are well.

I have the attached export, which needs to be modified.
This is the input

Basically I am interested only in rows which do not contain a “-” in the total Account sum.
Now If I apply the filter as in the screenshot.

it removes the row which belongs to total if it does not contain “-” and gives me the result as in the screenshot.

But that way I get rows which belong to other accounts. So Is there a smart way to manipulate this input to remove all rows which belong to an account with a positive total instead of removing singular rows.
Desired output would be:

So task is to remove all rows which belong to an account with a negative total.
export.XLSX (18.1 KB)

Thank you so much

[hurmet.noka]

No replies?
Is this community still kicking?

[Irene]

Hi,

I’m not sure whether there’s an elegant solution to this, other than looping through and manipulating the data table.

One way I can think of - which is not elegant but will work - is the following:

• Read the Excel sheet as a DataTable
• Add a new column to the DataTable
• For each row, update the new column to match the corresponding Account Number
• Find all lines where the Account Number is not empty (basically just the yellow lines) and where the total is negative → From these lines, extract the Account Number
• Delete all lines matching the Account Number you’ve found (this is now doable because each line will hold the Account Number it refers to)

It’s a multi-step approach anyway.

[hurmet.noka]

Thank you so much Irene