How to combine multiple query formula results with the same number of columns vertically? [duplicate]

Question

I have a spreadsheet with 31 Day level sheets and a few summary sheets being used for a daily sales report, using which I'm trying to create a legder. For this, I want columns A, B and C from each of the sheets named Day 1, Day 2, Day 3 .... Day 31 to be arranged vertically, if the column A matches with any of the values from column E in the image shown (they are the customers).

This formula pulls value correctly from a single sheet - Day 1 here.

=query('Day 1'!A:C,"select A, B, C where A matches '"& textjoin("|",1,E2:E) &"'",0)

I want to stack the results from this formula for all 31 Days vertically, and group them by name, which is column A. I tried using this formula below to see if this can work with 3 sheets, but it throws a value error: "Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: A"

=query({'Day 1'!A1:C237;'Day 2'!A1:C237;'Day 3'!A1:C237},"select * where A matches '"& textjoin("|",1,E2:E) &"'",0)

Can anyone help me resolve this, please?

rockinfreakshow · Accepted Answer · 2024-05-27 19:05:36Z

0

Need to use Col1 in place of A since the stacked input array is not an actual range. B becomes Col2 and C is Col3 and so on...

=query({'Day 1'!A1:C237;'Day 2'!A1:C237;'Day 3'!A1:C237},"select * where Col1 matches '"& textjoin("|",1,E2:E) &"' order by Col1",0)

answered May 27, 2024 at 19:05

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

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

Comments

player0 · Accepted Answer · 2024-05-27 21:30:53Z

you may try alternative:

=LET(r, {'Day 1'!A1:C237; 'Day 2'!A1:C237; 'Day 3'!A1:C237}, 
 FILTER(r, REGEXMATCH(INDEX(r,,1), TEXTJOIN("|", 1, E2:E))))

full formula:

=LET(r, {'Day 1'!A1:C237; 'Day 2'!A1:C237; 'Day 3'!A1:C237; 'Day 4'!A1:C237; 'Day 5'!A1:C237; 'Day 6'!A1:C237; 'Day 7'!A1:C237; 'Day 8'!A1:C237; 'Day 9'!A1:C237; 'Day 10'!A1:C237; 'Day 11'!A1:C237; 'Day 12'!A1:C237; 'Day 13'!A1:C237; 'Day 14'!A1:C237; 'Day 15'!A1:C237; 'Day 16'!A1:C237; 'Day 17'!A1:C237; 'Day 18'!A1:C237; 'Day 19'!A1:C237; 'Day 20'!A1:C237; 'Day 21'!A1:C237; 'Day 22'!A1:C237; 'Day 23'!A1:C237; 'Day 24'!A1:C237; 'Day 25'!A1:C237; 'Day 26'!A1:C237; 'Day 27'!A1:C237; 'Day 28'!A1:C237; 'Day 29'!A1:C237; 'Day 30'!A1:C237; 'Day 31'!A1:C237}, 
 FILTER(r, REGEXMATCH(INDEX(r,,1), TEXTJOIN("|", 1, E2:E))))

if sheet does not exist but will in future use IFERROR:

=LET(r, {'Day 1'!A1:C237; 'Day 2'!A1:C237; IFERROR('Day 3'!A1:C237, {"","",""})}, 
 FILTER(r, REGEXMATCH(INDEX(r,,1), TEXTJOIN("|", 1, E2:E))))

ultimate formula to rule them all

smarter alternative would be to use REDUCE to dynamically get all sheets:

=INDEX(LET(a, IFERROR(SEQUENCE(1, 3)/0), b, 
 REDUCE(a, SEQUENCE(31), LAMBDA(x, y, {x; IFERROR(INDIRECT("Day "&y&"!A1:C237"), a)})), 
 FILTER(b, REGEXMATCH(INDEX(b,,1), TEXTJOIN("|", 1, E2:E)))))

IFERROR(SEQUENCE(1, 3)/0) - creates a blank row with 3 columns (bacause A:C is 3 columns) for IFERROR handling management
SEQUENCE(31) - for 31 days/sheets starting from 1 to 31
works even with sheets that are not created yet (for example if sheet named Day 31 does not exist, it will still get you all other sheets that exist)

note: avoid QUERY if possible. see why here under "side notes for QUERY rookies" paragraph

Collectives™ on Stack Overflow

How to combine multiple query formula results with the same number of columns vertically? [duplicate]

2 Answers 2

Comments

ultimate formula to rule them all

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

ultimate formula to rule them all

Comments

Linked

Related