Need any formula or solution if 3 numbers match in a single cell

  • Hi Friends,

    sheet is attached.

    I'm new in excel and need help, i have 10k rows that have codes of employee i want to highlight or count in how many cells have at least 3 common numbers,

    for example 89..19...08 are in 3 rows. I want a formula that tell how many matched cells are there.

  • First of all thanks for your quick reply

    the solution you provided is working but

    what if any other 3 number combination matched and its only counts a1 cell numbers, but i want to match all columns. see attachment.

    I think there are at least more than 500 different numbers combination matched bcz i have 10k employee codes.

  • Thanks for your Thanks ...AND for the Like :thumbup:


    Do you mean that each one of your 500 numbers has to be considered as THE reference for the comparison ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • yes brother, Is it possible to find out which 3 numbers are matched and how many times its matched?

    I want to count only 1 combination of 3 number etc if a1 has ( 11 22 33 44 55 66 ) and a5 has 33 44 55 it should bet 1 but if any other cell has 33 66 22 then this combination should not 2 bcz it has difference combination numbers.

    i want to find how many times only 33 44 55 has in others cells and how many times 33 66 22 has in other cells.

  • Hello,


    In order to build the appropriate macro which will produce your expected result, much better than an image... it is better to attach a sample file with the final report you need ... even if you have to manually type in the numbers ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    Not sure to understand exactly the final output ...

    In your first message, you said

    Quote


    Count in how many cells have at least 3 common numbers, ...

    and, in your latest sample file, it looks like you need 2 common numbers ...


    Thanks for your clarification

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    Honestly, it is quite difficult to follow your idea ...


    Understand the combination of at least 3 numbers in a single cell ...


    But in your example, you are using 2244556677 ... or 10 digits ...whereas in your sample file the rule was 8 digits ...!!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • There are total 8 digits in the cell

    For example if in cell a1 11223344

    And in cell a2 00112233 and in cell a3 44112277 and in cell a4 88001122

    The answer should be combination 3

    bcz combination of 112233 are in cell a1 and in cell a2 combination off 001122 are in cell a2 and a4 and combination of 114422 are in a1 and a3. It will be plus point if it tell which combination are there

  • So you do work with 8 digits numbers ...


    Now, despite your sample workbook, still cannot actually see what is the structure of your final output....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)