Suppose I have an Excel sheet that looks like this:
| ADVISOR # | ADVISOR NAME | CUSTOMER |
| 123 | Steve | Rose B |
| 123 | Steve | Anne W |
| 123 | Steve | Ralph M |
| 456 | Steve | Charles R |
| 456 | Steve | Adelaide A |
| 456 | Steve | Julian R |
| 456 | Steve | Cathy D |
| 456 | Stephen | Isabella B |
| 789 | Clay | Stephen C |
| 789 | Clay | Beth B |
| 1011 | Robert | Carolyn M |
| 1011 | Robert | Sharon P |
| 1011 | Robert | Jill H |
| 1011 | Robert | Marissa C |
| 1011 | Robert | Sharon P |
| 1011 | Robert | Staci H |
| 1011 | Robert | Elizabeth H |
| 1213 | Daniel | Gia B |
| 1415 | Richard | Debra S |
| 1415 | Richard | Maureen D |
| 1617 | Timothy | Beverly K |
| 1617 | Timothy | Charles R |
| 1819 | Bryan | Deborah M |
| 1819 | Bryan | Jeremy R |
What I want to do is have a formula or macro that will look at the Advisor # in column A, then look at the customer name in column C. If the name in column C is a duplicate (in red in my example), AND the Advisor # is the same for that customer, then delete the row for one of the duplicates (the second entry would be preferred, as it occurred later in time – there are several other columns, including a date column). If the advisor number is DIFFERENT for a duplicate customer, then do nothing.
So in my example above, the row with the 2nd Sharon P would be deleted, but the row with the 2nd Charles R would be ignored. My original data set will have 20,000 rows or more.
Can you help?
Hi Tom
Excel has a remove duplicate option.
From the Data tab select Remove Duplicates, untick Advisor Name and click OK.
Hope this helps.
Sunny
Perfect, thanks!
