Duplicates

  • Thread starter Thread starter Jen_T
  • Start date Start date
J

Jen_T

Is there a way to check if duplicate rows have the same information in both ?
I have about 850 of data that is duplicated, giving me a total of 1,700
rows. Both sets of data have the same column headings, I need to check if
they have the same information for each duplicate. Is there an easy way to do
this besides manually ?

Example:
Column A in one record is it identical to column A in duplicate record, than
column B for the same record is it a duplicate in column B in the duplicate
record?
 
If you are using Excel 2007 then the easiest way to remove duplicates is:
- first put your data in the Table format (Insert -> Table),
- second run the Remove Duplicates command from Tools ribbon of the Table
Tool Design.

One of the method to find the duplicate in your data is also:
- insert a pivot table on your data,
- put (drag or select) all column headings consecutively to the Row Labels
box,
- in the Field settings of the row labels choose None for Subtotals
- put (drag) any one of the column heading of your data into the values area
and chose Count as the agregate function
- in the Total column of the Pivot Table you see now if the record is a
duplicate (for me the best view is a Clasic Pivot Table layout, which you
can get if you right click pivot table, pick the pivot table Option, select
Display tab and then choose classic pivot table)

Ivan
 
Check out the VBA RowDifferences Method: (pasted from help file)

RowDifferences Method
Returns a Range object that represents all the cells whose contents are
different from those of the comparison cell in each row.

expression.RowDifferences(Comparison)
expression Required. An expression that returns a range containing the
cells to be compared.

Comparison Required Variant. A single cell to compare with the specified
range.

Example
This example selects the cells in row one on Sheet1 whose contents are
different from those of cell D1.

Worksheets("Sheet1").Activate
Set c1 = ActiveSheet.Rows(1).RowDifferences( _
comparison:=ActiveSheet.Range("D1"))
c1.Select
 
I am not understanding. Will this compare the duplicatea rows and tell me if
all columns match ? The rows will have the same id to identify if they are
duplicates in colmn A.
 
My commercial Excel add-in "XL Companion" can compare rows.
Its Match Rows feature returns the common and uncommon rows between two worksheets.
(also does other useful stuff)
Email direct and ask for the Free trial version.
Remove xxx from my email address... james.coneXXX at comcast.netXXX
Please provide your real name and geographic location.
--
Jim Cone
Portland, Oregon USA



"Jen_T" <[email protected]>
wrote in message
I am not understanding. Will this compare the duplicate rows and tell me if
all columns match ? The rows will have the same id to identify if they are
duplicates in column A.

-snip-
 
Pivot Table works in Excel 2007 too. And there is probably quite smaller
problem with the number of duplicates as it is with maybe to much columns of
your table.

If you can't use Table form (of Excel 2007) and if the Pivota Table is to
weak for you, then you can use more simple method:

First you have to sort your table on all columns, where duplication is in
question. Because I seem to, that older excel can't sort on more than 3
column in one sort session, you have to sort consecutively if you have more
columns. Namely if I'm not wrong sort in excel takes into accout
thebeginning state.

Secondly insert a new column and put a formula into it. If let say that the
last column of your table is M column, then you must put in the first cell
N2 (N1 is the header row) only value of 0 and in cell N3 formula:

=IF(AND(A3=A2,B3=B2,C3=C2, ..., M3=M2),1,0)

(fill ... in upper formula with all your column!)

After that you copy the formula of cell N3 to whole column N in all rows
of your table. After that select the whole column N and first copy it to the
clipboard with Copy (Ctrl + C) command. Then click in the N1 column and from
the right mouse click menu pick the Paste Special and later the Value
command. With this Copy and Paste Special - Values procedure you will remove
the function dependence between two consecutive rows, what can be an
obstacle in later sorting.

With all this procedure you get in the N column value 0 if the row is
original (first occurence) and value 1 if it is a duplicate. If you want to
remove duplicates, then sort the wholw extended table on column N and delete
the lower rows with the value 1 in column N.

Ivan
 
Jim, thank you for the addin. I was working with it a bit and had a question.
In my worksheet I have 1700 rows, 850 are duplicates, I need the addin to
look at each of the two rows in the worksheet that are duplcaites and than
look at each column to indicate what is, if any does not match for the two
records. Do I need to look at the two rows one at a time, another words run
the addin 850 times?
 
Back
Top