looking for duplicate rows
 

Hi
I am hoping someone can help me. I am about to fill a spreadsheet with data
and i have a problem. There will be about 8 columns but what i need to do is
highlight where there are multiple pairs of data. Not if say column 5 has
multiple names but where column 5 has a name and column 8 has a name. I need
to highlight any rows there the same two names occur in these cells. I would
like this to search automatically as there will be thousands of names. I need
to be able to do it comparing columns 5-6, 5-7, 5-8, 6-7, 6-8 and 7-8 but I
assume I just copy the same formula accross. Can anyone help me please. Thanks

looking for duplicate rows
 

In the 9th column put a formula like

=SUM(COUNTIF($A:$H,$A1:$H1))8
entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.
This is for I1
No select I1 and drag fill down the column

Rows that have values that are found anywhere in another row will show true.
This assumes every cell contains names of interest.

--
Regards,
Tom Ogilvy





"Seb Warmoth" wrote:

Hi
I am hoping someone can help me. I am about to fill a spreadsheet with data
and i have a problem. There will be about 8 columns but what i need to do is
highlight where there are multiple pairs of data. Not if say column 5 has
multiple names but where column 5 has a name and column 8 has a name. I need
to highlight any rows there the same two names occur in these cells. I would
like this to search automatically as there will be thousands of names. I need
to be able to do it comparing columns 5-6, 5-7, 5-8, 6-7, 6-8 and 7-8 but I
assume I just copy the same formula accross. Can anyone help me please. Thanks

looking for duplicate rows
 

This is excellent but I am only looking to do pairs at a time, such as column
c/e or e/f, not the whole lot at once, any ideas? Thanks

"Tom Ogilvy" wrote:

In the 9th column put a formula like

=SUM(COUNTIF($A:$H,$A1:$H1))8
entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.
This is for I1
No select I1 and drag fill down the column

Rows that have values that are found anywhere in another row will show true.
This assumes every cell contains names of interest.

--
Regards,
Tom Ogilvy





"Seb Warmoth" wrote:

Hi
I am hoping someone can help me. I am about to fill a spreadsheet with data
and i have a problem. There will be about 8 columns but what i need to do is
highlight where there are multiple pairs of data. Not if say column 5 has
multiple names but where column 5 has a name and column 8 has a name. I need
to highlight any rows there the same two names occur in these cells. I would
like this to search automatically as there will be thousands of names. I need
to be able to do it comparing columns 5-6, 5-7, 5-8, 6-7, 6-8 and 7-8 but I
assume I just copy the same formula accross. Can anyone help me please. Thanks

looking for duplicate rows
 

something along the lines of
in: I1
=(countif($c:$c,$e1)+countif($e:$e,$c1))2

then drag fill downt he column.


--
Regards,
Tom Ogilvy


"Seb Warmoth" wrote:

This is excellent but I am only looking to do pairs at a time, such as column
c/e or e/f, not the whole lot at once, any ideas? Thanks

"Tom Ogilvy" wrote:

In the 9th column put a formula like

=SUM(COUNTIF($A:$H,$A1:$H1))8
entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.
This is for I1
No select I1 and drag fill down the column

Rows that have values that are found anywhere in another row will show true.
This assumes every cell contains names of interest.

--
Regards,
Tom Ogilvy





"Seb Warmoth" wrote:

Hi
I am hoping someone can help me. I am about to fill a spreadsheet with data
and i have a problem. There will be about 8 columns but what i need to do is
highlight where there are multiple pairs of data. Not if say column 5 has
multiple names but where column 5 has a name and column 8 has a name. I need
to highlight any rows there the same two names occur in these cells. I would
like this to search automatically as there will be thousands of names. I need
to be able to do it comparing columns 5-6, 5-7, 5-8, 6-7, 6-8 and 7-8 but I
assume I just copy the same formula accross. Can anyone help me please. Thanks

looking for duplicate rows
 

This is then searching for whatever is in E1 in column C. I want, if C2 and
E2 repeat further down in say C12 and E12, it would highlight this somehow.
Thanks

"Tom Ogilvy" wrote:

something along the lines of
in: I1
=(countif($c:$c,$e1)+countif($e:$e,$c1))2

then drag fill downt he column.


--
Regards,
Tom Ogilvy


"Seb Warmoth" wrote:

This is excellent but I am only looking to do pairs at a time, such as column
c/e or e/f, not the whole lot at once, any ideas? Thanks

"Tom Ogilvy" wrote:

In the 9th column put a formula like

