"Flag" row of duplicate entries with the greatest date

**mattmickle** · 06-30-2015, 10:13 AM

I have some data that I need to keep whole (meaning I can't use Remove Duplicates and I don't want to do too much sorting), but need a way of "flagging" duplicate values with the greatest date.

For Example:
ROW RECORD ASSIGNED
0000123456 6/17/2015
3 0000123456 6/24/2015
4 0000234567 6/17/2015
5 0000345678 6/24/2015

In column A, I want to "flag" each unique value, but where it finds duplicates in column B, only flag the one with the greatest date. By "flag", I mean return the Row each unique (and greatest unique) value resides. Therefore in the above, cell A2 would be blank because it is a duplicate value to row 3 with a lesser date.

I can then use the Row number as a unique value to lookup the data for reporting. Does this make sense?

**nigelbloomy** · 06-30-2015, 10:52 AM

Does this do what you are looking for?

Please Login or Register  to view this content.

**mattmickle** · 06-30-2015, 01:12 PM

Nigel,
Thanks for the reply. No, it doesn't work. It gives some row values, but not for all unique rows (or greatest date within duplicate rows).

**nigelbloomy** · 06-30-2015, 01:20 PM

Can you upload a spreadsheet showing where the formula doesn't work and what the expected outcome should be?

**ChemistB** · 06-30-2015, 01:29 PM

With data in colums A and B, in C2, use this ARRAY formula copied down

=IF(COUNTIF($A$2:$A$5,A2)>1, IF(MAX(($B$2:$B$5)*($A$2:$A$5=A2))=B2,"X",""),"X")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

See attached.

**mattmickle** · 06-30-2015, 01:51 PM

Ok, Nigel, Sorry! It did work...I just screwed up part of the formula. Thanks!

**nigelbloomy** · 06-30-2015, 01:55 PM

Oh good. I couldn't figure out what would have been wrong.

**newdoverman** · 06-30-2015, 02:08 PM

Removed by author.

**mattmickle** · 06-30-2015, 03:56 PM

CHemistB,
Your formula totally ignores duplicates, rather than returning the value of one of the duplicates with the greatest date.

**ChemistB** · 06-30-2015, 04:06 PM

My formula puts an X next to all unique values and next to the duplicate with the highest date.

**newdoverman** · 06-30-2015, 04:11 PM

Removed by author.

**mattmickle** · 06-30-2015, 04:11 PM

Nigel,
I was wrong...it's not working.
Attached is a duplicate entry. It's supposed to be looking at duplicate values in column E (Unique ID) then returning the row of the largest date.

the formula returns a value for the 3/31/2014 entry rather than the 6/17/2015 entry.

ideas?

**nigelbloomy** · 06-30-2015, 04:17 PM

You switched the sign for N2. It needs to be greater than instead of less than. This one works:

Please Login or Register  to view this content.

**mattmickle** · 06-30-2015, 04:25 PM

i had changed that because it seemed to be grabbing the lesser of dates, but i just tried copying your exact formula. it works for one set of duplicates, but not a second. look at McCormick and Hanley in this workbook. for McCormick, it grabs the 6/23/2014 date instead of the 6/22/2015 date, but for Hanley it grabs the correct date.

**mattmickle** · 06-30-2015, 04:32 PM

Chemist,
Here's your formula in my spreadsheet. It skips over the duplicates in every circumstance.

**nigelbloomy** · 06-30-2015, 04:40 PM

For some reason your dates in column N are not actual dates, they are being seen as text within the formulas. This fixes that:

Please Login or Register  to view this content.

**ChemistB** · 06-30-2015, 05:03 PM

Your dates are entered as text so the formula doesn't work. Here, I converted them to numbers.

Alternately, you can modify the formula to

=IF(COUNTIF($E$10:$E$1509,E10)>1, IF(MAX(($N$10:$N$1509)*($E$10:$E$1509=E10))=N10+0,"X",""),"X")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**newdoverman** · 06-30-2015, 05:08 PM

Removed by author.

**newdoverman** · 06-30-2015, 05:31 PM

Removed by author.

**mattmickle** · 07-01-2015, 08:48 AM

Nigel,
Again, thank you for your time on this. UNfortunately, once I expand the ranges beyond the current data set, your formula bombs. IN the spreadsheet I sent you, expand the range to 10:16 and you get #VALUE error. I've got my spread sheet set to 1500 rows of information because this will be used week to week with varying amounts of data.

**mattmickle** · 07-01-2015, 08:53 AM

Chemist,
Thank you for your time! The second formula worked perfectly. I definitely appreciate your time!

Matt

**newdoverman** · 07-01-2015, 09:00 AM

Removed by author.

**nigelbloomy** · 07-01-2015, 09:34 AM

You are correct. If DATEVALUE finds an empty cell, it will return an error. Chemist's method of adding 0 doesn't have that problem. So this formula would now work with all 1500+ rows even if there are empty cells in the range.

Please Login or Register  to view this content.

**nigelbloomy** · 07-01-2015, 09:39 AM

For helpful learning material, I found this that shows easy ways to convert dates stored as text to numerical values that Excel can use to do calculations.

http://excelsemipro.com/2010/08/the-...ng-text-dates/

**mattmickle** · 07-01-2015, 09:48 AM

thanks for all your help!

"Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Re: "Flag" row of duplicate entries with the greatest date

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions