CODEWITHSUNDEEP
google-sheetslambdaimportgoogle-sheets-formulaarray-formulas
pgSystemTester
pgSystemTester

Reputation: 9932

Stacking Multiple Arrays In Query/Lambda Function

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)

enter image description here

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.

Upvotes: 0

Views: 2163

Answers (2)

player0
player0

Reputation: 1

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))))

enter image description here

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)))))

enter image description here

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))))

enter image description here

in old days it would be solved by generating it:

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

enter image description here

Upvotes: 4

TheMaster
TheMaster

Reputation: 50846

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
)

Upvotes: 5

Related Questions