My question was inspired by this post in that I'm wondering if it's possible to create a formula to stack a dynamic amount of arrays based on a list (see below for clarification).
Sample Starting Data From Three Sources
| ID | Amount |
|---|---|
| India | 9 |
| Delta | 4 |
| Hotel | 8 |
| ID | Amount |
|---|---|
| Alpha | 1 |
| Echo | 5 |
| Foxtrot | 6 |
| ID | Amount |
|---|---|
| Bravo | 2 |
| Gulf | 7 |
| Charlie | 3 |
Desired final result:
| ID | Amount |
|---|---|
| Alpha | 1 |
| Bravo | 2 |
| Charlie | 3 |
| Delta | 4 |
| Echo | 5 |
| Foxtrot | 6 |
| Gulf | 7 |
| Hotel | 8 |
| India | 9 |
I can get the final result by using a query function as shown in this spreadsheet with a formula referencing the appropriate cells with fileID and range:
=Query({IMPORTRANGE(E2,F2);
IMPORTRANGE(E3,F3);
IMPORTRANGE(E4,F4)},"Select * where Col1 is not null order by Col1",1)
if you want to play with it in your own sheet, you could use this hard-coded function which is the same as above:
=Query({IMPORTRANGE("1WtI56_9mhyArMn_j_H4pZg8E0QdIBaKoJfAr-fDAoE0","'Sheet1'!A:B");
IMPORTRANGE("1HamomAuLtwKJiFEtRKTuEkt--YDTtWChUavetBcAcBA","'Sheet1'!A2:B");
IMPORTRANGE("1WtI56_9mhyArMn_j_H4pZg8E0QdIBaKoJfAr-fDAoE0","'Sheet2'!A2:B")},"Select * where Col1 is not null order by Col1",1)
My Question:
Is there a way to leverage a formula to generate this result based on the number of file ids and ranges in columns E and F? So if a fourth ID and range were added, the desired result in columns a and b would be shown? I suspect Lambda would work, but I am not as strong with it as I should be.
Unsuccessful attempt:
=lambda(someIDs,SomeRanges,IMPORTRANGE(someIds,SomeRanges))(filter(E2:E,E2:E<>""),filter(F2:F,F2:F<>""))
REALLY Bad Attempts:
=contact(Player()*1800-CoffeeBribe*Not(Home))
=company(theMaster(emailed)*(false))<>🐇
All helpful answers will be upvoted if not accepted. Thanks.