Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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