Identifying duplicate values in the same cell, where the data is separated by a pipe.

[E2Marv]

I have a large Excel file with over 85k rows, that I feed into our system to read and apply the data.

I have a field where my values are separated by a pipe as shown below:

NU001|NU002|NU003

BA001|BA001

BE001|CS001

I am trying to figure out how to identify rows where the data on either side of the pipe is the exact same (such as the second row, BA001|BA001).

I would prefer not to have to delimit the cells because ultimately, that is the format the data should be in, but I understand if that is not possible. I just need to find either a formula, or a way to conditional format the column to make it obvious to me which cells contain duplicate data.

sometimes the data has 3 values with 2 duplicates such as:

BA001|BA001|PR001

Thanks in advance

 

[JB2020]

I have a large Excel file with over 85k rows, that I feed into our system to read and apply the data.

I have a field where my values are separated by a pipe as shown below:

NU001|NU002|NU003

BA001|BA001

BE001|CS001

I am trying to figure out how to identify rows where the data on either side of the pipe is the exact same (such as the second row, BA001|BA001).

I would prefer not to have to delimit the cells because ultimately, that is the format the data should be in, but I understand if that is not possible. I just need to find either a formula, or a way to conditional format the column to make it obvious to me which cells contain duplicate data.

sometimes the data has 3 values with 2 duplicates such as:

BA001|BA001|PR001

Thanks in advance

Try this:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))=1,
LEFT(A1,SEARCH("|",A1)-1)=MID(A1,SEARCH("|",A1)+1,SEARCH("|",A1)-1),
AND(LEFT(A1,SEARCH("|",A1)-1)=MID(A1,SEARCH("|",A1)+1,SEARCH("|",A1)-1),
LEFT(A1,SEARCH("|",A1)-1)=RIGHT(A1,LEN(A1)- SEARCH("|",A1,SEARCH("|",A1)+1))))

 

[Fluff]

Another option

+Fluff v2.xlsm
	A	B
1
2	NU001|NU002|NU003	FALSE
3	BA001|BA001	TRUE
4	BE001|CS001	FALSE
5	BA001|BA001|PR001	TRUE
6
Sheet3

Cell Formulas
Range	Formula
B2:B5	B2	=OR(LEN(SUBSTITUTE(A2,LEFT(A2,5),""))<>LEN(A2)-5,LEN(SUBSTITUTE(A2,MID(A2,7,5),""))<>LEN(A2)-5)

 

[Peter_SSs]

I'm not sure whether you have given us enough samples to show the possible variety, but if

• all codes are 5 characters
• there is a max of 3 codes per cell and a minimum of 2 like your samples
• the dupes must be adjacent ("identify rows where the data on either side of the pipe"). That is you do not count BA001|BA002|BA001 as a dupe,

then try column B.
If my 3rd bullet point is incorrect but the others hold then try column C

20 11 18.xlsm
	A	B	C
1
2	NU001|NU002|NU003	FALSE	FALSE
3	BA001|BA001	TRUE	TRUE
4	BE001|CS001	FALSE	FALSE
5	BA001|BA001|PR001	TRUE	TRUE
6	PR001|BA001|BA001	TRUE	TRUE
7	BA001|BA002|BA001	FALSE	TRUE
Dupes

Cell Formulas
Range	Formula
B2:B7	B2	=OR(MID(A2,7,5)=LEFT(A2,5),MID(A2,7,5)=MID(A2,13,5))
C2:C7	C2	=OR(MID(A2,7,5)=LEFT(A2,5),MID(A2,7,5)=MID(A2,13,5),LEFT(A2,5)=MID(A2,13,5))

 

[E2Marv]

Try this:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))=1,
LEFT(A1,SEARCH("|",A1)-1)=MID(A1,SEARCH("|",A1)+1,SEARCH("|",A1)-1),
AND(LEFT(A1,SEARCH("|",A1)-1)=MID(A1,SEARCH("|",A1)+1,SEARCH("|",A1)-1),
LEFT(A1,SEARCH("|",A1)-1)=RIGHT(A1,LEN(A1)- SEARCH("|",A1,SEARCH("|",A1)+1))))

Thank you so much for your help, this solution in combination with the other two was exactly what I needed!! Thank you so very much for this!

 

[E2Marv]

Thank you all so much for your help with this! These formulas all actually worked to identify the duplicates. I ended up using a combination of Peter_SSs's formula, and the one from JB2020.

Thank you all again for your assistance, and for being so speedy! It is so appreciated.

All the best,

 

[E2Marv]

This

I'm not sure whether you have given us enough samples to show the possible variety, but if

• all codes are 5 characters
• there is a max of 3 codes per cell and a minimum of 2 like your samples
• the dupes must be adjacent ("identify rows where the data on either side of the pipe"). That is you do not count BA001|BA002|BA001 as a dupe,

then try column B.
If my 3rd bullet point is incorrect but the others hold then try column C

20 11 18.xlsm
	A	B	C
1
2	NU001|NU002|NU003	FALSE	FALSE
3	BA001|BA001	TRUE	TRUE
4	BE001|CS001	FALSE	FALSE
5	BA001|BA001|PR001	TRUE	TRUE
6	PR001|BA001|BA001	TRUE	TRUE
7	BA001|BA002|BA001	FALSE	TRUE
Dupes

Cell Formulas
Range	Formula
B2:B7	B2	=OR(MID(A2,7,5)=LEFT(A2,5),MID(A2,7,5)=MID(A2,13,5))
C2:C7	C2	=OR(MID(A2,7,5)=LEFT(A2,5),MID(A2,7,5)=MID(A2,13,5),LEFT(A2,5)=MID(A2,13,5))

was incredible helpful! Column C was what I needed, thank you so much!

 

[E2Marv]

Another option

+Fluff v2.xlsm
	A	B
1
2	NU001|NU002|NU003	FALSE
3	BA001|BA001	TRUE
4	BE001|CS001	FALSE
5	BA001|BA001|PR001	TRUE
6
Sheet3

Cell Formulas
Range	Formula
B2:B5	B2	=OR(LEN(SUBSTITUTE(A2,LEFT(A2,5),""))<>LEN(A2)-5,LEN(SUBSTITUTE(A2,MID(A2,7,5),""))<>LEN(A2)-5)

This formula was also very helpful, thank you so much for your assistance!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

was incredible helpful! Column C was what I needed, thank you so much!

You're welcome. Given the same two initial assumptions from before, here is a shorter one with less function calls required that I think also does the job?

20 11 18.xlsm
	A	B
1
2	NU001|NU002|NU003	FALSE
3	BA001|BA001	TRUE
4	BE001|CS001	FALSE
5	BA001|BA001|PR001	TRUE
6	PR001|BA001|BA001	TRUE
7	BA001|BA002|BA001	TRUE
8	BA001|BA001|BA001	TRUE
Dupes

Cell Formulas
Range	Formula
B2:B8	B2	=OR(FIND(MID(A2,7,5),A2)<7,FIND(MID(A2&"..",13,5),A2&"..")<12)