RE: Entire Row Duplicates

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

.

Relevant Pages

  • Re: Deleting certain cells only
    ... CDate may not be needed. ... it looks like the Format function does indeed perform the ... That may work for you depending on whether you can have duplicate dates ... cells not deleted. ...
    (microsoft.public.excel.programming)
  • Re: Check for duplicate entries???
    ... Share the values that you entered into the two cells. ... I went to his site and tried the "Highlighting Duplicate ... This first example will highlight duplicate rows in the range B2:B11. ... the cells that you wish to test and format, ...
    (microsoft.public.excel)
  • Re: CAN EXCEL TELL ME IF I ENTER THE SAME DATA TWICE?
    ... select the range of cells that you ... This first example will highlight duplicate rows in the range B2:B11. ... open the Conditional Formatting dialog from the Format menu, ...
    (microsoft.public.excel.misc)
  • RE: Entire Row Duplicates
    ... If i've understood correctly you want to identify when all cells in row A to ... To enter a conditional format select a1 to q1 and then:- ... "Paula" wrote: ... duplicate rows, however I only want to identify an entire row as a duplicate ...
    (microsoft.public.excel.misc)
  • Re: Can Access use Fuzzy Logic
    ... Then you need to get all records into the exactly the same format. ... schemes, like Soundex(), Soundex2and Simul. ... de-duping on the resulting fields. ... determination of what really is a duplicate. ...
    (comp.databases.ms-access)