duplicate parts with non matching descriptions

[bduncan]

Drawing NO. Part Name
GB/T1095-2003 Parallel key C10×8×45
GB/T1095-2003 Parallel key C10×8×45
GB/T1095-2003 Flat key C10×8×45
GB/T13871-1992 Oil seal Φ77×49.5×18
GB/T13871-1992 Oil seal Φ77×49.5×18
GB/T13871-1992 Oil seal Φ 77×49.5×18
GB/T13871-1992 Oil seal Φ 80x55×18

I have multiple lists of parts numbers that I combined and then filtered by
Drawing number. What I need is a way to highlight, if there is a drawing
number that has duplicates but the part name doesn't match between the
duplicates.

 

[Bernie Deitrick]

With your list staeing in columns A and B, with headers in row 1, enter this into cell C2 and copy
down.

=VLOOKUP(A2,A:B,2,FALSE)=B2

This will highlight subsequent instances that don't match the first instance.

You would get (filter on FALSE)

Drawing NO. Part Name
GB/T1095-2003 Parallel key C10×8×45 TRUE
GB/T1095-2003 Parallel key C10×8×45 TRUE
GB/T1095-2003 Flat key C10×8×45 FALSE
GB/T13871-1992 Oil seal ?77×49.5×18 TRUE
GB/T13871-1992 Oil seal ?77×49.5×18 TRUE
GB/T13871-1992 Oil seal ? 77×49.5×18 FALSE
GB/T13871-1992 Oil seal ? 80x55×18 FALSE

If you wanted to highlight both, you could use this array formula (enter using Ctrl-Shift-Enter)-
update the ranges to reflect your table:

=SUM(1/SUMPRODUCT((A2&B2=$A$2:$A$17&$B$2:$B$17)*1))=(1/COUNTIF($A$2:$A$17,A2))

then filter on FALSE values

HTH,
Bernie
MS Excel MVP

 

[Gary Brown]

1) SORT by 'Drawing Number' then 'Part Name'
Assuming 'Drawing Number' is Col A, Row 1
Assuming 'Part Name' is Col B, Row 1

2) In cell C2 put the following formula...
=IF(AND(A2=A1,B2<>B1),1,0)

3) Highlight Columns A, B and C
4) Select from the top menu Format>Conditional Formatting...
5) Select 'Formula is' for Condition 1
6) put =$C1=1 in the text box next to 'Formula is'
7) Hit the Format... button
8) Select Patterns tab
9) Select a color then OK
10) Hit OK
11) YOU ARE HIGHLIGHTED!
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown

 

[bduncan]

Thanks for the help.
I've got the 0,1 in the column C but the row isn't high lighting.
I'm using excel 2007 if that makes a difference.
In the "Edit Formatting Rule" box I selected "Use a formula to determine
which cells to format" and entered the formula you gave.

 

[Gary Brown]

Did you select a pattern so the background will show in a different color?

--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown