Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Translate from Numbers to Descriptions | Excel Discussion (Misc queries) | |||
Highlight duplicate entries matching 2 criteria in another workshe | Excel Worksheet Functions | |||
IF and OR functions to obtain text descriptions | Excel Worksheet Functions | |||
how can I delete both parts of a duplicate entry in excel | Excel Discussion (Misc queries) | |||
Matching parts to model numbers | Excel Discussion (Misc queries) |