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

"Mike H" wrote:

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" wrote:

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" wrote:

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" wrote:

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