Filter Specific Rows from Data

Hello,

I'm hoping someone could assist in the best way to filter specific rows from a data set that aren't unique. Essentially I only want the last row that appears in the data set for the transaction for each unique line number. Sometimes the data has only one row and other times multiple rows.

Sample Row Filter.xlsx

Workflow

Tips and Tricks

Fuzzy Match

Accepted answers

MRapp

Does this work? Instead of adding another grouping, I have used the existing "Line Number" field to find the last row each "group".

However, I am not sure why the order has changed after applying the Sample Tool.🤔

All comments

TUSHAR050392

Hey @TaxTitan, Looking at your data, it looks like Transaction and Line number is the combination for which you want last records.

1. You can first rank your data using Multirow formula - You will name the column Rank, group by Transaction and Line number and in formula write - Row-1: Rank +1.

2. Once you have rank, use a summarize tool, group by Transaction and Line number and find max Rank.

3. Use a join tool and join on Transaction and Line number and Rank.

This way your last records will get joined and you will get the desired output. Hope this helps.

MRapp

Does this work? Instead of adding another grouping, I have used the existing "Line Number" field to find the last row each "group".

However, I am not sure why the order has changed after applying the Sample Tool.🤔

TaxTitan

Thank you so much this works for my data! I was thinking of using the summarize tool as well and using last. But I wasn't sure which would be better.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703