Formula to identify if for a unique identifier there is more than one value in another column

V

vanbooth

New Member
Joined
Apr 3, 2017
Messages
15
I have two columns of data, one contains a unique identifier, the other contains another 4 digit key.

What I want to do is for the unique identifier when there is more than one 4 digit key that is associated to it, I would like to to display x. Marking both entries not just the first or second. For example:

[TABLE="width: 322"]
<tbody>[TR]
[TD]4 digit Key[/TD]
[TD]Unique Identifier[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]PL01[/TD]
[TD]AB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]ABC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]ABCD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]ABCDE[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]FR05[/TD]
[TD]ABCDE[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]ABCDEF[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]GB30[/TD]
[TD]ABCDEF[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]GB30[/TD]
[TD]ABCDEFG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PL01[/TD]
[TD]ABCDEFGH[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GB30[/TD]
[TD]ABCDEFGHI[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]PL01[/TD]
[TD]ABCDEFGHI[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]


Does anyone have any suggestions for how to best tackle this?

TIA
 
Sort by date Sort by votes
vanbooth said:
When I copy and paste the formula into a new sheet to test it with the same data set it provides an 'x' for all the data.

I think this is because it does not appear to be referencing column A at all. It needs to lookup the value in column A from the value in column B to see if two values found in column A. If two values are found it needs to identify in column C beside both entries with an x. If the value in column B has only one corresponding value in column A it should be blank.

Does that help explain better?

TIA
Click to expand...

Try to post an illustrative sample...
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Thanks for the reply I provided a more accurate data set after to Ali's reply - found here:

[TABLE="width: 431"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]BE01[/TD]
[TD]ILD-S1933-UTZ-0002341223412[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]ISW-K6138-SPT-0001902019020[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]YPW-F120001-05-00011871187[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]YYD-A16NH-UTZ-000132248367[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]FR05[/TD]
[TD]CHD-Q15NOP-UTZ-0001523431162[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]BAK-D1NV-0002341223412[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-Q70SDO-0005611756117[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]FR05[/TD]
[TD]CHD-Q70SDO-0005611756117[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-T6436-UTZ-00089261313[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-U2431-UTZ-0001024157[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-U4830NVUTZ-0002937629376[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-U5431BO2FT-000102446755[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-V9124-000102446755[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHM-U23S-00030903090[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]GB30[/TD]
[TD]CHM-U23S-00030903090[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]GB30[/TD]
[TD]CHM-V8071-000375766100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GB30[/TD]
[TD]CHM-W8069-000375766100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GB30[/TD]
[TD]CVW-U16USP-NES-00030903090[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]PL01[/TD]
[TD]CVW-U16USP-NES-00030903090[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]GB30[/TD]
[TD]CVW-U16USP-NES-000390016545[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]PL01[/TD]
[TD]CVW-U16USP-NES-000390016545[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]GB30[/TD]
[TD]FAIR-M4GB-0004701918221[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GB30[/TD]
[TD]FAIR-M4GB-0004701939911[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
In C2 control+shift+enter, not just enter, and copy down:

=IF(SUM(IF(FREQUENCY(IF($B$2:$B$24=B2,MATCH($A$2:$A$24,$A$2:$A$24,0)),ROW($A$2:$A$24)-ROW($A$2)+1),1))>1,"x","")
 
Upvote 0
Aladin Akyurek said:
In C2 control+shift+enter, not just enter, and copy down:

=IF(SUM(IF(FREQUENCY(IF($B$2:$B$24=B2,MATCH($A$2:$A$24,$A$2:$A$24,0)),ROW($A$2:$A$24)-ROW($A$2)+1),1))>1,"x","")
Click to expand...


Thank you so much! Is there a way to do it for all of column A and column B, the list of data will vary each time

TIA
 
Upvote 0
vanbooth said:
Thank you so much! Is there a way to do it for all of column A and column B, the list of data will vary each time

TIA
Click to expand...

Two options:

1. Convert the data are into a table (Insert | Table)

Then in C2 control+shift+enter, not just enter, and copy down:

=IF(SUM(IF(FREQUENCY(IF([Concatenate]=B2,MATCH([Key],[Key],0)),ROW([Key])-ROW(INDEX([Key],1,1))+1),1))>1,"x","")

2. Define the following names in Formulas | Name Manager...

Key as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))

Concat as referring to:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(REPT("z",255),Sheet1!$A:$A))

Now in C2 control+shift+enter, not just enter, and copy down:

=IF(SUM(IF(FREQUENCY(IF(Concat=B2,MATCH(Key,Key,0)),ROW(Key)-ROW(INDEX(Key,1,1))+1),1))>1,"x","")
 
Upvote 0
Aladin Akyurek said:
Two options:

1. Convert the data are into a table (Insert | Table)

Then in C2 control+shift+enter, not just enter, and copy down:

=IF(SUM(IF(FREQUENCY(IF([Concatenate]=B2,MATCH([Key],[Key],0)),ROW([Key])-ROW(INDEX([Key],1,1))+1),1))>1,"x","")

2. Define the following names in Formulas | Name Manager...

Key as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))

Concat as referring to:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(REPT("z",255),Sheet1!$A:$A))

Now in C2 control+shift+enter, not just enter, and copy down:

=IF(SUM(IF(FREQUENCY(IF(Concat=B2,MATCH(Key,Key,0)),ROW(Key)-ROW(INDEX(Key,1,1))+1),1))>1,"x","")
Click to expand...



Thank you! I went with option two and defined the names - in C2 I can't seem to get the Ctrl+shift+enter to work. When I paste into the formula bar and Ctrl+shift+enter it just takes me to C3. I did notice when I click back on the formula in C2 in the formula bar it displays as {=IF(SUM(IF(FREQUENCY(IF(Concat=B2,MATCH(Key,Key,0)),ROW(Key)-ROW(INDEX(Key,1,1))+1),1))>1,"x","")} but when you click back in the formula bar the {} dissapear.

What am I doing wrong?

Thanks again!
 
Upvote 0
Maybe I'm misinterpreting here but isn't this just a condition based on a COUNTIFS?


Book1
ABC
1BE01ILD-S1933-UTZ-0002341223412 
2BE01ISW-K6138-SPT-0001902019020
3BE01YPW-F120001-05-00011871187
4BE01YYD-A16NH-UTZ-000132248367
5FR05CHD-Q15NOP-UTZ-0001523431162
6BE01BAK-D1NV-0002341223412
7BE01CHD-Q70SDO-0005611756117x
8FR05CHD-Q70SDO-0005611756117x
9BE01CHD-T6436-UTZ-00089261313
10BE01CHD-U2431-UTZ-0001024157
11BE01CHD-U4830NVUTZ-0002937629376
12BE01CHD-U5431BO2FT-000102446755
13BE01CHD-V9124-000102446755
14BE01CHM-U23S-00030903090x
15GB30CHM-U23S-00030903090x
16GB30CHM-V8071-000375766100
17GB30CHM-W8069-000375766100
18GB30CVW-U16USP-NES-00030903090x
19PL01CVW-U16USP-NES-00030903090x
20GB30CVW-U16USP-NES-000390016545x
21PL01CVW-U16USP-NES-000390016545x
22GB30FAIR-M4GB-0004701918221
23GB30FAIR-M4GB-0004701939911
Sheet1
Cell Formulas
RangeFormula
C1=IF(COUNTIFS($B$1:$B$23,$B1,$A$1:$A$23,"<>"&$A1)=0,"","x")


WBD
 
Upvote 0
Actually that sounds like a simpler way to do it - any way to expand that to be all of column A and B?

TIA!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
COUNTIF greater than 0 in one column and unique values in another
Replies
3
Views
2K
RodneyC
R
R
  • Solved
Excel VBA to Identify Duplicates from one Worksheet Based on Cell Value from Another Worksheet
Replies
3
Views
666
rarascon
R
HobbesIsReal
  • Solved
Find Duplicates But Only If They Are In More Than One Visit IDs In A Second Column
Replies
9
Views
558
Fluff
Fluff
G
  • Question Question
Export specific content from an existing large range and display unique values
Replies
6
Views
683
Marc L
Marc L
P
  • Question Question
find and replace unique values on another sheet
Replies
1
Views
2K
pann8888
P

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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