asked on

Remove duplicates in Excel

I have an Excel sheet with 18,000 rows, and 3 columns. Column "B" is a first name field, Column "C" is a last name field. I want to filter the duplicates from the sheet, but I need to filter by the first+last name fields. I can only find a way to filter out the duplicates from one column at a time. Any assistance would be appreciated.

ASKER CERTIFIED SOLUTION

SANTABABY

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aaron

ASKER

I've entered that formula into D1, selected "D" to highlight the entire column, hit paste; and it stalled for a a few seconds but nothing happened. I checked some of the cells that I can verify are duplicates and there is no entry in the "D" folumn.

experts1

Do As follows:

(1) Add a new column
(2) This column will display first name / last name combination
(3) Enter formula in new column as follows:
=B1 & " " & C1
The above formula will concatenate (join) the first and last names in this single column
This assumes that the first row is number 1
(4) copy this formula to all 18,000 rows of the new column
(5) you should now be able to use this column to filter the duplicate rows as required.

Regards

Aaron

ASKER

Is there an easy way to copy the formula to all rows? I think I'm missing something really easy here...

experts1

Certailny,

Let us assume your new column is "D"

(1) select the first row with formula "D1"
(2) right click mouse and select COPY
(3) select the cell in the next row below "D2" in same column
(4) scroll down to last row in column "D18000"
(5) Hold down SHIFT key, then select "D18000"
At this point you should see column "D" Highlighted
(6) right click anywhere in column "D" and select PASTE

Note: important to hold SHIFT key before clicking on "D18000"

Result should be all 18000 rows now have UNIQUE formula for each row in column "D"

SANTABABY

Could you please:
1. enter the formula in Cell D1
2. Select cell D1 and copy
3. Select cell D2 and the shift+select cell D18000 (to select the range).
4. Then paste.

Thanks.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello,

wouldn't it be much easier to use the Remove Duplicates button on the Data ribbon? For example in this data set

data      first      last
a      tom      blogg
b      tim      blogg
c      jack      blogg
d      joe      blogg
e      tom      blogg
f      tim      jones
g      jack      jones

Tom blogg is a duplicate, all others are unique. Click a cell in the data table, click "Remove Duplicates" in the Data ribbon, untick column A in the dialog and click OK. The result is

data      first      last
a      tom      blogg
b      tim      blogg
c      jack      blogg
d      joe      blogg
f      tim      jones
g      jack      jones

Five clicks, start to finish.

Try it out in the attached file.

cheers, teylyn
Book2.xlsx

Aaron

ASKER

This solution allowed me to filter the duplicates by assigning the "X" value; at that point I was able to perform the csv export I needed without losing the data for the duplicates.