Forum Discussion
giriokamat
Aug 23, 2022Copper Contributor
Returning part of column header corresponding to matched value in multiple spreadsheets
Looking for output this way:
Sheet 1
w | 1 |
e | 1 |
r | 2 |
t | 55 |
y | 66 |
u | 2 |
i | 0 |
q | 1 |
e | 1 |
r | 2 |
Dealing with Multiple sheets with Name and Gender (1,2,3,4,55,66). Code has to match the name in Sheet 1 with Sheet 2 and Sheet 3 and return the part of the column header corresponding to "1" on the respective sheet. Sheets may have multiple rows with the same name as shown in sheet 2 with "w".
Sheet 2
Name | Male 1 | Female 2 | other 3 | other 4 | other 55 | Donotknow 66 |
w | 0 | 0 | 0 | 0 | 0 | 0 |
w | 1 | 0 | 0 | 0 | 0 | 0 |
a | 0 | 0 | 0 | 0 | 0 | 1 |
q | 1 | 0 | 0 | 0 | 0 | 0 |
r | 0 | 1 | 0 | 0 | 0 | 0 |
e | 1 | 0 | 0 | 0 | 0 | 0 |
t | 0 | 0 | 0 | 0 | 1 | 0 |
y | 0 | 0 | 0 | 0 | 0 | 1 |
u | 0 | 1 | 0 | 0 | 0 | 0 |
Sheet 3
e | 0 | 0 | 0 | 0 | 0 | 0 |
t | 0 | 0 | 0 | 0 | 0 | 0 |
y | 0 | 0 | 0 | 0 | 0 | 1 |
u | 0 | 1 | 0 | 0 | 0 | 0 |
=IFERROR(INDEX({1;2;3;4;55;66},MATCH(1,INDEX($F$3:$K$11,MATCH(A2,$E$3:$E$11,0),0),0)),"NO MATCH")
This formula returns "no match" for name "w" which is a limitation in my understanding. This is because MATCH returns the first match for every name. The first match for "w" is in cell E3 and there are only 0s in range F3:K3.
=IFERROR(IFERROR(INDEX({1,2,3,4,55,66},SUMPRODUCT(($E$3:$E$11=A2)*MMULT($F$3:$K$11,TRANSPOSE(COLUMN(F:K))))-5),INDEX({1,2,3,4,55,66},SUMPRODUCT(($E$14:$E$18=A2)*MMULT($F$14:$K$18,TRANSPOSE(COLUMN(F:K))))-5)),0)
This formula includes another sheet. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. In the example i've added name "z".
- Patrick2788Silver Contributor
Here's a 365 solution with Lambda.
Roll up the data with VSTACK, add a totals column to determine which rows = 0, and then filter out rows with all 0s.
=LET(stack,VSTACK(Sheet2:Sheet3!$A$1:$G$11),totals,BYROW(stack,LAMBDA(row,SUM(row))),FILTER(stack,totals<>0))
Lambda to determine the column number for a given input containing a 1.
=LAMBDA(input,BYROW(input,LAMBDA(row,XMATCH(1,XLOOKUP(row,TAKE(RollUp,,1),RollUp)))))
Formula to deliver the results:
=IFERROR(BYROW(A2:A11,LAMBDA(col,TEXTAFTER(CHOOSECOLS(header,FindCol(col))," "))),0)
PeterBartholomew1 may be interested in giriokamat 's request. Including him here.
- PeterBartholomew1Silver Contributor
Since you referred the workbook to me, I have, somewhat rudely, made a number of changes!
My version of RollUp uses a slightly less rigorous filter criterion
RollUp = LET( stack, VSTACK(tables), FILTER(stack, TAKE(stack, , 1) <> "") );
Rather than going for FindCol, I used a second XLOOKUP to return the Header directly. I removed an instance of BYROW because the input to the Lambda function had already been reduced to a scalar by the worksheet formula.
FindHdr = LAMBDA(input, XLOOKUP(1, XLOOKUP(input, TAKE(RollUp, , 1), RollUp), header) );
I converted the BYROW in the worksheet formula to MAP because the names form a single column. By deferring the application of the TEXTAFTER function, I could let the helper function operate directly on the FindHdr lambda function rather than wrapping it within a further LAMBDA to pass the name parameter,
WorksheetFormula = IFERROR(TEXTAFTER( MAP(nameList, FindHdr), " "), 0)
There might be advantages in your original use of BYROW in that the inner nested functions could accept array arguments if required. I went for case-specific simplification though.
- Patrick2788Silver ContributorThank you for sharing. That's some excellent efficiency. When there's no need to obtain the column number to pick from the header, it makes things a bit simpler. It seems there is some pattern building with these exercises and methods for obtaining a solution. Any time multiple sheets are involved it becomes a question of using VSTACK or TOCOL, for example. If VSTACK is used, then inevitably FILTER will follow in some capacity. If TOCOL is used, there will be some wrapping more than likely. It's very stimulating and fun to have plenty of options!
I welcome different solutions. The OP has 3 solutions to pick from to suit his/her needs and version. Same goes for anyone that may be browsing the discussion.
- giriokamatCopper Contributor
I was able to get this output with
=TEXTJOIN(";",TRUE,IF(INDEX(Sheet2!$B$2:$G$10,MATCH(A2,Sheet2!$A$2:$A$10,0),0)=1,{1,2,3,4,55,66},"")). How can i modify this to get the first row corresponding to "w" working. I
w e 1 r 2 t 55 y 66 u 2 i #N/A q 1 e 1 r 2 - OliverScheurichGold Contributor
=IFERROR(INDEX({1,2,3,4,55,66},SUMPRODUCT(($E$3:$E$11=A2)*MMULT($F$3:$K$11,TRANSPOSE(COLUMN(F:K))))-5),0)
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
- giriokamatCopper Contributor
Thanks for your help. I have landed on this code:
=IFERROR(INDEX({1,2,3,4,55,66},MATCH(1,INDEX(F3:K11,MATCH(A2,E3:E11,0),0),0)),"NO MATCH")
Any limitations of this code?
Is it possible to extend this into multiple sheets? please let me know.