Problem description & analysis

The following is data in CSV file:

A number of (N) records are stored in rows in the CSV file. Row 1 contains Country field values. Row 2 contains names of all the other fields. Row 3 contains values of all the other fields. Now we are trying to reorganize data in the file to make it standard rowwise data. The desired result is as follows:

Solution

Use SPL XLL plug-in of Excel

Write the formula in a blank cell:

=spl("=([[""country"",""goods"",""value""]]|transpose(?).run(if(~(1)==null,~(1)=~[-1](1)))).record().pivot(country;goods,value)",A1:M3)

As shown:

Explanation:

Transpose the data and add missing values to the Country column.

Add field names to generate a table sequence.

Use pivot() function to transpose rows to columns.

Q & A Collection

https://stackoverflow.com/questions/63698177/how-to-split-a-long-row-by-specific-range-and-add-it-as-a-new-rows-excel