Forum Discussion
Find out & highlight duplicate data(number)within same excel sheet in diff column or in diff sheet
I have number in excel column A like "5434567880765" and so on & in column B a JSON Data is present where the no "5434567880765" may be present? So how to found the duplicate & highlight it in the same or different row in that excel sheet ? PFB the Excel column A & Column B example, It's may be present in same sheet or different sheet.
A B
4356778 "team":"In","test":"4356778","IsPresent":"True"
9876546 "team":"US","test":"9876546","IsPresent":"True"
5435657 "team":"US","test":"2874541","IsPresent":"True"
5 Replies
PFB the sample :
There is more than 3000 rows like below where we have to highlight the duplicate :
The Column A number may be present in Column B or may not be . It may be present is same Row or different Row.
Requirement is highlight the duplicate data in color. Please let me know the work around. Basically these are present is different excel workbook. Column A Present in Excel1 & Column B present in Excel2 (in Column A)
Column A Column B
11345667865
{"mode":"User","no":"11345667865","Date":"25052025"}
11128765434
{"mode":"User","no":"11128765434","Date":"25052025"}
11443679864
{"mode":"User","no":"11443679864","Date":"25052025"}
11238765345
{"mode":"User","no":"11238765345","Date":"25052025"}
11342345678
{"mode":"User","no":"11342345678","Date":"25052025"}
11254621905
{"mode":"User","no":"11254135405","Date":"25052025"}
11456754376
{"mode":"User","no":"11453214376","Date":"25052025"}
11198745632
{"mode":"User","no":"11198745632","Date":"25052025"}
11345673452
{"mode":"User","no":"11345619852","Date":"25052025"}
11298745672
{"mode":"User","no":"11254621905","Date":"25052025"}
11423456798
{"mode":"User","no":"11423012798","Date":"25052025"}
11314567234
{"mode":"User","no":"11345673452","Date":"25052025"}
11498723451
{"mode":"User","no":"11456754376","Date":"25052025"}
11287346573
{"mode":"User","no":"11342345678","Date":"25052025"}
11321345321
{"mode":"User","no":"11128765434","Date":"25052025"}
Yes, using conditional formatting will let you highlight duplicates. If they are on 1 sheet then simple go to Conditional Formatting -> Highlight Cell Rules -> Duplicate Values ...
But if you want to cross over multiple sheets then you need a custom rule AND you will have to define a range NAME and the range on each sheet can't be too big (i.e. max # rows combined must be less than max # rows allowed in Excel; NOTE alternatively you could stack columns if you much more limited # of columns per sheet but have many more rows)
So in the Name Manager I defined a name called 'allsheets' and defined it as the multiple sheet range $A$1:$J$10 across between and including Sheet1:Sheet2 (in my case that was only those 2 sheets).
Then on each sheet you will need to create a custom rule similar to the following:
=SUM(--(A1=VSTACK(allsheets)))>1
Alternatively you could put the VSTACK directly into the Name Definition and remove it from the Conditional Formatting Rule (really not sure if it makes any difference).
Sample attached and the dark red is from the above rule and the light red is the default single sheet (only the 159 is highlighted because it is in row 11 and the above rule stops at row 10 (and because of order of the rules the 161 is shown in dark red)
May consider conditional formatting for same sheet:
=ISNUMBER(SEARCH(A1, B1))PFB the sample :
There is more than 3000 rows like below where we have to highlight the duplicate :
=ISNUMBER(SEARCH(A1, B1)) selecting only first few rows where the duplicate exist in same row,
The Column A number may be present in Column B or may not be . It may be present is same Row or different Row.
Requirement is highlight the duplicate data in color. Please let me know the work around. Basically these are present is different excel workbook. Column A Present in Excel1 & Column B present in Excel2 (in Column A)
Column A Column B
11345667865
{"mode":"User","no":"11345667865","Date":"25052025"}
11128765434
{"mode":"User","no":"11128765434","Date":"25052025"}
11443679864
{"mode":"User","no":"11443679864","Date":"25052025"}
11238765345
{"mode":"User","no":"11238765345","Date":"25052025"}
11342345678
{"mode":"User","no":"11342345678","Date":"25052025"}
11254621905
{"mode":"User","no":"11254135405","Date":"25052025"}
11456754376
{"mode":"User","no":"11453214376","Date":"25052025"}
11198745632
{"mode":"User","no":"11198745632","Date":"25052025"}
11345673452
{"mode":"User","no":"11345619852","Date":"25052025"}
11298745672
{"mode":"User","no":"11254621905","Date":"25052025"}
11423456798
{"mode":"User","no":"11423012798","Date":"25052025"}
11314567234
{"mode":"User","no":"11345673452","Date":"25052025"}
11498723451
{"mode":"User","no":"11456754376","Date":"25052025"}
11287346573
{"mode":"User","no":"11342345678","Date":"25052025"}
11321345321
{"mode":"User","no":"11128765434","Date":"25052025"}
PFB the sample :
There is more than 3000 rows like below where we have to highlight the duplicate :
=ISNUMBER(SEARCH(A1, B1)) is highlighting only first few rows where the duplicate is exist in the same row .
The Column A number may be present in Column B or may not be . It may be present is same Row or different Row.
Requirement is highlight the duplicate data in color. Please let me know the work around. Basically these are present is different excel workbook. Column A Present in Excel1 & Column B present in Excel2 (in Column A)
Column A Column B
11345667865
{"mode":"User","no":"11345667865","Date":"25052025"}
11128765434
{"mode":"User","no":"11128765434","Date":"25052025"}
11443679864
{"mode":"User","no":"11443679864","Date":"25052025"}
11238765345
{"mode":"User","no":"11238765345","Date":"25052025"}
11342345678
{"mode":"User","no":"11342345678","Date":"25052025"}
11254621905
{"mode":"User","no":"11254135405","Date":"25052025"}
11456754376
{"mode":"User","no":"11453214376","Date":"25052025"}
11198745632
{"mode":"User","no":"11198745632","Date":"25052025"}
11345673452
{"mode":"User","no":"11345619852","Date":"25052025"}
11298745672
{"mode":"User","no":"11254621905","Date":"25052025"}
11423456798
{"mode":"User","no":"11423012798","Date":"25052025"}
11314567234
{"mode":"User","no":"11345673452","Date":"25052025"}
11498723451
{"mode":"User","no":"11456754376","Date":"25052025"}
11287346573
{"mode":"User","no":"11342345678","Date":"25052025"}
11321345321
{"mode":"User","no":"11128765434","Date":"25052025"}
