CODEWITHSUNDEEP
excelexcel-formula
Boris Delormas
Boris Delormas

Reputation: 2549

Get last unique row in Excel

I'm having a table looking like this:

    id1    |    id2    |    dateUpdate
==========================================
    aaa    |    111    |    2016-01-01
    aaa    |    111    |    2016-01-02
    aaa    |    222    |    2016-01-05
    aaa    |    222    |    2016-01-15
    bbb    |    333    |    2016-01-05
    bbb    |    444    |    2016-01-01
    ccc    |    111    |    2016-01-02

I'd like to get only the latest row for each id1/id2 couple:

    id1    |    id2    |    dateUpdate
==========================================
    aaa    |    111    |    2016-01-02
    aaa    |    222    |    2016-01-15
    bbb    |    333    |    2016-01-05
    bbb    |    444    |    2016-01-01
    ccc    |    111    |    2016-01-02

Thanks for your help!

Upvotes: 1

Views: 836

Answers (1)

skkakkar
skkakkar

Reputation: 2828

Screen shot showing functionUse Aggregate Function

=AGGREGATE(14, 6, 1/($A$2:$A$99=E2)*($B$2:$B$99=F2)*($C$2:$C$99), 1)

You have to put the unique combinations in column E2 and Column F2 downwards You have five unique combinations 

COLUMN A      COLUMN B 

aaa           111
aaa           222
bbb           333
bbb           444
ccc           111

And Aggregate Funtion is to be put in column G2 and fill down downwards.

=AGGREGATE(14, 6, 1/($A$2:$A$99=E2)*($B$2:$B$99=F2)*($C$2:$C$99), 1)

Regarding Syntax 14 is for Large value that is the for example larger for first aaa 111 combination that is #02-01-2016# Next digit 6 in formula is for ignoring errors You can quickly get the pseudo-Last value using the AGGREGATE function.. This is a standard non-array formula that does NOT require Ctrl+Shift+Enter. AGGREGATE was introduced with Excel 2010.

($A$2:$A$99=E2) checks which value of column A matches with the first unique value of column A of unique combination of A & B This is converted to an array of logical values which gives an array of True Or False. Dividing 1 by these logical values give either 1 or DIV# error. Similar process is adopted for Column B values mentioned in column F. I have taken data limited to 99 rows. If it more number of rows you change the figure of 99 to a higher figure as per your requirements. HTH

EDIT You can also go for pivot table solution as suggested by @Tim Biegeleisen in his comments. Snapshot given here-under illustrates that approach.

pivot table approach

id1 and id2 are taken as filter fields and Maximum Value of dateUpdate is chosen in the value field.

Upvotes: 1

Related Questions