Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 20
Default duplicate parts with non matching descriptions

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default duplicate parts with non matching descriptions

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


"bduncan" wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 209
Default duplicate parts with non matching descriptions

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

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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 20
Default duplicate parts with non matching descriptions

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

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

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.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 209
Default duplicate parts with non matching descriptions

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


"bduncan" wrote:

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

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

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Translate from Numbers to Descriptions Joey Excel Discussion (Misc queries) 3 April 9th 08 08:33 PM
Highlight duplicate entries matching 2 criteria in another workshe RS Excel Worksheet Functions 12 March 15th 07 03:17 PM
IF and OR functions to obtain text descriptions Terranoman Excel Worksheet Functions 1 November 10th 06 10:07 AM
how can I delete both parts of a duplicate entry in excel Chrisinct Excel Discussion (Misc queries) 1 August 15th 06 09:34 PM
Matching parts to model numbers Craig wotdoo Excel Discussion (Misc queries) 4 May 12th 05 05:56 AM


All times are GMT +1. The time now is 11:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"