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:

4 digit Key	Unique Identifier	Duplicate
PL01	AB
BE01	ABC
BE01	ABCD
BE01	ABCDE	x
FR05	ABCDE	x
BE01	ABCDEF	x
GB30	ABCDEF	x
GB30	ABCDEFG
PL01	ABCDEFGH
GB30	ABCDEFGHI	x
PL01	ABCDEFGHI	x

<tbody>
</tbody>

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:

Realigned Location	Realigned Product, Sold to and Ship to
BE01	BAK-D1NV-0002341223412
BE01	BAR-D5-UTZ-00010242897
BE01	CAL-D2-UTZ-0004679619910
BE01	CAL-M1-UTZ-0004679619910
BE01	CAL-M2-UTZ-000102446755
BE01	CHB-F138861D16-0001981919819
BE01	CHB-F160112D02-00065606560
BE01	CHB-F168260D01-00065606560
BE01	CHD-F073034B03-00021962196
BE01	CHD-F111094-02-0001811118111
BE01	CHD-F120036-02-0001220195
BE01	CHD-F120149-15-0001927519275
BE01	CHD-F130042B07-000498641089
BE01	CHD-F138365U01-000102446755
BE01	CHD-F138663B02-00065496549		x
BE01	CHD-F138949U04-00016011601	x
BE01	CHD-F140106-01-00013051305
BE01	CHD-F140106-01-0002341223412
BE01	CHD-F140296B01-00013261326

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

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