compare rows of data in excel 1

[peppermint]

Hi:
I am trying to compare rows of data in excel and want to know if this is possible.

For example, let's say I have the following data below. I want to run a "search" and see if there any repeats. So A1,B1 and A5,B5 are "doubles" - I'd want to know that and delete one row, etc.

A B
1 red blue
2 orange blue
3 red red
4 purple yellow
5 red blue

Also, if this is possible for a small amount of data like above, would I be able to run this search on let's say 10,000 rows of data comparing information in 10 rows (let's say all 10 rows have to "match" perfectly?)

I was trying to use something like the EXACT formula, but I could only get it to compare two cells for one column at a time. =EXACT(A1,A2) - which doesn't really help. Any information is appreciated.

 

[mscallisto]

In c1 put the calculation =COUNTIF(A:B,INDEX(A1,1,1)) and
drag it to the last row.


It will show you how many of each you have.

you can also sort on this if it makes locating easier

 

[bluedragon2]

In C1, put =A1&"."&B1
then copy down and sort col C

Now in D1 put =C1
in D2 put =IF(C2=C1,"",C2)
and copy down

Copy/Paste Special Values of Col D

Sort Col D and then do text to columns with . being the delimeter and you have a uniqe list.




Blue

If I wasn't Blue, I would just be a Dragon...

 

[peppermint]

Thanks for the responses. Although counting the number of duplicate cells is helpful, I think I might be looking for something like this:

http://www.add-ins.com/duplicate_finder.htm

but i don't want to download/buy another program. I was hoping that excel had something itself that could find duplicates.

 

[mscallisto]

Sorry I didn't read the whole request
My post is a quick way to see all dups or trips etc.
you would then have to delete the dups manually.


This may be easier:
Highlight the cells you wish to deal with then
Data > Filter
Advanced
Pick filter in place
Check unique records only
Ok

 

[mscallisto]

Hmmm
Yes you can accomplish what that software does but it's a bit more complicated than your first request.

is that exactly what you want or are you content to simply remove the dups?

 

[alexanderd]

have a look at chip pearsons site

http://www.cpearson.com/

and look for his programme on lists!! very good and could help you

 

[KenWright]

Just select the entire range of data, do Data / Filter / Advanced Filter / then in the 'criteria' option select your range of headers, then check the 'Unique records only' option and hit OK.

This will *filter* them in place so not actually delete them, but if you would rather put a subset of just unique records in another place, just choose that option and select a start point for it to paste the data to. You can then delete your original data and put the new list in its place.

Just for the record though, you could also have gotten there by creating an ectra column at the end that used a formula such as:-

=A3&B3&C3&D3&E3&F3&G3&H3

to concatenate the data, and then used the COUNTIF option listed in the previous replies to see how many of that column were duplicates. Filter on evereything above 1 and then just delete all visible rows, using Edit / go To / Special / Visible cells only, then Edit / Delete / Delete Entire roweg, assuming your concatenated data was in Col I, then in Col J in J1 put the following and copy down.

=IF(COUNTIF(I:I,I1)>1,"Delete me","&quot

Then just autofilter on Col J and choose the "Delete Me" value to filter on and then follow the steps above.

Regards
Ken...............

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[DrBowes]

Run this to delete all cells with the same in columns A and B, the value of "str" can be altered to include extra columns if required.

Sub DeleteDuplicates()
Dim cln As New Collection
Dim str As String
Dim i As Integer
Dim last As Double
On Error GoTo Remove
last = Range("a65536&quot.End(xlUp).Row
For i = last To 1 Step -1
str = Cells(i, 1).Value & Cells(i, 2).Value
cln.Add str, CStr(str)
Next i
Exit Sub
Remove:
Rows(i).Delete
Resume Next
End Sub

Regards