I know, multiple questions has been asked with respect to duplicate in excel. However, I didnt find anything similar, so asking the same.
I have an excel consist of one columns which contains large set of records in the format below, say
XYZ/123/456.abc.def.bla.ghi
ABC/985/klm_nop
FGH/999/mon.pqr.stu
I want to compare for duplicate but by ignoring the first letter (till ‘/’). If it matches the highlighted part with any other set in the column then return me as a duplicate/highlight otherwise unique.
any help on this ? thanks in advance.
>Solution :
So your question is not how to find duplicates, but how to get rid of the first part of your records.
You will achieve this with this formula: =MID(A1,FIND("/",A1)+1,LEN(A1)) – assuming the first entry is in A1
Then you can use conditional formatting for duplicate values.