Links to >>>>>>
This TechTIP is one you may want to add to your personal bag of tricks. Frequently we need to combine multiple lists in Excel, and then eliminate the duplicate entries. Excel can do that with Filter/Advanced/Unique records. But you don't get to select, or see what records are eliminated.
Here's a page torn from the Corbitt Associates Excel recipe book, that will do the trick. The first step is to sort on the column you wish to find, and eliminate duplicates. Next, assume your list begins at C12, to agree with our formula. Now, in D13, enter this formula, =IF(EXACT(C13,C12),IF(C13<>"","*DUPE*",""),"")
Copy that formula down as far as the list to be de-duped.
The first IF statement does the heavy lifting, and says if the contents of my row, in the column to my left are exactly the same as the contents in the row above it in the column to the left, then it is a duplicate. The second IF prevents *DUPE* from appearing if your 'items' column contains blanks.
You can not directly sort on the *DUPES* column, because it is a formula and depends on the current sort of the 'items' column. But here's the common work around for that situation that you probably already know. Copy the area containing your list and the *DUPE* formula. Then, decide where you want the copy to be placed, and Paste Special/Paste Values. Now you can sort on the *DUPE* column, because it is a text field, and not a formula dependent on the sort of the 'items' column.
Here's an example that shows what you should see. The first image shows what you should see on the screen, and the second image shows what you would see if you looked at the de-dupe formula in each cell.
Corbitt Associates provides sales, marketing and training capabilities, when you don't have resources available. Your resources may not have the required skill and experience, or they may be committed to another project. Don't let that program get behind, or go undone. Contact us, to be your extended team. When you can't Do It Yourself (DIY), we can Do It For You (DIFY).
If you wish to opt-out from this distribution list, click Unsubscribe and you will be automatically removed.
If you would like to contact us, please send email to: editor@corbittassociates.com
Corbitt Associates
3590 Lester CT SW
Lilburn, GA 30047-7504