Flag versions that are equal

J

joshbjames

New Member
Joined
Jul 25, 2017
Messages
33
In my spreadsheet, column B contains part codes that repeat per how many versions I have. Column D contains the version.

We have acquired a ton of duplicates and I would like to flag them in column E. Below is the result I am aiming for. Basically, if any of the versions are duplicate, flag all.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Description[/TD]
[TD]PartCode[/TD]
[TD]Info[/TD]
[TD]Version[/TD]
[TD]Issue[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]abcd[/TD]
[TD]----[/TD]
[TD]123[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]abcd[/TD]
[TD]----[/TD]
[TD]123[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]abcd[/TD]
[TD]----[/TD]
[TD]123[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]efgh[/TD]
[TD]----[/TD]
[TD]456[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]efgh[/TD]
[TD]----[/TD]
[TD]789[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]ijkl[/TD]
[TD]----[/TD]
[TD]012[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]ijkl[/TD]
[TD]----[/TD]
[TD]012[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]ijkl[/TD]
[TD]----[/TD]
[TD]345[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]ijkl[/TD]
[TD]----[/TD]
[TD]678[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]mnop[/TD]
[TD]----[/TD]
[TD]901[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]qrst[/TD]
[TD]----[/TD]
[TD]234[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]qrst[/TD]
[TD]----[/TD]
[TD]234[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]qrst[/TD]
[TD]----[/TD]
[TD]234[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]qrst[/TD]
[TD]----[/TD]
[TD]234[/TD]
[TD]Ok[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
try


Excel 2010
ABCDE
1DescriptionPartCodeInfoVersionIssue
2----abcd----123Duplicate
3----abcd----123Duplicate
4----abcd----123Duplicate
5----efgh----456OK
6----efgh----789OK
7----ijkl----12Duplicate
8----ijkl----12Duplicate
9----ijkl----345OK
10----ijkl----678OK
11----mnop----901OK
12----qrst----234Duplicate
13----qrst----234Duplicate
14----qrst----234Duplicate
15----qrst----234Duplicate
Sheet4
Cell Formulas
RangeFormula
E2=IF(COUNTIFS($B$2:$B$15,B2,$D$2:$D$15,D2)>1,"Duplicate","OK")
E3=IF(COUNTIFS($B$2:$B$15,B3,$D$2:$D$15,D3)>1,"Duplicate","OK")
E4=IF(COUNTIFS($B$2:$B$15,B4,$D$2:$D$15,D4)>1,"Duplicate","OK")
E5=IF(COUNTIFS($B$2:$B$15,B5,$D$2:$D$15,D5)>1,"Duplicate","OK")
E6=IF(COUNTIFS($B$2:$B$15,B6,$D$2:$D$15,D6)>1,"Duplicate","OK")
E7=IF(COUNTIFS($B$2:$B$15,B7,$D$2:$D$15,D7)>1,"Duplicate","OK")
E8=IF(COUNTIFS($B$2:$B$15,B8,$D$2:$D$15,D8)>1,"Duplicate","OK")
E9=IF(COUNTIFS($B$2:$B$15,B9,$D$2:$D$15,D9)>1,"Duplicate","OK")
E10=IF(COUNTIFS($B$2:$B$15,B10,$D$2:$D$15,D10)>1,"Duplicate","OK")
E11=IF(COUNTIFS($B$2:$B$15,B11,$D$2:$D$15,D11)>1,"Duplicate","OK")
E12=IF(COUNTIFS($B$2:$B$15,B12,$D$2:$D$15,D12)>1,"Duplicate","OK")
E13=IF(COUNTIFS($B$2:$B$15,B13,$D$2:$D$15,D13)>1,"Duplicate","OK")
E14=IF(COUNTIFS($B$2:$B$15,B14,$D$2:$D$15,D14)>1,"Duplicate","OK")
E15=IF(COUNTIFS($B$2:$B$15,B15,$D$2:$D$15,D15)>1,"Duplicate","OK")
 
Upvote 0
Thanks, that worked great except in the case of "ijkl". I need to flag all 4, not just the ones that are duplicated. My original post also contains an error on the last 4 rows: the issue column cells should say "duplicate" and not "ok".
 
Last edited:
Upvote 0
So if any of the part codes are duplicates all are duplicates regardless of version number?

maybe
Code: 
=IF(OR(COUNTIFS($B$2:$B$15,B2,$D$2:$D$15,D2)>1,AND(B2=B1,E1="Duplicate")),"Duplicate","OK")
 
Upvote 0
Ah, I just realized a level of complexity to this that I did not include in my example. The same version number can occur on different product codes, so going off of count isn't very accurate. Here is updated data.

Note 1 - "ijkl" has 4 lines, 2 duplicated and 2 unique, but all are marked "Duplicate" because there is a duplicate associated with the part code.
Note 2 - "qrst" is all "Ok" even though the version numbers are also in "abcd", "efgh" and "ijkl"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PartCode[/TD]
[TD]Version[/TD]
[TD]Issue[/TD]
[/TR]
[TR]
[TD]abcd[/TD]
[TD]123[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]abcd[/TD]
[TD]123[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]abcd[/TD]
[TD]123[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]efgh[/TD]
[TD]456[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]efgh[/TD]
[TD]789[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]ijkl[/TD]
[TD]012[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]ijkl[/TD]
[TD]012[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]ijkl[/TD]
[TD]345[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]ijkl[/TD]
[TD]678[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]mnop[/TD]
[TD]901[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]qrst[/TD]
[TD]123[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]qrst[/TD]
[TD]456[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]qrst[/TD]
[TD]789[/TD]
[TD]Ok[/TD]
[/TR]
[TR]
[TD]qrst[/TD]
[TD]012[/TD]
[TD]Ok[/TD]
[/TR]
</tbody>[/TABLE]


So sorry for the confusion on this thread. This was a bigger mess than I realized.
 
Upvote 0
The formula posted above should work. It is checks both the part code and the version.


Excel 2010
ABCDE
1DescriptionPartCodeInfoVersionIssue
2abcd123Duplicate
3abcd123Duplicate
4abcd123Duplicate
5efgh456OK
6efgh789OK
7ijkl12Duplicate
8ijkl12Duplicate
9ijkl345Duplicate
10ijkl678Duplicate
11mnop901OK
12qrst123OK
13qrst456OK
14qrst789OK
15qrst12OK
Sheet4
Cell Formulas
RangeFormula
E2=IF(OR(COUNTIFS($B$2:$B$15,B2,$D$2:$D$15,D2)>1,AND(B2=B1,E1="Duplicate")),"Duplicate","OK")
E3=IF(OR(COUNTIFS($B$2:$B$15,B3,$D$2:$D$15,D3)>1,AND(B3=B2,E2="Duplicate")),"Duplicate","OK")
E4=IF(OR(COUNTIFS($B$2:$B$15,B4,$D$2:$D$15,D4)>1,AND(B4=B3,E3="Duplicate")),"Duplicate","OK")
E5=IF(OR(COUNTIFS($B$2:$B$15,B5,$D$2:$D$15,D5)>1,AND(B5=B4,E4="Duplicate")),"Duplicate","OK")
E6=IF(OR(COUNTIFS($B$2:$B$15,B6,$D$2:$D$15,D6)>1,AND(B6=B5,E5="Duplicate")),"Duplicate","OK")
E7=IF(OR(COUNTIFS($B$2:$B$15,B7,$D$2:$D$15,D7)>1,AND(B7=B6,E6="Duplicate")),"Duplicate","OK")
E8=IF(OR(COUNTIFS($B$2:$B$15,B8,$D$2:$D$15,D8)>1,AND(B8=B7,E7="Duplicate")),"Duplicate","OK")
E9=IF(OR(COUNTIFS($B$2:$B$15,B9,$D$2:$D$15,D9)>1,AND(B9=B8,E8="Duplicate")),"Duplicate","OK")
E10=IF(OR(COUNTIFS($B$2:$B$15,B10,$D$2:$D$15,D10)>1,AND(B10=B9,E9="Duplicate")),"Duplicate","OK")
E11=IF(OR(COUNTIFS($B$2:$B$15,B11,$D$2:$D$15,D11)>1,AND(B11=B10,E10="Duplicate")),"Duplicate","OK")
E12=IF(OR(COUNTIFS($B$2:$B$15,B12,$D$2:$D$15,D12)>1,AND(B12=B11,E11="Duplicate")),"Duplicate","OK")
E13=IF(OR(COUNTIFS($B$2:$B$15,B13,$D$2:$D$15,D13)>1,AND(B13=B12,E12="Duplicate")),"Duplicate","OK")
E14=IF(OR(COUNTIFS($B$2:$B$15,B14,$D$2:$D$15,D14)>1,AND(B14=B13,E13="Duplicate")),"Duplicate","OK")
E15=IF(OR(COUNTIFS($B$2:$B$15,B15,$D$2:$D$15,D15)>1,AND(B15=B14,E14="Duplicate")),"Duplicate","OK")
 
Upvote 0
You must log in or register to reply here.

Similar threads

LBee
  • Solved
Highlight both row if there is a match from column C in column A
2
Replies
10
Views
1K
LBee
LBee
L
  • Question Question
Create report by merging duplicates items based on low price
Replies
0
Views
374
leap out
L
M
  • Question Question
List cells in a range into one column
Replies
3
Views
527
DanteAmor
DanteAmor
M
  • Question Question
collection duplicate items for each sheet alone based on column
Replies
2
Views
564
Mussa
M
B
  • Question Question
building an excel calendar
Replies
8
Views
742
bharath235
B

Forum statistics

Threads
1,223,714
Messages
6,174,044
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top