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]

How about this?

Excel 2016 (Windows) 32 bit
	A	B	C
1	PL01	AB
2	BE01	ABC
3	BE01	ABCD
4	BE01	ABCDE	x
5	FR05	ABCDE	x
6	BE01	ABCDEF	x
7	GB30	ABCDEF	x
8	GB30	ABCDEFG
9	PL01	ABCDEFGH
10	GB30	ABCDEFGHI	x
11	PL01	ABCDEFGHI	x
Sheet1

Cell Formulas
Range	Formula
C1	=IF(COUNTIF($B$1:$B$11,B1)>1,"x","")

 

[vanbooth]

How about this?

Excel 2016 (Windows) 32 bit

	A	B	C
1	PL01	AB
2	BE01	ABC
3	BE01	ABCD
4	BE01	ABCDE	x
5	FR05	ABCDE	x
6	BE01	ABCDEF	x
7	GB30	ABCDEF	x
8	GB30	ABCDEFG
9	PL01	ABCDEFGH
10	GB30	ABCDEFGHI	x
11	PL01	ABCDEFGHI	x

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C1 =IF(COUNTIF($B$1:$B$11,B1)>1,"x","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Just tried it and not working

I need it to look at column B (the same value may be listed in column B more than once) and then see if in A there is more than one entry associated too it.

So for example if in column B you see ABCD and in column A it is linked to 1234 but also linked to 4321 I need it to put an x beside both in column C.

Thanks so much for the help!!

 

[AliGW]

Yes, I thought you might say something like that ... You see, the thing is, I can only provide a solution based on the data sample you provide. If that isn't adequate, then you need to provide a set that is realistic and reflects your real data set-up. Once you've done that, I'll take another look.

 

[vanbooth]

Yes, I thought you might say something like that ... You see, the thing is, I can only provide a solution based on the data sample you provide. If that isn't adequate, then you need to provide a set that is realistic and reflects your real data set-up. Once you've done that, I'll take another look.

Here is a more accurate data set:


Key

	Concatenate	DUPLICATE
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>

Thank you!!!!

 

[AliGW]

This is not a more representative set, because my formula is working on this list, too. See below - my formula has been added to column D:

Excel 2016 (Windows) 32 bit

	A	B	C	D
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	x
8	FR05	CHD-Q70SDO-0005611756117	x	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	x
15	GB30	CHM-U23S-00030903090	x	x
16	GB30	CHM-V8071-000375766100
17	GB30	CHM-W8069-000375766100
18	GB30	CVW-U16USP-NES-00030903090	x	x
19	PL01	CVW-U16USP-NES-00030903090	x	x
20	GB30	CVW-U16USP-NES-000390016545	x	x
21	PL01	CVW-U16USP-NES-000390016545	x	x
22	GB30	FAIR-M4GB-0004701918221
23	GB30	FAIR-M4GB-0004701939911

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D1 =IF(COUNTIF($B$1:$B$23,B1)>1,"x","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[vanbooth]

This is not a more representative set, because my formula is working on this list, too. See below - my formula has been added to column D:

Excel 2016 (Windows) 32 bit

	A	B	C	D
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	x
8	FR05	CHD-Q70SDO-0005611756117	x	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	x
15	GB30	CHM-U23S-00030903090	x	x
16	GB30	CHM-V8071-000375766100
17	GB30	CHM-W8069-000375766100
18	GB30	CVW-U16USP-NES-00030903090	x	x
19	PL01	CVW-U16USP-NES-00030903090	x	x
20	GB30	CVW-U16USP-NES-000390016545	x	x
21	PL01	CVW-U16USP-NES-000390016545	x	x
22	GB30	FAIR-M4GB-0004701918221
23	GB30	FAIR-M4GB-0004701939911

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D1 =IF(COUNTIF($B$1:$B$23,B1)>1,"x","") <tbody> </tbody>

<tbody>
</tbody>

Sorry that's not working either. I feel like it needs to reference A or else how will it identify that the CONCAT has two Keys from Column A associated too it?

 

[AliGW]

In what way is it 'not working'? It produces the exact same results as you have shown above! Unless you give me a set of data that will not work using the formula I have already supplied, then I can't help you.

 

[Aladin Akyurek]

Just tried it and not working

I need it to look at column B (the same value may be listed in column B more than once) and then see if in A there is more than one entry associated too it.

So for example if in column B you see ABCD and in column A it is linked to 1234 but also linked to 4321 I need it to put an x beside both in column C.

Thanks so much for the help!!

Your original post shows ABCD in column B just once and there are no 1234 and 4321 in column A which are associated with it... Such would be needed in order to constrain any solution.

 

[vanbooth]

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