Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am wanting to automatically delete any rows of data A#:C# which have duplicate dataset combinations in columns A (alphabetical text) AND B (numeric text) so that every row has a unique combination of datasets within its cells in column A and B.

For instance, in the following example, I would want Excel to automatically delete ONLY row 4 (A4:F4):

excel example

share|improve this question
    
Could you sort it first? Would a helper column be an option? You could concatenate the two strings, sort the list and then have a formula compare one cell to the next one and give you 1 or 0. But that's a lot of question marks. :-) A macro would be a lot easier. – TheUser1024 Nov 5 '12 at 20:47
    
I've added an answer but it assumes you want a row removed if columns A and C match another rows columns A and C. In your question it is not clear, because you say A and B but your example uses A and C. – Pete Oakey Nov 5 '12 at 22:13

Easiest way would be to add a 4th column which concatenates columns A and C like this:

=$A1&$C1

Then copy the formula down.

Then select all 4 columns and click Data --> Remove Duplicates. Select only column D and there you go.

Edit - I think it is worth pointing out it will delete the latest duplicate, in your example row 6 not 4.

share|improve this answer
    
Hello Pete, Oops! Sorry to confuse: I changed thehe example but didn't adjust my first sentence. SHould have read: – Alfred Nov 6 '12 at 18:35
    
Hello Pete, Oops! Sorry to confuse: I changed thehe example but didn't adjust my first sentence. I actually meant to compare A and B among rows in the example, which was why I noted I'd want 4 to be removed (because it's cells A4 and B4 match A2 and B2). But I get what you mean. Thank you, but I realized as I went to Data that I can simply select all columns & rows, click on Data and select only columns A and C, and that takes care of my duplicate rows. Sorry for asking what must seem like an obvious question, but Pete your answer got me to what I needed! Thank TheUser1024 for your input. – Alfred Nov 6 '12 at 19:18
    
No worries - maybe accept my answer then? – Pete Oakey Nov 11 '12 at 13:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .