Search code examples
excelexcel-formulaexcel-365

Find the match or matches with most same values in the same columns

I want to find, based on the criteria of a match in my table, which match or matches have the most same criteria in the same columns with priority.

enter image description here

Could u help me?

I tried with countifs but no result. and i want for a big data thats an example.

Solution

  • I think I understand what you mean, even though your shared picture shows a different result:

    =LET(fullrange,  A2:E11,
     number,     TAKE(fullrange,,1),
     data,       DROP(fullrange,,1),
     sumbyrow,   BYROW(data,LAMBDA(x,SUM(x))),
MAP(SEQUENCE(COLUMNS(data),,COLUMNS(data),-1),LAMBDA(x,TEXTJOIN(",",1,FILTER(number,sumbyrow=x,"")))))

    This takes the range fullrange and splits the first column numbers and the remaining columns data

    sumbyrow sums the matches of the data row by row and creates an array of these.

    Then MAP performs a TEXTJOIN for each number where sumbyrow equals the sequence of the number of columns of data to 1, row by row).

    enter image description here