Forum Discussion

thisisanirbann's avatar
thisisanirbann
Copper Contributor
May 23, 2025

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 sa...
Formulas and Functions
Kidd_Ip's avatar
Kidd_Ip
MVP
May 24, 2025

May consider conditional formatting for same sheet:

 

=ISNUMBER(SEARCH(A1, B1))

 

  • thisisanirbann's avatar
    thisisanirbann
    Copper Contributor
    May 24, 2025

    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"}

  • thisisanirbann's avatar
    thisisanirbann
    Copper Contributor
    May 24, 2025

    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"}

Resources