I am pretty good with google sheets, using advanced functions. However, this issue has perplexed me for a long time. The current format of a sheet, "Current Sheet" below is a sheet that end-users use to enter figures.

I need, using formulas (not copy-and-paste) to structure that data in another sheet like you see below in the "What I Need" sheet. This data is ultimately going to be read via python script with rows inserted into a database.

The dates are Jan-Dec, and there are over 30 Categories. Every category has a Month value.

Thanks in advance for the assistance!

CodePudding user response：

Try this:

=index({"Month","Item","Value";split(flatten(transpose(eomonth(B1:C1,-1) 1&"❄️"&A4:A14&"❄️"&B4:C14)),"❄️")})

CodePudding user response：

• The following formula returns the data structured as desired:

=ARRAYFORMULA({"Month","Item","Value";SPLIT(FLATTEN(B1:C1&"♣"&A4:A14&"♣"&B4:C14), "♣")})

• The =ARRAYFORMULA allows you to do operations with the ranges, therefore I've added on the outer level
• First, a concatenation of "Months" range "Categories" range values range, separated by any arbitrary delimiter in order to =SPLIT later on.
• Then, =FLATTEN this result to get a single column
• Then the =FLATTEN result is =SPLIT by the delimiter and put into a defined array as second row.
• The first row is manually set with header labels.