=SUM(COUNTIF($A:$H,$A1:$H1))8
entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.
This is for I1
No select I1 and drag fill down the column

Rows that have values that are found anywhere in another row will show true.
This assumes every cell contains names of interest.

--
Regards,
Tom Ogilvy





"Seb Warmoth" wrote:

Hi
I am hoping someone can help me. I am about to fill a spreadsheet with data
and i have a problem. There will be about 8 columns but what i need to do is
highlight where there are multiple pairs of data. Not if say column 5 has
multiple names but where column 5 has a name and column 8 has a name. I need
to highlight any rows there the same two names occur in these cells. I would
like this to search automatically as there will be thousands of names. I need
to be able to do it comparing columns 5-6, 5-7, 5-8, 6-7, 6-8 and 7-8 but I
assume I just copy the same formula accross. Can anyone help me please. Thanks

looking for duplicate rows
 

Would the duplication of E2 have to be in column E or could (for example)

C9 = E2 and E9 = C2?

would any matches/duplicates be marked in both locations (rows. 2 and 9 in
the example) or would the first occurance be unmarked.

--
Regards,
Tom Ogilvy



"Seb Warmoth" wrote:

This is then searching for whatever is in E1 in column C. I want, if C2 and
E2 repeat further down in say C12 and E12, it would highlight this somehow.
Thanks

"Tom Ogilvy" wrote:

something along the lines of
in: I1
=(countif($c:$c,$e1)+countif($e:$e,$c1))2

then drag fill downt he column.


--
Regards,
Tom Ogilvy


"Seb Warmoth" wrote:

This is excellent but I am only looking to do pairs at a time, such as column
c/e or e/f, not the whole lot at once, any ideas? Thanks

"Tom Ogilvy" wrote:

In the 9th column put a formula like

=SUM(COUNTIF($A:$H,$A1:$H1))8
entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.
This is for I1
No select I1 and drag fill down the column

Rows that have values that are found anywhere in another row will show true.
This assumes every cell contains names of interest.

--
Regards,
Tom Ogilvy





"Seb Warmoth" wrote:

Hi
I am hoping someone can help me. I am about to fill a spreadsheet with data
and i have a problem. There will be about 8 columns but what i need to do is
highlight where there are multiple pairs of data. Not if say column 5 has
multiple names but where column 5 has a name and column 8 has a name. I need
to highlight any rows there the same two names occur in these cells. I would
like this to search automatically as there will be thousands of names. I need
to be able to do it comparing columns 5-6, 5-7, 5-8, 6-7, 6-8 and 7-8 but I
assume I just copy the same formula accross. Can anyone help me please. Thanks

looking for duplicate rows
 

No figure in column C will ever appear in column E and visa versa. It is just
a case of looking for where any name in C and name in E appears more than
once. Duplicates of any name in C does not matter. It only matters if it has
the same name in E in both rows. Thanks

"Tom Ogilvy" wrote:

Would the duplication of E2 have to be in column E or could (for example)

C9 = E2 and E9 = C2?

would any matches/duplicates be marked in both locations (rows. 2 and 9 in
the example) or would the first occurance be unmarked.

--
Regards,
Tom Ogilvy



"Seb Warmoth" wrote:

This is then searching for whatever is in E1 in column C. I want, if C2 and
E2 repeat further down in say C12 and E12, it would highlight this somehow.
Thanks

"Tom Ogilvy" wrote:

something along the lines of
in: I1
=(countif($c:$c,$e1)+countif($e:$e,$c1))2

then drag fill downt he column.


--
Regards,
Tom Ogilvy


"Seb Warmoth" wrote:

This is excellent but I am only looking to do pairs at a time, such as column
c/e or e/f, not the whole lot at once, any ideas? Thanks

"Tom Ogilvy" wrote:

In the 9th column put a formula like

=SUM(COUNTIF($A:$H,$A1:$H1))8
entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.
This is for I1
No select I1 and drag fill down the column

Rows that have values that are found anywhere in another row will show true.
This assumes every cell contains names of interest.

--
Regards,
Tom Ogilvy





"Seb Warmoth" wrote:

Hi
I am hoping someone can help me. I am about to fill a spreadsheet with data
and i have a problem. There will be about 8 columns but what i need to do is
highlight where there are multiple pairs of data. Not if say column 5 has
multiple names but where column 5 has a name and column 8 has a name. I need
to highlight any rows there the same two names occur in these cells. I would
like this to search automatically as there will be thousands of names. I need
to be able to do it comparing columns 5-6, 5-7, 5-8, 6-7, 6-8 and 7-8 but I
assume I just copy the same formula accross. Can anyone help me please. Thanks

