Finding Duplicate Values/ Different Sorting Methods

[Hankmardukaas]

Hello,

I am looking to find duplicate values within a column of numbers, that also contain letters. These are used to identify recipes. I am trying to determine if we have different recipes that are using the same master reference number. What would be the best way to go about this? It is a large list just over 7k lines. Below is a screenshot of the data. Unfortunately, my admin permissions do not allow me to install the XL2BB plugin to provide a mini sheet in this post.

Any help/info is appreciated.

Thanks!

 

[DanteAmor]

Hi @Hankmardukaas, Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.


According to your example, which ones are duplicates?

And, how do you want to identify duplicates? With a color, with a mark in column C?

Give duplicate examples, explain why they are duplicates for you and mark them as you want.



----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

 

[Hankmardukaas]

Thanks for the reply and help! I would like to have duplicates identified with a color. Would a mark in column c be better? I am not sure of the best way to go about this. Below is an example of duplicate recipes. I am trying to find any other recipes using the number '0522' within the list of data.

Scones, Cherry Choc Chunk (D) -FG 0522F	0522F
Scones, Cherry Choc Chunk (D) 0522S	0522S

 

[DanteAmor]

Put the following formula in C2 and copy it down.
Cells with "DUP" mean they are duplicated.

Dante Amor
	A	B	C
1	Recipe Name	Master Reference	Duplicate
2	Pastry strudel	0770
3	Pastry turnover	0771
4	Scones FG	0772F	Dup
5	Scones	0772S	Dup
6	Scones Blueberry FG	0775F	Dup
7	Scones Blueberry	0775S	Dup
8	Cookie peanut	0776
9	Scones lemon FG	0783F	Dup
10	Scones lemon	0783S	Dup
Hoja1

Cell Formulas
Range	Formula
C2:C10	C2	=IF(COUNTIF(B:B,LEFT(B2,4)&"*")>1,"Dup","")

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Hankmardukaas]

This marked everything as a duplicate. The only two duplicates in the example below are the last two items. Any chance you could explain your formula as well? Thanks again, I appreciate the help.

R- Bread Country White 1485	1485	Dup
R- Bread Seed Lover 1486	1486	Dup
R- Bread Whole Wheat 1487	1487	Dup
R- Coleslaw Spicy Peanut 1488	1488 RET 111	Dup
SD, Grain, Lentil Swt & Spicy (QT) -FG (MT) 1489F	1489F	Dup
SD, Grain, Lentil Swt and Spicy (QT) (MT) 1489S	1489S	Dup

 

[DanteAmor]

Did you copy the formula correctly into cell C2?
Do you have blank spaces in column B after each master referece?

As you can see in my example, only what you commented is what marks it as a duplicate.

=IF(COUNTIF(B:B,LEFT(B2,4)&"*")>1,"Dup","")
What the formula does is take the first 4 characters of cell B2, count how many times those 4 characters appear in column B. If it is more than 1, then it is duplicate.


Here I put the same formula working with your new example:

Dante Amor
	A	B	C
1	Recipe Name	Master Reference	Duplicate
2	R- Bread Country White 1485	1485
3	R- Bread Seed Lover 1486	1486
4	R- Bread Whole Wheat 1487	1487
5	R- Coleslaw Spicy Peanut 1488	1488 RET 111
6	SD, Grain, Lentil Swt & Spicy (QT) -FG (MT) 1489F	1489F	Dup
7	SD, Grain, Lentil Swt and Spicy (QT) (MT) 1489S	1489S	Dup
Hoja1

Cell Formulas
Range	Formula
C2:C7	C2	=IF(COUNTIF(B:B,LEFT(B2,4)&"*")>1,"Dup","")

 

[Hankmardukaas]

Sorry for the late reply and thanks for the follow up. How did you sort the master reference column in that manner to have spaces before and after?

 

[Hankmardukaas]

When using the formula as shown it is marking items that are not duplicates. In the items below, 1502, does not have a duplicate number in my list.

Bar, Almond Butter -FG 6733F	6733F	Dup
Bar, Almond Butter 6733S	6733S	Dup
Bar, Apple (1/2 Pan) -FG 1503F	1502	Dup
Bar, Apple (1/2 Pan) 1503S	1503S	Dup
Bar, Apple -FG 1298F	1298F	Dup
Bar, Apple 1298S	1298S	Dup
Bar, Apricot Linzer Nut (1/2 Pan) -FG 1531F	1531F	Dup
Bar, Apricot Linzer Nut (1/2 Pan) 1531S	1531S	Dup
Bar, Apricot Linzer Nut -FG 5390F	5390F	Dup
Bar, Apricot Linzer Nut 5390S	5390S	Dup

 

[DanteAmor]

When using the formula as shown it is marking items that are not duplicates. In the items below, 1502, does not have a duplicate number in my list.

Check the formula carefully, in my test the result is correct:

varios 09nov2023b.xlsm
	A	B	C
1	Recipe Name	Master Reference	Duplicate
2	Bar, Almond Butter -FG 6733F	6733F	Dup
3	Bar, Almond Butter 6733S	6733S	Dup
4	Bar, Apple (1/2 Pan) -FG 1503F	1502
5	Bar, Apple (1/2 Pan) 1503S	1503S
6	Bar, Apple -FG 1298F	1298F	Dup
7	Bar, Apple 1298S	1298S	Dup
8	Bar, Apricot Linzer Nut (1/2 Pan) -FG 1531F	1531F	Dup
9	Bar, Apricot Linzer Nut (1/2 Pan) 1531S	1531S	Dup
10	Bar, Apricot Linzer Nut -FG 5390F	5390F	Dup
11	Bar, Apricot Linzer Nut 5390S	5390S	Dup
dam13

Cell Formulas
Range	Formula
C2:C11	C2	=IF(COUNTIF(B:B,LEFT(B2,4)&"*")>1,"Dup","")