Entire Row Duplicates

[Paula]

Hi, I have read the pages of excel duplicates and I can work out how to find
duplicate rows, however I only want to identify an entire row as a duplicate
if every
single cell from row a-q exactly matches, any ideas how I would do this? I
don't have 2007 excel so don't have the data delete duplicates option.

Many thanks for your advice.
Paula

 

[Mike H]

Hi,

Select A1:A1 then

Format|Conditional format
Formula is
=SUMPRODUCT(--($A1<>"")*($A1:$Q1=$A1))=17

Pick a colour.
OK

Use the format painter to paint this into other rows as required.

Mike

 

[Paula]

Hi Mike,

I don't fully understand this, I had a go and don't think I have done it
correctly, and also don't understand how to paste it down, I would also need
an answer I could filter on rather than to colour in as I need to get rid of
the dupes, sorry to ask but could you explain to me.

Many thanks
Paula

 

[Mike H]

Paula,

If i've understood correctly you want to identify when all cells in row A to
Q are the same and I have assumed the top row for this is row 1.

To enter a conditional format select a1 to q1 and then:-

Format
Conditional Format
from the dropdown select 'Formula Is'
Paste the formula I gave you into the box.
Click the 'Format' button
Click the 'patterns' tab and select a colour
Click OK

Now providing A1 is blank and A1 to Q1 are all the same the row will be
hughlighted.

Yo copy this into other rows:
View|Toolbars and ensure the 'Standard' toolbar is checked.
On the toolbar you'll see an icon that looks like a paintbrush. Click it

Click in (say) A2 and hold the left mouse button down and drag to Q2 to
'paint' the format into those cells.

Mike

 

[Paula]

Hi Mike,

It is duplicate rows I am trying to identify, so for example I have about
9000 rows of data but I have found a problem with the report because
sometimes the same data is repeated twice so for example

I might have across the columns

MR SMITH LONDON 500 FORD FIEST 1 CAR
MR SMITH LONDON 500 FORD FIEST 1 CAR

So what I am trying to do is if a row is 100% identical to another in any
part of the report I want to be able to highlight it as a duplicate in a way
which I can then filter on.

Hope this helps explain what I am trying to do, thx for your support.

Rgds Paula


A

 

[F.G.]

Hi Mike,

It is duplicate rows I am trying to identify, so for example I have about
9000 rows of data but I have found a problem with the report because
sometimes the same data is repeated twice so for example

I might have across the columns

MR SMITH   LONDON    500   FORD FIEST   1 CAR
MR SMITH   LONDON    500   FORD FIEST 1 CAR

So what I am trying to do is if a row is 100% identical to another in any
part of the report I want to be able to highlight it as a duplicate in a way
which I can then filter on.

Hope this helps explain what I am trying to do, thx for your support.

Rgds Paula

A











- Show quoted text -

Paula,

Another way to do it a little bit trickier.
Insert a column in the beginning of your sheet as identifier and in
there you enter a formula to represent few characters of the most
relevant rows, or all of them.
For example: =left(b2,5)&right(c2,5) and so on in order you want them
and the number of characters you want.
Then copy your formula on entire column.
Once you’ve don this then enter the countif function at the end of the
sheet to see the repetition: =countif(a2:a9000,a2)
Copy the formula until the end, and then you can autofilter and see
the repetitive rows.

Question to Mike:
What does the 17 represent in your previous formula
=SUMPRODUCT(--($A1<>"")*($A1:$Q1=$A1))=17

Regards,
FG

 

[John C]

17 columns from A to Q
--
John C

Paula,

Another way to do it a little bit trickier.
Insert a column in the beginning of your sheet as identifier and in
there you enter a formula to represent few characters of the most
relevant rows, or all of them.
For example: =left(b2,5)&right(c2,5) and so on in order you want them
and the number of characters you want.
Then copy your formula on entire column.
Once you’ve don this then enter the countif function at the end of the
sheet to see the repetition: =countif(a2:a9000,a2)
Copy the formula until the end, and then you can autofilter and see
the repetitive rows.

Question to Mike:
What does the 17 represent in your previous formula
=SUMPRODUCT(--($A1<>"")*($A1:$Q1=$A1))=17

Regards,
FG