The rows or selection can contain names but also other (different) information but the name is the value that needs to be searched to find a duplicate. It’s hard to explain so I will show it with an example:
Rows of data or selection:
ICT-XXXX – New laptop // Greg Smeets // 2024-07-05
ICT-XXXX – old laptop // Julie De Roover // 2024-07-05
ICT-XXXX – New laptop // Anton Gilles // 2024-07-05
ICT-XXXX – laptop // Wout Tiberi // 2024-07-05
ICT-XXXX – New laptop // Jaffer Antone // 2024-07-05
ICT-XXXX – laptop // Julie De Roover // 2024-07-05
ICT-XXXX – Old laptop // Greg Smeets // 2024-07-05
So the rows of Julie De Roover & Greg Smeets should be shown in red for example.
The conditional foramtting > highlight duplicaties values function in excel doesn’t support this or does not offer a solution.
2
If you have Excel 365 you can do it with one formula in conditional formatting without a helper column:
=LET(t,A1,
n,TEXTBEFORE(TEXTAFTER(t,"//"),"//"),
all,TEXTBEFORE(TEXTAFTER($A$1:$A$7,"//"),"//"),
COUNTA(FILTER(all,all=n))>1)
Without Excel 365 the simplest way would be to use a helper column with the extracted names:
Formula for the helper column:
=TRIM(MID(A1,FIND("//",A1)+2,FIND("//",A1,FIND("//",A1)+2)-FIND("//",A1)-2))
Formula for conditional formatting:
=COUNTIF($B$1:$B$7,B1)>1
5
Here’s another way, using named formulas:
getName
– extract the name before the last part (based on your comment):
=LAMBDA(cell,
LET(
num_parts, (LEN(cell) - LEN(SUBSTITUTE(cell, "//", ""))) / 2,
IFERROR(
TRIM(TEXTAFTER(TEXTBEFORE(cell, "//", num_parts), "//", num_parts - 1)),
""
)
)
)
allNames
– to make selecting names from the input column – adjust according to your data (will automatically include new rows within A1000):
=getName(Sheet1!$A$1:XLOOKUP(TRUE, Sheet1!$A$1:$A$1000 <> "", Sheet1!$A$1:$A$1000, , , -1))
isDuplicate
– the main function used in Conditional formatting:
=LAMBDA(a,
AND(
NOT(ISBLANK(a)),
IFERROR(
ROWS(FILTER(allNames, allNames = getName(a))) > 1,
FALSE
)
)
)
For conditional formatting, select a1:axx with a1 begin the active cell, enter:
=isDuplicate($A1)
If you’re looking to highlight, duplicate names in Excel but your data has different info in each row (like dates or tasks), the basic “Highlight Duplicates” won’t quite cut it.
Here’s what you can do:
- Select your range of data.
- Go to Conditional Formatting > New Rule > Use a formula.
- Use this formula (assuming names are in column B):
=COUNTIF($B:$B, B1) > 1 - Set your formatting (red fill, for example) and hit OK.
If all your data is in one column, you’d need a more specific formula to pull out the name from between “//”. But this approach should work in most cases where you have clear columns. It’s a simple trick but really handy for catching duplicates based on names, even if the rest of the row is different.
1