0

Right now, I'm trying to filter the Excel spreadsheet such that the columns would remove every instance of duplicate data.

This normally would not be an issue except the Excel spreadsheet I'm dealing with has 400 columns with 2000 rows each and going one by one clicking on Remove Duplicates is incredibly tedious.

I tried to highlight all of the data and click on Remove Duplicates, but it did not work as expected. What I expected was that every column would now only show unique rows for that particular column. Instead, it would either return a message telling me that no duplicate rows were found or would filter the unique rows across all columns. Like it only did:

SELECT DISTINCT *
FROM table

Is there a way to filter across all columns and only have it filter out all duplicate rows for that particular column? Something like this:

SELECT DISTINCT col1
FROM table

SELECT DISTINCT col2
FROM table

...

SELECT DISTINCT coln
FROM table

Didn't really think this was necessary, but here it is:

BEFORE

+-------+---------------+--------------------+
| Name  |     City      |      Position      |
+-------+---------------+--------------------+
| John  | Philadelphia  | Software Developer |
| John  | New York City | Software Engineer  |
| John  | Washington DC | Accountant         |
| Henry | Philadelphia  | Janitor            |
| Jeff  | Philadelphia  | Project Manager    |
| Paul  | Philadelphia  | Software Engineer  |
| Dave  | Boston        | Accountant         |
+-------+---------------+--------------------+

AFTER

+-------+---------------+--------------------+
| Name  |     City      |      Position      |
+-------+---------------+--------------------+
| John  | Philadelphia  | Software Developer |
| Henry | New York City | Software Engineer  |
| Jeff  | Washington DC | Accountant         |
| Paul  | Boston        | Janitor            |
| Dave  |               | Project Manager    |
+-------+---------------+--------------------+
  • Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. – DavidPostill Apr 4 '16 at 16:10
  • Sure, I've added an example. – Lee Apr 4 '16 at 16:32
1

With VBA:

Dim i As Integer

For i = 1 To 400
    Columns(i).EntireColumn.RemoveDuplicates Columns:=1, Header:=xlYes
Next i
  • I'm familiar with VB, but never used it for Excel. – Lee Apr 4 '16 at 17:41
  • Create a new macro by going to the Developer tab, you may have to enable it first under Options > Customize Ribbon. In the Developer tab click on Macros. Enter a name and click Create and paste in the code above between the Sub and End Sub lines. Then hit F5 to run it or you can access it from the Macros menu in the Devolper tab. – Atoadaso Apr 4 '16 at 17:57
  • Nice, this was exactly what I was looking for. Thanks! – Lee Apr 4 '16 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.