I’m working on a project where I need to change how my data is organized in Google Sheets. Right now, I have a table that looks like this:
Item ID | Value 1 | Date 1 | Value 2 | Date 2 | Value 3 | Date 3
1001 | 10 | Jan 1 | 15 | Feb 1 | 20 | Mar 1
1002 | 5 | Jan 15 | 8 | Feb 15 | |
But I want to turn it into something more like this:
Item ID | Value | Date
1001 | 10 | Jan 1
1001 | 15 | Feb 1
1001 | 20 | Mar 1
1002 | 5 | Jan 15
1002 | 8 | Feb 15
Each item has a different number of value changes. I’ve tried using Query() and Flatten() functions, but I can’t seem to get it right. I’m pretty new to this stuff. Any ideas on how to make this work? Thanks!
You’re dealing with a common data reshaping problem known as ‘unpivoting’ or ‘melting’ data. For Google Sheets, I’d recommend using a combination of ARRAYFORMULA and QUERY functions to achieve this transformation. Here’s a formula that should work:
=ARRAYFORMULA(QUERY(
{A2:A, B2:B, C2:C; A2:A, D2:D, E2:E; A2:A, F2:F, G2:G},
“SELECT Col1, Col2, Col3 WHERE Col2 IS NOT NULL ORDER BY Col1, Col3”
))
Place this in a new sheet, starting at cell A1. It creates a dynamic array that automatically updates as you add or change data in your original sheet. The formula essentially stacks the value-date pairs vertically and then filters out any empty rows. This approach is scalable and doesn’t require manual adjustments as your data grows.
I’ve faced a similar challenge in my work with financial data. Here’s a solution that worked well for me:
Use the TRANSPOSE function along with SPLIT to restructure your data. First, create a helper column that combines all your values and dates, separated by a delimiter. Then use SPLIT to separate them back out.
In a new column, try this formula:
=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(B2&“|”&C2&“|”&D2&“|”&E2&“|”&F2&“|”&G2,“|”)),“|”))
This will give you alternating columns of values and dates. You can then use INDEX to pull out just the values and dates you need, and FILTER to remove any blank rows.
It’s a bit complex at first, but once set up, it’s quite flexible and handles varying numbers of entries per item seamlessly.