[CODE] or [FORMALA] To get unique ID's based on latest closed date

[Richavlaues]

Hi Experts,

In my excel spreadsheet, I have two columns "ID" and "Closed Date". I want only unique ID's to show based on latest Closed Date. So would like to know if there is any formula or VBA code that does this.

Am attaching sample data that contains 10 records, but in real time I have more than 50,000 records. Hence looking for formula or VBA code.

I appreciate any help provided.

Thanks,
Richa

[Special-K]

Try

=MAX(IF(A2=A$2:A$11,B$2:B$11))
Array formula, use Ctrl-Shift-Enter
and copy down

[Arkadi]

You say you want to want to show IDs based on latest closed date... meaning only show 1 ID? which is the latest one? or ignore time, and show all that match the latest date? I assume this would go in another column?

[Arkadi]

Nevermind... just understood

[Special-K]

Format column C as general
Format column D as date and time

in C2
=IFERROR(INDEX($A$2:$A$11, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$11), 0)),"")
Array formula, use Ctrl-Shift-Enter
and copy down

in D2
=IF(C2<>"",MAX(IF(A2=A$2:A$11,B$2:B$11)),"")
Array formula, use Ctrl-Shift-Enter
and copy down

[Richavlaues]

Update:
Meanwhile was searching for similar questions on internet and found a link here that takes me closer to solution but not really close enough.

This is what I tried:
Created another column and entered below formula:

Please Login or Register  to view this content.

But the result am getting is only for last record as that's the latest closed date overall but am still not getting how to modify the formula that works for my requirement.
Am getting result as shown in below image:
UniqueonDate.jpg
But am trying to get as per below image:
UniqueonDate1.jpg

Thanks,
Richa

[Special-K]

=IF(MAX(IF(A11=$A$2:$A$11, $B$2:$B$11))=B11, "Newest", "")
Array formula, use Ctrl-Shift-Enter

[Richavlaues]

Format column C as general
Format column D as date and time

in C2
=IFERROR(INDEX($A$2:$A$11, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$11), 0)),"")
Array formula, use Ctrl-Shift-Enter
and copy down

in D2
=IF(C2<>"",MAX(IF(A2=A$2:A$11,B$2:B$11)),"")
Array formula, use Ctrl-Shift-Enter
and copy down

Thanks and that was quick. Can you please let me know if I can get values in corresponding cells instead of showing results as stack on top rows?

[Richavlaues]

=IF(MAX(IF(A11=$A$2:$A$11, $B$2:$B$11))=B11, "Newest", "")
Array formula, use Ctrl-Shift-Enter

Thanks. I tried above formula and getting null values.

[Special-K]

in C2
=IF(MAX(IF(A2=A$2:A$11,B$2:B$11))=B2,"Newest","")
Array formula, use Ctrl-Shift-Enter
and copy down the column

[Richavlaues]

Is it possible to get results like this as shown in below image?
UniqueonDate1.jpg

[Arkadi]

I tried pasting (Special K's) this in the formula bar and pressing ctrl-shift-enter to apply it (while in C2), then copied down, and it works as you asked:

Please Login or Register  to view this content.

The only change I made was chaning the A11=... to A2=... and =B11 to =B2 (in case you were using his exact formula in row 2)

[Richavlaues]

in C2
=IF(MAX(IF(A2=A$2:A$11,B$2:B$11))=B2,"Newest","")
Array formula, use Ctrl-Shift-Enter
and copy down the column

Wow you are awesome and Thanks for all the help. This formula worked.

Can I use Record Macro to have this in vba code, instead of doing it manually?

Again, Thank you so much. I really appreciate your help and time.

Thanks,
Richa

[Special-K]

Am no VBA expert.
You can try it but VBA produced by Macro Recording is usually inefficient.

[Arkadi]

Try something like this if you want to apply it via VBA:

Please Login or Register  to view this content.

[Richavlaues]

I tried pasting (Special K's) this in the formula bar and pressing ctrl-shift-enter to apply it (while in C2), then copied down, and it works as you asked:

Please Login or Register  to view this content.

The only change I made was chaning the A11=... to A2=... and =B11 to =B2 (in case you were using his exact formula in row 2)

Thanks Arkadi. My bad, I just copied down the formula instead of using ctrl+shift+enter keys

Thanks,
Richa

[Arkadi]

Glad that worked out

My previous post (last one on page 1) should help you apply the array formula to the range using VBA

[Richavlaues]

Try something like this if you want to apply it via VBA:

Please Login or Register  to view this content.

Perfect and Thank you so much. I really appreciate all the help and time for helping me with this. I would still be struggling on how to start. Thanks for prompt response.

Thanks,
Richa

[Arkadi]

Glad we could help you sort it out

Thanks for the feedback and for marking the thread as solved.