0

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.

player0
  • 124,011
  • 12
  • 67
  • 124
pgSystemTester
  • 8,979
  • 2
  • 23
  • 49

2 Answers2

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

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • Without a doubt, you've participated in those competitions. `Reduce` is just flowing. – Osm Nov 01 '22 at 18:40
4

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
)
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • 1
    Does this have a name `E2:INDEX...`? @Themaster – Osm Nov 01 '22 at 18:59
  • 2
    @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 Nov 01 '22 at 19:00
  • @Osm [Reference](https://support.microsoft.com/en-us/office/calculation-operators-and-precedence-in-excel-48be406d-4975-4d31-b2b8-7af9e0e2878a) – TheMaster Nov 01 '22 at 19:25
  • Great answer. I need to learn more about reduce (as I said earlier). I gave player() the check since she posted first. Thanks again... – pgSystemTester Nov 01 '22 at 20:56