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

[vanbooth]

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

 

[AliGW]

=IF(COUNTIFS($B:$B,$B1,$A:$A,"<>"&$A1)=0,"","x")

 

[wideboydixon]

=IF(COUNTIFS($B:$B,$B1,$A:$A$,"<>"&$A1)=0,"","x")

Yes that's right - just discard that rogue "$"

WBD

 

[AliGW]

Oops! Well-spotted.

 

[vanbooth]

That is seeming to put an x on the next row instead of the current row

For example green is where the x should be, red is where it is:

[TABLE="width: 406"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Realigned Location[/TD]
[TD]Realigned Product, Sold to and Ship to[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]BAK-D1NV-0002341223412[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]BAR-D5-UTZ-00010242897[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CAL-D2-UTZ-0004679619910[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CAL-M1-UTZ-0004679619910[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CAL-M2-UTZ-000102446755[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHB-F138861D16-0001981919819[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHB-F160112D02-00065606560[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHB-F168260D01-00065606560[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-F073034B03-00021962196[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-F111094-02-0001811118111[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-F120036-02-0001220195[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-F120149-15-0001927519275[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-F130042B07-000498641089[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-F138365U01-000102446755[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-F138663B02-00065496549[/TD]
[TD] [/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-F138949U04-00016011601[/TD]
[TD]x[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-F140106-01-00013051305[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-F140106-01-0002341223412[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BE01[/TD]
[TD]CHD-F140296B01-00013261326[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Thanks!

 

[wideboydixon]

You did put the right formula into the second row? You didn't reference row 1 there?

Book1
	A	B	C
1	Realigned Location	Realigned Product, Sold to and Ship to	X?
2	BE01	BAK-D1NV-0002341223412
3	BE01	BAR-D5-UTZ-00010242897
4	BE01	CAL-D2-UTZ-0004679619910
5	BE01	CAL-M1-UTZ-0004679619910
6	BE01	CAL-M2-UTZ-000102446755
7	BE01	CHB-F138861D16-0001981919819
8	BE01	CHB-F160112D02-00065606560
9	BE01	CHB-F168260D01-00065606560
10	BE01	CHD-F073034B03-00021962196
11	BE01	CHD-F111094-02-0001811118111
12	BE01	CHD-F120036-02-0001220195
13	BE01	CHD-F120149-15-0001927519275
14	BE01	CHD-F130042B07-000498641089
15	BE01	CHD-F138365U01-000102446755
16	BE01	CHD-F138663B02-00065496549	x
17	BE01	CHD-F138949U04-00016011601
18	BE01	CHD-F140106-01-00013051305
19	BE01	CHD-F140106-01-0002341223412
20	BE01	CHD-F140296B01-00013261326
21	BF01	CHD-F138663B02-00065496549	x
Sheet1

Cell Formulas
Range	Formula
C2	=IF(COUNTIFS($B:$B,$B2,$A:$A,"<>"&$A2)=0,"","x")

WBD

 

[vanbooth]

Perfect, thank you!!!

 

[Aladin Akyurek]

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!

See the workbook that implements the set up: https://www.dropbox.com/s/ckse5a7rf...re than one value in another column.xlsx?dl=0