Is this possible in Excel?

[FastForward]

Hi Everyone: Is it possible to do the following in Excel? Looking and looking but couldn't find anything.
Thanks very much in advance!

In the attached, the 1st table (actually a spreadsheet) is the data I get, which is sorted by the Bill Number. Is there any possible way to apply formulas to give me the results in the 2nd table?

The criteria is:

• All rows that have a status of "approved" or "batched" or "exported" or "pending" should be included in the resulting table.
• Rows with a status of "rejected" or "draft" should be included in the resulting table only if the other rows with the same Bill Number does not have a status of approved, batched, exported, or pending,

 

[vletm]

FastForward​

Your threads title should be something else
... reread Forum Rules.
Some things are possible with Excel ...
... here one sample
I moved Your resulting data set to right side, that You can compare it
... with my J...K-columns sample results.

 

[FastForward]

FastForward​

Your threads title should be something else
... reread Forum Rules.
Some things are possible with Excel ...
... here one sample
I moved Your resulting data set to right side, that You can compare it
... with my J...K-columns sample results.

Hi @vletm Sorry about the thread title. I've been reviewing your solution for over an hour and still don't understand how you did this magic! Wow! Don't think I'll ever be able to understand those formulas. I tried recreating it except I added a few more rows to the bottom of the initial dataset. But when I do a copy on the first row of the "help" column and paste all the remaining rows (including the new ones I added) the results were the same as yours on the initial row set but all the new rows were set to TRUE which wasn't correct. Should I not be using a copy and paste? Any idea what I did wrong?
Anyway, I manually updated the values in the help column for the new rows, but when I did a copy and paste of the 1st cell in the result set, it created the exact same values you had, but again not including the new rows.
Really appreciate your help!

 

[vletm]

... over an hour?
... over a week?

You wrote something after Your did something ...

Any idea ... Try to find positive things ...

# do not touch 'help-column

Try to add this way
# add new bill number in the end of that table
# add status to same row
# add amount to same row
# repeat those three steps as many times as need

Try to delete this way
# select any cell that row You would like to delete
# with mouse - right click > Delete > Table rows

 

[FastForward]

... over an hour?
... over a week?

You wrote something after Your did something ...

Any idea ... Try to find positive things ...

# do not touch 'help-column

Try to add this way
# add new bill number in the end of that table
# add status to same row
# add amount to same row
# repeat those three steps as many times as need

Try to delete this way
# select any cell that row You would like to delete
# with mouse - right click > Delete > Table rows

Thank you