Aug 23 2022 08:56 AM
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 |
Aug 23 2022 10:07 AM
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 |
Aug 23 2022 11:39 AM
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.
Aug 23 2022 02:27 PM
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.
Aug 23 2022 02:38 PM
=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.
Aug 23 2022 03:36 PM
Aug 24 2022 02:37 AM
It is never enough to leave well alone! I have gone for a more verbose formula in which every step is assigned a name in much the same manner as one could us helper ranges in traditional spreadsheets.
= LET(
headerValue, VALUE(TEXTAFTER(header," ")),
stack, VSTACK(tables),
stackedTable, FILTER(stack, TAKE(stack, , 1) <> ""),
stackRowHdr, TAKE(stackedTable,,1),
stackValues, ReduceTableλ(stackedTable,headerValue),
XLOOKUP(nameList, stackRowHdr, stackValues,0)
)
The first line extracts the final value from each header
The next two lines stack the 3D range and removes empty rows
The table is then reduced to two columns, the first being the row headers and the second contains the values corresponding to the first 1 of each row (using a Lambda function).
These two columns form the lookup array and return array for the final lookup.
ReduceTableλ
//"Looks up the first 1 in each row and returns the header"
= LAMBDA(table,header,
BYROW(table,
LAMBDA(row, XLOOKUP(1,row,header,0))
)
);
As you see, this is the antithesis of the 'rule of thumb' that says no formula should be longer than your thumb. The idea is more one of 'if the formula is not as long as your arm, you haven't explained the steps of your calculation sufficiently'.
Aug 24 2022 06:35 AM
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.
Aug 24 2022 07:11 AM
Aug 24 2022 02:11 PM
Solution=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".
Aug 25 2022 05:51 AM - edited Aug 25 2022 05:53 AM
Thank You. I have another follow up question. If I have a few empty cells with no "0's" How can I modify this code to account for this? Please and Thanks. I am using excel 2016.
Aug 25 2022 09:49 AM
=IFERROR(INDEX({1,2,3,4,55,66},SUMPRODUCT(($E$3:$E$11=A2)*MMULT(N(($F$3:$K$11<>"")*$F$3:$K$11),TRANSPOSE(COLUMN(F:K))))-5),0)
You can try this formula. Enter the formula with ctrl+shift+enter since you don't work with Office365 or 2021. This example is done in Excel 2013.
Aug 24 2022 02:11 PM
Solution=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".