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

 

[Aladin Akyurek]

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

Try to post an illustrative sample...

 

[vanbooth]

Thanks for the reply I provided a more accurate data set after to Ali's reply - found here:

BE01	ILD-S1933-UTZ-0002341223412
BE01	ISW-K6138-SPT-0001902019020
BE01	YPW-F120001-05-00011871187
BE01	YYD-A16NH-UTZ-000132248367
FR05	CHD-Q15NOP-UTZ-0001523431162
BE01	BAK-D1NV-0002341223412
BE01	CHD-Q70SDO-0005611756117	x
FR05	CHD-Q70SDO-0005611756117	x
BE01	CHD-T6436-UTZ-00089261313
BE01	CHD-U2431-UTZ-0001024157
BE01	CHD-U4830NVUTZ-0002937629376
BE01	CHD-U5431BO2FT-000102446755
BE01	CHD-V9124-000102446755
BE01	CHM-U23S-00030903090	x
GB30	CHM-U23S-00030903090	x
GB30	CHM-V8071-000375766100
GB30	CHM-W8069-000375766100
GB30	CVW-U16USP-NES-00030903090	x
PL01	CVW-U16USP-NES-00030903090	x
GB30	CVW-U16USP-NES-000390016545	x
PL01	CVW-U16USP-NES-000390016545	x
GB30	FAIR-M4GB-0004701918221
GB30	FAIR-M4GB-0004701939911

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

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","")

 

[vanbooth]

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","")

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

 

[Aladin Akyurek]

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

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","")

 

[vanbooth]

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","")

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!

 

[AliGW]

The {} will reappear when you exit the formula bar using CTRL+SHIFT+ENTER.

 

[wideboydixon]

Maybe I'm misinterpreting here but isn't this just a condition based on a COUNTIFS?

Book1
	A	B	C
1	BE01	ILD-S1933-UTZ-0002341223412
2	BE01	ISW-K6138-SPT-0001902019020
3	BE01	YPW-F120001-05-00011871187
4	BE01	YYD-A16NH-UTZ-000132248367
5	FR05	CHD-Q15NOP-UTZ-0001523431162
6	BE01	BAK-D1NV-0002341223412
7	BE01	CHD-Q70SDO-0005611756117	x
8	FR05	CHD-Q70SDO-0005611756117	x
9	BE01	CHD-T6436-UTZ-00089261313
10	BE01	CHD-U2431-UTZ-0001024157
11	BE01	CHD-U4830NVUTZ-0002937629376
12	BE01	CHD-U5431BO2FT-000102446755
13	BE01	CHD-V9124-000102446755
14	BE01	CHM-U23S-00030903090	x
15	GB30	CHM-U23S-00030903090	x
16	GB30	CHM-V8071-000375766100
17	GB30	CHM-W8069-000375766100
18	GB30	CVW-U16USP-NES-00030903090	x
19	PL01	CVW-U16USP-NES-00030903090	x
20	GB30	CVW-U16USP-NES-000390016545	x
21	PL01	CVW-U16USP-NES-000390016545	x
22	GB30	FAIR-M4GB-0004701918221
23	GB30	FAIR-M4GB-0004701939911
Sheet1

Cell Formulas
Range	Formula
C1	=IF(COUNTIFS($B$1:$B$23,$B1,$A$1:$A$23,"<>"&$A1)=0,"","x")

WBD

 

[vanbooth]

Gotcha, thank you!

 

[vanbooth]

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

TIA!