Omitting a data row

[EvansM]

Hello Community,

Please refer to the attached spreadsheets.

On the attached spreadsheets, i have input and output data. If you look in my input spreadsheet, i have 2 rows highlighted for the BILLMOYR of 1907. NOTE that the BILLDATE for these two rows are not the same.

What i want is that if the BILLMOYR column has the same number(ex. 1907) then i only want to show my output with the highest date on the BILLDATE column. (ex. in the attached spreasheets, i want to see 190719 for the BILLMOYR of 1907)
The row with lower BILLDATES should be deleted and not show on my results.( Refer   MBRHIST - Output Data.xlsx)

Please help me on how to handle this scenario.

Thanks.

[BrandonB]

You can use a sort tool to get BILLDATE descending and then use a unique tool with the BILLMOYR checked so only the latest passes through. 

[EvansM]

@BrandonB  Thanks for your help but did not work.

[AbhilashR · Accepted solution]

Hi @EvansM, does the approach in the attached solution work for you? I used the summarize tool to identify max billdate for each billmoyr and joined it back to the original dataset to exclude unwanted billmoyr with old date. 

Question about the sample output file you provided - should billmoyr 1904 be treated differently? It has repeated rows by different dates, yet the output file has both in them.

[EvansM · Accepted solution]

@AbhilashR Thank you very much for your help.BILLMOYR of 1904 should not treated differently. It should treated same as 1907. 

Sorry I didn't catch that on sample data.