-1

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Teronimo
  • 13
  • 1
  • Could you post one or more samples? – P.b Mar 17 '23 at 21:31
  • i want to post a screenshot but i cant cause im new user, do u know other way? – Teronimo Mar 17 '23 at 21:47
  • post a link to the picture stored on imgur.com – Scott Craner Mar 17 '23 at 21:48
  • https://imgur.com/a/LiJwPV5 thats the correct because i want for the exactly sme columns thanks a lot – Teronimo Mar 17 '23 at 21:49
  • 3 also has 3 in your picture – Scott Craner Mar 17 '23 at 21:56
  • yes but i want the groups not uniques – Teronimo Mar 17 '23 at 21:59
  • 1
    I still do not understand why 3 is in a group with 4. And why 2 was left off. – Scott Craner Mar 17 '23 at 22:02
  • yes u have right, 2 is in group with 5,6,7,8, & 9 with 2 exactly columns. My mistake in pic – Teronimo Mar 17 '23 at 22:21
  • Would you provide the input sample and the expected output in Table Markdown format, you can use this [tool](https://www.tablesgenerator.com/markdown_tables#). You can copy from your excel, then generate it and copy back to the question. Similarly the logic for the expected output or better the expected output as part of the sample. We don't even know in what format you want the output. If there is any error in your sample data, the expectation is that you update the question properly. There is no need to upload screenshot for a sample data like you have, use table markdown – David Leal Mar 18 '23 at 00:43
  • hi thanks, i try to use this tool. I m really sorry because my english no so good and i m a new one here. I dont want some specific format from my output, only the right results. Maybe using filter function to show me the rows with same criteria. – Teronimo Mar 18 '23 at 05:53

1 Answers1

1

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

P.b
  • 8,293
  • 2
  • 10
  • 25
  • You really feel me, thanks a lot bro. It's perfect. Do you know if it possible to add if i want a specific column to be stadard 1 for example column B2? – Teronimo Mar 18 '23 at 16:01
  • B2 is a cell. B:B would be the column (or just B). But I don't understand what you're asking. Do you mean to only include rows where the Criteria1 column equals 1? Also, if this answered your question, please accept it by clicking the green `V` or by upvoting. – P.b Mar 18 '23 at 16:28
  • yes i mean if B column is 1 not 0 no the cell B2. For example IF(B:B=1; mpla mpla with your formula. sorry for my example is ridicoulus i know but i cant handle my english good. – Teronimo Mar 18 '23 at 17:08
  • `=LET(fullrange,A2:E11,filtered,FILTER(fullrange,CHOOSECOLS(fullrange,2)),number,TAKE(filtered,,1),data,DROP(filtered,,1),sumbyrow,BYROW(data,LAMBDA(x,SUM(x))),MAP(SEQUENCE(COLUMNS(data),,COLUMNS(data),-1),LAMBDA(x,TEXTJOIN(",",1,FILTER(number,sumbyrow=x,"")))))` is what I think you mean – P.b Mar 18 '23 at 17:23