looking for duplicate rows
 

sub check_for_dups()
Dim lastrow as Long, i as Long, j as Long
Columns(3).Interior.ColorIndex = xlNone
columns(5).Interior.ColorIndex = xlNone
lastrow = cells(rows.count,"C").end(xlup).row
for i = 2 to lastrow - 1
for j = i + 1 to lastrow
if cells(i,"C") = cells(j,"C") then
if cells(i,"E") = cells(j,"E") then
cells(i,"C").Interior.ColorIndex = 3
cells(j,"C").Interior.ColorIndex = 3
cells(i,"E").Interior.ColorIndex = 3
cells(j,"E").Interior.ColorIndex = 3
end if
end if
Next j
Next i
End sub

--
Regards,
Tom Ogilvy

"Seb Warmoth" wrote:

No figure in column C will ever appear in column E and visa versa. It is just
a case of looking for where any name in C and name in E appears more than
once. Duplicates of any name in C does not matter. It only matters if it has
the same name in E in both rows. Thanks

"Tom Ogilvy" wrote:

Would the duplication of E2 have to be in column E or could (for example)

C9 = E2 and E9 = C2?

would any matches/duplicates be marked in both locations (rows. 2 and 9 in
the example) or would the first occurance be unmarked.

--
Regards,
Tom Ogilvy



"Seb Warmoth" wrote:

This is then searching for whatever is in E1 in column C. I want, if C2 and
E2 repeat further down in say C12 and E12, it would highlight this somehow.
Thanks

"Tom Ogilvy" wrote:

something along the lines of
in: I1
=(countif($c:$c,$e1)+countif($e:$e,$c1))2

then drag fill downt he column.


--
Regards,
Tom Ogilvy


"Seb Warmoth" wrote:

This is excellent but I am only looking to do pairs at a time, such as column
c/e or e/f, not the whole lot at once, any ideas? Thanks

"Tom Ogilvy" wrote:

In the 9th column put a formula like

=SUM(COUNTIF($A:$H,$A1:$H1))8
entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.
This is for I1
No select I1 and drag fill down the column

Rows that have values that are found anywhere in another row will show true.
This assumes every cell contains names of interest.

--
Regards,
Tom Ogilvy





"Seb Warmoth" wrote:

Hi
I am hoping someone can help me. I am about to fill a spreadsheet with data
and i have a problem. There will be about 8 columns but what i need to do is
highlight where there are multiple pairs of data. Not if say column 5 has
multiple names but where column 5 has a name and column 8 has a name. I need
to highlight any rows there the same two names occur in these cells. I would
like this to search automatically as there will be thousands of names. I need
to be able to do it comparing columns 5-6, 5-7, 5-8, 6-7, 6-8 and 7-8 but I
assume I just copy the same formula accross. Can anyone help me please. Thanks

looking for duplicate rows
 

Thank you Tom. I have put this in as a module. I am not sure how I use it
though. Do I need to write =check_for_dups(???????) and in what cell do I put
this in? Thanks for all your help.

"Tom Ogilvy" wrote:

sub check_for_dups()
Dim lastrow as Long, i as Long, j as Long
Columns(3).Interior.ColorIndex = xlNone
columns(5).Interior.ColorIndex = xlNone
lastrow = cells(rows.count,"C").end(xlup).row
for i = 2 to lastrow - 1
for j = i + 1 to lastrow
if cells(i,"C") = cells(j,"C") then
if cells(i,"E") = cells(j,"E") then
cells(i,"C").Interior.ColorIndex = 3
cells(j,"C").Interior.ColorIndex = 3
cells(i,"E").Interior.ColorIndex = 3
cells(j,"E").Interior.ColorIndex = 3
end if
end if
Next j
Next i
End sub

--
Regards,
Tom Ogilvy

"Seb Warmoth" wrote:

No figure in column C will ever appear in column E and visa versa. It is just
a case of looking for where any name in C and name in E appears more than
once. Duplicates of any name in C does not matter. It only matters if it has
the same name in E in both rows. Thanks

"Tom Ogilvy" wrote:

Would the duplication of E2 have to be in column E or could (for example)

C9 = E2 and E9 = C2?

would any matches/duplicates be marked in both locations (rows. 2 and 9 in
the example) or would the first occurance be unmarked.

--
Regards,
Tom Ogilvy



"Seb Warmoth" wrote:

This is then searching for whatever is in E1 in column C. I want, if C2 and
E2 repeat further down in say C12 and E12, it would highlight this somehow.
Thanks

