Copying (almost) Duplicate Rows

[aledapita]

I am trying to MOVE rows with duplicates in one particular column.

Column A (Store Number) has some duplicates in it (10%). The other information in the row of the duplicate MAY NOT match the other the first row.

EX: Not the real data but you get the idea

Store Date Color
123 9/4 Blue
771 9/10 Yellow
771 8/17 Yellow
500 9/15 Black
500 7/9 Purple


I need to KEEP 1 of each Store number with the Most Recent date. I am savvy and know how to sort! However, sometimes the other columns don't match (color's might not be the same).

How do I keep 1 of each of the main column (store) with the most recent date without any duplicates? The "extra" duplicates must be copied somewhere, they can't be deleted.

I have attempted an Advanced Filter selecting "Copy" "no criteria" and "Unique". It selects the entire sheet as the other cells are not excatly identical.

Any Suggestions?

 

[doubledaffy]

If you have a lot of rows of data, you might want to consider a VBA script.

Otherwise, do a two level sort. Select all of that data you want (X rows by Y columns) and sort it first by store number and then by date. It should all come out matching and in the order you want. Then you can cut and paste to your hearts desire.

 

[aledapita]

Thanks for the sorting suggestion. It is currently formatted by store then by date. There are too many to cut and paste so I'm looking for a faster way...

 

[doubledaffy]

Well, barring a VBA script, here's what I suggest:

Compile a list of all the store names in a new sheet column A. Then in the columns to right you can do an OFFSET/MATCH to pair up the right data with the most recent date of your sorted data. With some named ranges (dynamic ones would be helpful) you'd be on your way to getting this done a lot faster. It isn't copying and pasting so much as selecting the right data.