Forum Discussion
Filter rows from multiple column data
I currently have 5 columns with names I.E A, B, C, D, E and I want to be able to return the sum of the data (F1:AE23) for when those 5 names are duplicated. However, they may be in a different order I.E. A, C, D, B, E
=BYROW(A1:E23,LAMBDA(x,IF(TEXTJOIN("",,TRANSPOSE(SORTBY(TRANSPOSE(x),TRANSPOSE(x))))="ABCDE",SUM(OFFSET(x,0,5,1,26)),"")))
An alternative could be this formula which is easier to adapt if you want to check if e.g. A to Z occur in the first 26 columns in any order.
4 Replies
Barring any anomalies in the data or anything different than what's been offered in the mock data, this might work for you (If you have access to LAMBDA).
We can simply the logic by taking 5 columns from the left of a given row. Run UNIQUE by column and if the result is 5 (no dupes), then SUM the row.
=LAMBDA(row,IF(COUNTA(UNIQUE(TAKE(row, , 5), 1)) = 5, SUM(DROP(row, , 5)), ""))Folded into BYROW:
=BYROW(data,Total)=BYROW(A1:E23,LAMBDA(x,IF(TEXTJOIN("",,TRANSPOSE(SORTBY(TRANSPOSE(x),TRANSPOSE(x))))="ABCDE",SUM(OFFSET(x,0,5,1,26)),"")))
An alternative could be this formula which is easier to adapt if you want to check if e.g. A to Z occur in the first 26 columns in any order.
=BYROW(A1:E23,LAMBDA(row,IF(AND(ISNUMBER(SEARCH("A",TEXTJOIN("",,row))),ISNUMBER(SEARCH("B",TEXTJOIN("",,row))),ISNUMBER(SEARCH("C",TEXTJOIN("",,row))),ISNUMBER(SEARCH("D",TEXTJOIN("",,row))),ISNUMBER(SEARCH("E",TEXTJOIN("",,row)))),SUM(OFFSET(row,0,5,1,26)),"")))
You can try this formula. However there should be an easier way to check if A, B, C, D and E occur in the first 5 columns in any order.
- This worked! Thank you for the help!
