Returning part of column header corresponding to matched value in multiple spreadsheets

[giriokamat]

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

[#2]

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

[#3]

@giriokamat 

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.

[#4]

@Patrick2788 

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.

[#5]

@giriokamat 

=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.

 

[#6]

Thank 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.

[#7]

@Patrick2788 

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'.

[#8]

@OliverScheurich 

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.

[#9]

This one is a bit longer but I like the surgical approach to this task. I like it because it shows how you're stepping through the task step by step. Obtain header values, stack, filter stack, etc.

I can appreciate the 'rule of thumb reference' used correctly. Every time I hear someone say that casually I wonder if he/she knows where it came from!

[#10 · Accepted solution]

@giriokamat 

=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".

[#11]

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.

[#12]

@giriokamat 

=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.