Stacking Multiple Arrays In Query/Lambda Function

Question

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.

Can I answer with Im here for the up-v*te? @pgSystemTester :D — Osm
– Osm, Commented Nov 1, 2022 at 19:05
I think that would be stretching the definition of helpful answers =) — pgSystemTester
– pgSystemTester, Commented Nov 1, 2022 at 21:22

TheMaster · Accepted Answer · 2022-11-01 18:56:15Z

5

REDUCE accepts and returns arrays. We can use it to stack ranges. INDEX/COUNTA can be used to get the range needed without blanks. OFFSET can be used to get the next column's value.

=QUERY(
  REDUCE(
    {"Id","Amount"},
    E2:INDEX(E2:E,COUNTA(E2:E)),
    LAMBDA(
      a,e,
      {a;IMPORTRANGE(e,OFFSET(e,0,1))}
    )
  ),
  "Select * where Col1 is not null order by Col1",
  1
)

answered Nov 1, 2022 at 18:56

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Osm Over a year ago

Does this have a name E2:INDEX...? @Themaster

TheMaster Over a year ago

@Osm : is range operator(a operator like +). It doesn't have to be INDEX. You can join any valid range from any range returning formulas(eg:OFFSET,INDIRECT,etc).

TheMaster Over a year ago

@Osm Reference

pgSystemTester Over a year ago

Great answer. I need to learn more about reduce (as I said earlier). I gave player() the check since she posted first. Thanks again...

player0 · Accepted Answer · 2022-11-01 19:24:58Z

4

if ranges would be the same:

=LAMBDA(x, QUERY(REDUCE({"ID", "Amount"}, x, 
 LAMBDA(a, c, {a; IMPORTRANGE(c, "Sheet1!A2:B")})), 
 "where Col1 is not null", 1))
 (E2:INDEX(E:E, MAX((E:E<>"")*ROW(E:E))))

if ranges are not the same:

=INDEX(LAMBDA(x, y, QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(MAP(x, y, 
 LAMBDA(e, f, QUERY("♣"&FLATTEN(QUERY("♥"&TRANSPOSE(
 IMPORTRANGE(e, f)),,9^9)),,9^9))),,9^9), 
 "♣")), "♥"), "where Col1 <> ' ' order by Col2", 1))(
 E2:INDEX(E:E, MAX((E:E<>"")*ROW(E:E))), 
 F2:INDEX(F:F, MAX((F:F<>"")*ROW(F:F)))))

or:

=LAMBDA(x, QUERY(REDUCE({"ID", "Amount"}, x, 
 LAMBDA(a, b, {a; IMPORTRANGE(b, OFFSET(b,,1))})), 
 "where Col2 is not null", 1))
 (E2:INDEX(E:E, MAX((E:E<>"")*ROW(E:E))))

in old days it would be solved by generating it:

={""; INDEX("={"&TEXTJOIN("; ", 1, "IMPORTRANGE("""&
 FILTER(E2:E, E2:E<>"")&""", """&FILTER(F2:F, F2:F<>"")&""")")&"}")}

edited Nov 1, 2022 at 19:24

answered Nov 1, 2022 at 18:30

player0

131k14 gold badges91 silver badges149 bronze badges

1 Comment

Osm Over a year ago

Without a doubt, you've participated in those competitions. Reduce is just flowing.

Collectives™ on Stack Overflow

Stacking Multiple Arrays In Query/Lambda Function

Sample Starting Data From Three Sources

Desired final result:

My Question:

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Sample Starting Data From Three Sources

Desired final result:

My Question:

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related