This is a follow up question to: How to convert a data matrix into a relational table using Google Sheets formula?

The formula from player() works great.

I'm having problem in doing this for a large data set. I created a sample data set 32 columns and 198 rows. I am getting "Error In ARRAY_LITERAL, an Array Literal was missing values for one or more rows." Here is the link: https://docs.google.com/spreadsheets/d/19176EF9luKQjmmVNxdC3CwF4Qqtwrz-Ux4mtxZujuF0/edit?usp=sharing

Appreciate the help!

CodePudding user response：

The Trim function will not work with more 50000 chars.

Try:

=ARRAYFORMULA({"Country", "Date", "Value"; SORT(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE( IF(Sheet1!B2:Z<>"","♠"&Sheet1!A2:A&"♦"&Sheet1!B1:1&"♦"&Sheet1!B2:Z,)),,999^99)),,999^99), "♠")), "♦"), 2, 1, 1, 1)})