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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
How about this?


Excel 2016 (Windows) 32 bit
ABC
1PL01AB 
2BE01ABC
3BE01ABCD
4BE01ABCDEx
5FR05ABCDEx
6BE01ABCDEFx
7GB30ABCDEFx
8GB30ABCDEFG
9PL01ABCDEFGH
10GB30ABCDEFGHIx
11PL01ABCDEFGHIx
Sheet1
Cell Formulas
RangeFormula
C1=IF(COUNTIF($B$1:$B$11,B1)>1,"x","")
 
Upvote 0
AliGW said:
How about this?

Excel 2016 (Windows) 32 bit
ABC
PL01AB
BE01ABC
BE01ABCD
BE01ABCDEx
FR05ABCDEx
BE01ABCDEFx
GB30ABCDEFx
GB30ABCDEFG
PL01ABCDEFGH
GB30ABCDEFGHIx
PL01ABCDEFGHIx

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=IF(COUNTIF($B$1:$B$11,B1)>1,"x","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


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!!
 
Upvote 0
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.
 
Upvote 0
AliGW said:
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.
Click to expand...


Here is a more accurate data set:


Key[TABLE="width: 376"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Concatenate[/TD]
[TD]DUPLICATE[/TD]
[/TR]
[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]


Thank you!!!!
 
Upvote 0
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
ABCD
BE01ILD-S1933-UTZ-0002341223412
BE01ISW-K6138-SPT-0001902019020
BE01YPW-F120001-05-00011871187
BE01YYD-A16NH-UTZ-000132248367
FR05CHD-Q15NOP-UTZ-0001523431162
BE01BAK-D1NV-0002341223412
BE01CHD-Q70SDO-0005611756117xx
FR05CHD-Q70SDO-0005611756117xx
BE01CHD-T6436-UTZ-00089261313
BE01CHD-U2431-UTZ-0001024157
BE01CHD-U4830NVUTZ-0002937629376
BE01CHD-U5431BO2FT-000102446755
BE01CHD-V9124-000102446755
BE01CHM-U23S-00030903090xx
GB30CHM-U23S-00030903090xx
GB30CHM-V8071-000375766100
GB30CHM-W8069-000375766100
GB30CVW-U16USP-NES-00030903090xx
PL01CVW-U16USP-NES-00030903090xx
GB30CVW-U16USP-NES-000390016545xx
PL01CVW-U16USP-NES-000390016545xx
GB30FAIR-M4GB-0004701918221
GB30FAIR-M4GB-0004701939911

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=IF(COUNTIF($B$1:$B$23,B1)>1,"x","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
AliGW said:
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
ABCD
BE01ILD-S1933-UTZ-0002341223412
BE01ISW-K6138-SPT-0001902019020
BE01YPW-F120001-05-00011871187
BE01YYD-A16NH-UTZ-000132248367
FR05CHD-Q15NOP-UTZ-0001523431162
BE01BAK-D1NV-0002341223412
BE01CHD-Q70SDO-0005611756117xx
FR05CHD-Q70SDO-0005611756117xx
BE01CHD-T6436-UTZ-00089261313
BE01CHD-U2431-UTZ-0001024157
BE01CHD-U4830NVUTZ-0002937629376
BE01CHD-U5431BO2FT-000102446755
BE01CHD-V9124-000102446755
BE01CHM-U23S-00030903090xx
GB30CHM-U23S-00030903090xx
GB30CHM-V8071-000375766100
GB30CHM-W8069-000375766100
GB30CVW-U16USP-NES-00030903090xx
PL01CVW-U16USP-NES-00030903090xx
GB30CVW-U16USP-NES-000390016545xx
PL01CVW-U16USP-NES-000390016545xx
GB30FAIR-M4GB-0004701918221
GB30FAIR-M4GB-0004701939911

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=IF(COUNTIF($B$1:$B$23,B1)>1,"x","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


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?
 
Upvote 0
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.
 
Upvote 0
vanbooth said:
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!!
Click to expand...

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.
 
Last edited:
Upvote 0
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
 
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,214
Messages
6,170,774
Members
452,353
Latest member
strainu

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