Solved

Remove duplicates in Excel

Posted on 2011-09-12
8
260 Views
Last Modified: 2012-05-12
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.
0
Comment
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 10

Accepted Solution

by:
SANTABABY earned 500 total points
ID: 36524511
Choose any column (D or later) where you want to indicate whether the row is duplicate.
Say we choose D. In cell D1, enter the formula : =IF(COUNTIFS(B:B,$B1,C:C,$C1)>1,"X","")

Select the entire column D and paste.

Rows which have duplicated will be marked with 'X' in the corresponding column D cells.
0
 
LVL 8

Author Comment

by:Acosta Technology Services
ID: 36524690
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.
0
 
LVL 9

Expert Comment

by:experts1
ID: 36524705
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
0
 
LVL 8

Author Comment

by:Acosta Technology Services
ID: 36524736
Is there an easy way to copy the formula to all rows?  I think I'm missing something really easy here...
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 9

Expert Comment

by:experts1
ID: 36524863
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"

0
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36524868
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.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36527031
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
0
 
LVL 8

Author Closing Comment

by:Acosta Technology Services
ID: 36530793
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.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now