"Tom Ogilvy" wrote:

something along the lines of
in: I1
=(countif($c:$c,$e1)+countif($e:$e,$c1))2

then drag fill downt he column.


--
Regards,
Tom Ogilvy


"Seb Warmoth" wrote:

This is excellent but I am only looking to do pairs at a time, such as column
c/e or e/f, not the whole lot at once, any ideas? Thanks

"Tom Ogilvy" wrote:

In the 9th column put a formula like

=SUM(COUNTIF($A:$H,$A1:$H1))8
entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.
This is for I1
No select I1 and drag fill down the column

Rows that have values that are found anywhere in another row will show true.
This assumes every cell contains names of interest.

--
Regards,
Tom Ogilvy





"Seb Warmoth" wrote:

Hi
I am hoping someone can help me. I am about to fill a spreadsheet with data
and i have a problem. There will be about 8 columns but what i need to do is
highlight where there are multiple pairs of data. Not if say column 5 has
multiple names but where column 5 has a name and column 8 has a name. I need
to highlight any rows there the same two names occur in these cells. I would
like this to search automatically as there will be thousands of names. I need
to be able to do it comparing columns 5-6, 5-7, 5-8, 6-7, 6-8 and 7-8 but I
assume I just copy the same formula accross. Can anyone help me please. Thanks

looking for duplicate rows
 

Tools=Macro=Macros, select Check_for_dups and click Run.

--
Regards,
Tom Ogilvy

"Seb Warmoth" wrote in message
...
Thank you Tom. I have put this in as a module. I am not sure how I use it
though. Do I need to write =check_for_dups(???????) and in what cell do I
put
this in? Thanks for all your help.

"Tom Ogilvy" wrote:

sub check_for_dups()
Dim lastrow as Long, i as Long, j as Long
Columns(3).Interior.ColorIndex = xlNone
columns(5).Interior.ColorIndex = xlNone
lastrow = cells(rows.count,"C").end(xlup).row
for i = 2 to lastrow - 1
for j = i + 1 to lastrow
if cells(i,"C") = cells(j,"C") then
if cells(i,"E") = cells(j,"E") then
cells(i,"C").Interior.ColorIndex = 3
cells(j,"C").Interior.ColorIndex = 3
cells(i,"E").Interior.ColorIndex = 3
cells(j,"E").Interior.ColorIndex = 3
end if
end if
Next j
Next i
End sub

--
Regards,
Tom Ogilvy

"Seb Warmoth" wrote:

No figure in column C will ever appear in column E and visa versa. It
is just
a case of looking for where any name in C and name in E appears more
than
once. Duplicates of any name in C does not matter. It only matters if
it has
the same name in E in both rows. Thanks

"Tom Ogilvy" wrote:

Would the duplication of E2 have to be in column E or could (for
example)

C9 = E2 and E9 = C2?

would any matches/duplicates be marked in both locations (rows. 2 and
9 in
the example) or would the first occurance be unmarked.

--
Regards,
Tom Ogilvy



"Seb Warmoth" wrote:

This is then searching for whatever is in E1 in column C. I want,
if C2 and
E2 repeat further down in say C12 and E12, it would highlight this
somehow.
Thanks

"Tom Ogilvy" wrote:

something along the lines of
in: I1
=(countif($c:$c,$e1)+countif($e:$e,$c1))2

then drag fill downt he column.


--
Regards,
Tom Ogilvy


"Seb Warmoth" wrote:

This is excellent but I am only looking to do pairs at a time,
such as column
c/e or e/f, not the whole lot at once, any ideas? Thanks

"Tom Ogilvy" wrote:

In the 9th column put a formula like

=SUM(COUNTIF($A:$H,$A1:$H1))8
entered with Ctrl+Shift+Enter rather than just enter since
this is an array
formula.
This is for I1
No select I1 and drag fill down the column

Rows that have values that are found anywhere in another row
will show true.
This assumes every cell contains names of interest.

--
Regards,
Tom Ogilvy





"Seb Warmoth" wrote:

Hi
I am hoping someone can help me. I am about to fill a
spreadsheet with data
and i have a problem. There will be about 8 columns but
what i need to do is
highlight where there are multiple pairs of data. Not if
say column 5 has
multiple names but where column 5 has a name and column 8
has a name. I need
to highlight any rows there the same two names occur in
these cells. I would
like this to search automatically as there will be
thousands of names. I need
to be able to do it comparing columns 5-6, 5-7, 5-8, 6-7,
6-8 and 7-8 but I
assume I just copy the same formula accross. Can anyone
help me please. Thanks