Remove duplicate rows in excel when not all the columns are the same

How can I automatically remove duplicate rows in Excel when data->filter doesn't work, because the columns aren't all the same?

I believe the picture explains what I need, but I can probably supply a test file as well if necessary.

Help

Zach

Posted 2011-12-28T18:48:03.860

Reputation: 978

Answers

-1

Here is your answer, it work for me

Delete duplicate rows from a list in Excel 2010

LowvaPrg

Posted 2011-12-28T18:48:03.860

Reputation: 357

Please copy and paste your answer, don't just give a link since sometimes links die, as is the case now with your link. – NickNo – 2014-12-05T23:30:58.353

Doesn't work with the example data in my screenshot. – Zach – 2011-12-28T19:09:32.140

Did you Select Column A by clicking on the A and checking off Unique records only at the Advanced Filter – LowvaPrg – 2011-12-28T19:15:15.533

Yes. Please try it with the data in my screenshot and let me know if it works for you. – Zach – 2011-12-28T19:19:03.607

i tried it on Excel 2003 and it didn't do the firs ROW but it did the rest – LowvaPrg – 2011-12-28T19:21:12.013

Ah I think the problem is that I didn't have column headings. Let me try a few times on my real data and I'll mark it closed if it works. Thanks. – Zach – 2011-12-28T19:25:39.010

Part of the issue is that in my real data, when I do advanced filter, it was automatically expanding the selection to include the other columns. If I change the selection to only include $A$1 - $a$1700 it seems to work. Not sure why its expanding the selection on my real data, it was fine in the test file I was working on. – Zach – 2011-12-28T19:33:09.620

Is there a way to filter based on column A only, but still select everything in the filtered data, so that I can replace the original data with the filtered data? – Zach – 2011-12-28T19:51:25.503

2@LowvaPrg Link-only answers are a no-no due to possible future link-rot. Please include pertinent information in your answer. – Ƭᴇcʜιᴇ007 – 2011-12-28T20:55:45.570

@techie007 thnx for the information, i just wanted to give credit to where it is deserve. Is it ok if i can include both? – LowvaPrg – 2011-12-28T22:36:42.320

Yes, please include both a (paraphrased) version of the pertinent information, and any source(s) you obtained them from. :) – Ƭᴇcʜιᴇ007 – 2011-12-28T23:29:19.053

I am currently using excel 2010 but This functionality should still be the same.

So basically you want to remove any rows if there is a duplicate in that row's column A correct? If this is the case, highlight everything and go to

Data > Remove Duplicates

You can then select which columns you want to be included in the duplicate search. In your case you only want to select column A. This should solve your problem.

Let me know if this is actually what you need or if imisunderstood anything.

Jay

Posted 2011-12-28T18:48:03.860

Reputation: 265

I'm in 2003 so I don't have data>remove duplicates. I can get it to filter out duplicates using data>filter>advanced filter, unique records only, but I'm not sure how to copy the results of the filteriing. If you filter on all your data, you can just hit copy since the filtering leaves it selected, but if I only filter on one column, i need to select the other columns, copy, then delete everything and paste back in the filtered data. – Zach – 2011-12-28T19:48:31.063