Error trying to unpivot columns

Spreadsheets Google Sheets
1

I’m a total spreadsheet nerd, so I had my personal finance spreadsheet hooked into tiller’s data sources pretty quickly, but thought I could do some tweaks to make it more universally useful and simple to set up.

My sheet is similar to other short term forecasting methods, but I’m trying to reduce the manual entries.

I’m struggling a bit on the error handling end.

I’ve got a cascade of queries and calculations, don’t want to overdo the details but:

  • Items Query
    • Gets items from the past year from Transactions
    • Has a formula to strip out some of the random junk to make two similar entries have a repeatable (but less readable) key.
    • Has a vlookup for the account type from Accounts sheet.
  • Repeats Query
    • Groups the items and determines an interval

That part all works fine. The SWE in me wants to write a macro for the iterative bit, but the coding standard around here seems to be to do it all in the sheet itself. So I have 5 columns generated by

=ARRAYFORMULA(if(ISNUMBER($Y6:$Y), $W6:$W+$Y6:$Y*Z5:AD5,""))

Sample Output:

avg min max count Days Interval 1 2 3 4 5
spectrumspectrum–ppd Checking CHECKING $ (59.99) 4/10/2026 6/10/2026 3 30.50 7/10/2026 8/10/2026 9/9/2026 10/10/2026 11/9/2026
afvpayendingin Discover More Card CREDIT $ (3.34) 6/2/2026 6/9/2026 5 1.75 6/10/2026 6/12/2026 6/14/2026 6/16/2026 6/17/2026

This is resilient to additional columns being inserted/deleted between the first and last column, Arrayformula is perfectly happy to cross those two results.

The next bit is tricky and I hope there’s a better way than what I’m doing.

I dynamically generate the queries to be run, this list auto expands/shrinks if the number of columns changes:

Queries
Select R, T, S, U, Z WHERE Z < date ‘2026-07-14’ AND X > 2
Select R, T, S, U, AA WHERE AA < date ‘2026-07-14’ AND X > 2
Select R, T, S, U, AB WHERE AB < date ‘2026-07-14’ AND X > 2
Select R, T, S, U, AC WHERE AC < date ‘2026-07-14’ AND X > 2
Select R, T, S, U, AD WHERE AD < date ‘2026-07-14’ AND X > 2

Now I should be on the home stretch, this works great:
=Sort({Query(R6:AC525, AJ4,1);QUERY(R6:AC525, AJ5,1)}, 2, true,3, true, 5, true)

Sample Output:

Description Account Type Account Amount Due
spectrumspectrum–ppd CHECKING Checking $ (59.99) 7/10/2026
afvpayendingin CREDIT Discover More Card $ (3.34) 6/10/2026
afvpayendingin CREDIT Discover More Card $ (3.34) 6/12/2026

But:

  • I have to manually add all the queries, it’s not resilient to the number of repeats being changed.
  • If I include enough repeat date columns to ensure we always have all the repeats, sometimes some of the queries are empty results, and the whole thing breaks.
    • I’ve tried using iferror and I keep getting different sorts of problems with arrays not matching.

No additional error handling, non-query row included: Error
In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

Added is blank query error handling:

=Sort({Query(R6:AC525, AJ4,1);if(isBlank(AJ10),{"","","","",""}, QUERY(R6:AC525, AJ10,1))}, 2, true,3, true, 5, true)

In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

Same result with:

=Sort({Query(R6:AC525, AJ4,1);IFERROR(QUERY(R6:AC525, AJ10,1),{"","","","",""})}, 2, true,3, true, 5, true)

I could live with a fixed set of repeat columns, it’s more intended to catch the cable bill and the paychecks than the daily vending machine habit. I’ll have to add on to this later for annual items you might want to manually enter. But the bit about blank rows not working is really bugging me.

I shouldn’t be surprised if there’s not an easy solution for this, even SQL makes you manually list the unpivoted columns.

2

Though I’m not certain if I want to use AI for my financial planning, I have used it with success for coding, perhaps it can help you in this reguard. If you use Chrome, try the ‘Ask Gemini’ button in the upper right corner (I would guess Copilot could work as well in Edge, the point is the agent needs to be able to see your sheet and read the formulas). Click on a cell with a formula, explain what you’re trying to do, or what error you’re seeing, and it can often suggest things to try. It’s far from perfect, and often doesn’t get it on the first try, but with a little back and forth I’ve been able to work out a number of issues.

3

I’ve already asked chatpt. It produced a varient of this split/flatten strategy, but it errored out.

But let’s see if in the morning light I can understand better what it’s doing.

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(C1:G1&"×"&A2:A10&"×"&B2:B10&"×"&C2:G10), "×"), 
 "select Col1,Col2,Col3,Col4,'VAC' where Col4 is not null label 'VAC'''", ))

ArrayFormula we know, query we know. Split seems to work like other languages, spliting a stiring on the provided delimiter, and spreading the parts out in a row.

Flatten takes a range of cells and makes a vertical list.

Example:

=ARRAYFORMULA(flatten(AC6:AE8))

Produces a vertical list of the nine cell contents.

Ok… so I think the problem is that the example didn’t have multiple header columns, and it led ChatGPT astray. I don’t want most of my flattened items to be processed by ArrayFormula…

Pretty sure query was working against me there, this is proof of concept, but I need to flesh out, and make sure it doesn’t break when I add the query.

=ARRAYFORMULA(Split(R6&"|"&S6&"|"&flatten(AC6:AE8),"|"))
4

That’s not right. I unpivoted the block inside flatten but it didn’t merge with the rest of the rows.

5

New candidate for expansion:

=ARRAYFORMULA(Split(flatten(R6:R8&"|"&AC6:AE8),"|"))

This gets me 3 repeats of 3 source rows.

The problem I was having before was having open ended arrays (AC6:AE) makes the array sizes mismatch.

6

I think I have all the parts now:

=ARRAYFORMULA(Query(Split(flatten(R6:R200 & “|” & S6:S200 &“|”& T6:T200 &“|”& U6:U200 &“|”& X6:X200&“|”& AC6:AE200*1),“|”),“select Col1, Col2,Col3, Col4, Col6 where Col5>2 and Col6 is not null and Col6 < “& AG2 &” order by Col3, Col2, Col6”))

  • The ranges must be fixed size, open ended ranges don’t mix with arrays of different dimensions.
  • The split/flatten turns the date into a string. The *1 gets us the numeric value of the date, so I can compare that to a normal numeric date cell value instead of doing the date ‘Text(AG2)’ construction.