Find Duplicate (Multiple Columns), Count only most recent Row - Excel 2007

[armbands1]

Hello,

I am struggling with an Excel formula and was hoping someone would be able to assist me with!

What I am trying to do:

Match columns A ('Name') and B ('Company'), then only count the row with the most recent date (in column D -'Date' ) by putting 1 in column F ('Count') and 0/blank in column F ('Count'), for the remaining rows with the matched A and B columns.

Please see attached worksheet for an example.

The purpose is to build an Aging Table via a Pivot Table (as per the example worksheet).

Please let me know if this does not make any sense!

Thank you for your time!

[benishiryo]

hi armbands1, welcome to the forum. try this array formula in F2 & copy down:

=IF(A2&B2&MAX(IF($A$2:$A$9&$B$2:$B$9=A2&B2,$D$2:$D$9))=A2&B2&D2,1,"")

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it

[armbands1]

Amazing! I won't even try and tell you how long I have been stuck on this.

Thank you, this works perfectly.