Forum Discussion
Lookup across spreadsheet- Random Locations for Headings
Hoping there is a way to lookup the Firstname, Surname, Data1, Data2 and populate the list as shown below- This is a spit out from a database and as such the data can be randomly plotted on the output- However the data is always immedietly below.
Another approach. It's set to handle data like in the example but could be adjusted as needed.
'ReShape =LAMBDA(matrix,LET( header, {"First Name", "Last Name", "Data1", "Data2", "Data3", "Data4"}, BinVector, TOCOL( N((matrix <> "") * (LEFT(matrix, 4) <> "Data") * (RIGHT(matrix, 4) <> "Name")) ), seq, SEQUENCE(ROWS(BinVector)), r, WRAPROWS(FILTER(seq, BinVector <> 0), 6), reshaped, INDEX(TOCOL(matrix), r), VSTACK(header, reshaped) ))
8 Replies
Another approach. It's set to handle data like in the example but could be adjusted as needed.
'ReShape =LAMBDA(matrix,LET( header, {"First Name", "Last Name", "Data1", "Data2", "Data3", "Data4"}, BinVector, TOCOL( N((matrix <> "") * (LEFT(matrix, 4) <> "Data") * (RIGHT(matrix, 4) <> "Name")) ), seq, SEQUENCE(ROWS(BinVector)), r, WRAPROWS(FILTER(seq, BinVector <> 0), 6), reshaped, INDEX(TOCOL(matrix), r), VSTACK(header, reshaped) ))- Hi Patrick-
Appreciate this- However, where is the formula editable?
=IFERROR(IFERROR(INDEX($B$1:$B$28,SMALL(IF(($B$1:$B$28=H$1)*(ROW($A$1:$A$28)>$E2)*(ROW($A$1:$A$28)<$E3),ROW($B$1:$B$28)+1),1)),INDEX($C$1:$C$28,SMALL(IF(($C$1:$C$28=P$8)*(ROW($A$1:$A$28)>$E2)*(ROW($A$1:$A$28)<$E3),ROW($B$1:$B$28)+1),1))),INDEX($D$1:$D$28,SMALL(IF(($D$1:$D$28=P$8)*(ROW($A$1:$A$28)>$E2)*(ROW($A$1:$A$28)<$E3),ROW($B$1:$B$28)+1),1)))You can try this formula which returns the results in the intended layout. The formula is in cell H2 and filled across range H2:K5.
The formulas in cells E2 (filled down) and F2 (filled across range F2:G5) are in my first reply in this discussion.
All formulas must be entered with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
agags333 If you have Excel for MS365, you could try TOROW with WRAPROWS to arrange the data in a table, then use a combination of CHOOSECOLS with VSTACK to return the desired output:
=LET( arr, WRAPROWS(TOROW(A1:D30, 1), 12), VSTACK( TAKE(CHOOSECOLS(arr, 1, 2, 5, 6, 9, 10), 1), CHOOSECOLS(arr, 3, 4, 7, 8, 11, 12) ) )Note: this will only work if there are always 6 data fields plus headers (for a total of 12 cells containing values) per person, and all of the other cells within the range are blank, as shown in your sample screenshot.
MS365 Solution with Dynamic Array Functions
An alternative could be these formulas along with a slightly different layout of the result table.
All formulas must be entered with ctrl+shift+enter if one doesn't work with Office365 or Excel for the web or Excel 2021.
=MATCH(F2&G2,$A$1:$A$28&$B$1:$B$28,0)This formula is in cell E2 and filled down to cell E5. E6 is filled manually with 100. 100 is a random number greater than the last row of the sample data (28).
=IFERROR(INDEX(A$1:A$28,SMALL(IF(A$1:A$28=F$1,ROW($A$1:$A$28)+1),ROW(A1))),"")This formula is in cell F2 and filled across range F2:G5.
=IFERROR(INDEX(IF(MOD(COLUMN()+3,3)=2,$B$1:$B$28,IF(MOD(COLUMN()+3,3)=0,$C$1:$C$28,$D$1:$D$28)),SMALL(IF((IF(MOD(COLUMN()+3,3)=2,$B$1:$B$28,IF(MOD(COLUMN()+3,3)=0,$C$1:$C$28,$D$1:$D$28))=H$1)*(ROW($A$1:$A$28)>$E2)*(ROW($A$1:$A$28)<$E3),ROW($A$1:$A$28)+1),1)),"")This formula is in cell H2 and filled across range H2:S5.
