I have two arrays. One contains string values referring to other ranges of the sheet (other pages). The ranges that the strings are referencing have a constant column size (horizontal, 4 in this case) but may differ in row size (vertical). The other array contains a list of names. Both arrays are the same size, with positioning corresponding to each. I want to obtain a longer range from this data, with the names of the second array to the right of each range respectively. Preferably with a single function/cell, spilling over to the space required. I would extremely dislike to have to use google appscript/vba script. Layout of the problem:
MainPage:
Reference | Name |
---|---|
FirstPage!A2:D4 | Bob |
SecondPage!A3:D5 | Adam |
FirstPage:
row# | Id | Amount | Goods | Price |
---|---|---|---|---|
2 | 23 | 45 | Celery | 120$ |
3 | 12 | 34 | Radish | 100$ |
4 | 8 | 32 | friedegg | 50$ |
SecondPage:
row# | Id | Amount | Goods | Price |
---|---|---|---|---|
3 | 35 | 23 | Lettuce | 32$ |
4 | 10 | 64 | Milk | 87$ |
5 | 9 | 95 | cpus | 234$ |
Wanted Output:
row# | Id | Amount | Goods | Price | Name |
---|---|---|---|---|---|
2 | 23 | 45 | Celery | 120$ | Bob |
3 | 12 | 34 | Radish | 100$ | Bob |
4 | 8 | 32 | friedegg | 50$ | Bob |
5 | 35 | 23 | Lettuce | 32$ | Adam |
6 | 10 | 64 | Milk | 87$ | Adam |
7 | 9 | 95 | cpus | 234$ | Adam |
What I have tried:
lambda(id, MAKEARRAY(rows(indirect(index(MainSheet!A2:A999, id))), 5, LAMBDA(row, column, if(column=5, index(MainSheet!B2:B999, id), index(INDIRECT(index(MainSheet!A2:A999, id)), row, column)))))(1)
\-> works for individual ranges, but I can´t figure out how to merge them (hence the question title) (just replace the 1 with the id of the reference)
VSTACK(makearray(counta(MainSheet!A2:A999), 1, lambda(id, _, MAKEARRAY(rows(indirect(index(MainSheet!A2:A999, id))), 5, LAMBDA(row, column, if(column=5, index(MainSheet!B2:B999, id), index(INDIRECT(index(MainSheet!A2:A999, id)), row, column)))))))
\-> "result should be in a single row" error
reduce({},makearray(counta(MainSheet!A2:A999), 1, lambda(id, _, MAKEARRAY(rows(indirect(index(MainSheet!A2:A999, id))), 5, LAMBDA(row, column, if(column=5, index(MainSheet!B2:B999, id), index(INDIRECT(index(MainSheet!A2:A999, id)), row, column)))))), LAMBDA(array, array2, array&array2))
\-> "Reference error": it seems that reduce()
doesn't work with arrays as an initial value. (I've also tried forcing it with arrayformula()
REDUCE
to iterate A2:A3
and get the ref
erencesOFFSET
to get the corresponding name
,INDIRECT
to get the corresponding range
from the given ref
erence,IF/SEQUENCE
to create duplicate names according to the number of ROWS
in the range
STACK
the range
and names
H
orizontally and finally V
ertically stack the c
urrent iterator result with the previous a
ccumulator.=ARRAYFORMULA(
REDUCE(
SPLIT("Id,Amount,Goods,Price,Name",","),
A2:A3,
LAMBDA(a,c,
LET(
ref, c,
name, OFFSET(c,0,1),
range, INDIRECT(ref),
names, IF(SEQUENCE(ROWS(range)),name),
VSTACK(a,HSTACK(range,names))
)
)
)
)