Omitting a data row

Question

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.

MBRHIST-Input Data.xlsx

MBRHIST-Output Data.xlsx

AbhilashR · Accepted Answer

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 Answer

@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.