Excel Spreadsheet Data Transform

Question

I have data in a table that needs to be transformed into time-series row data.

At the moment my data is in the following columns and format:

GEOID, July, August, September, October, November, December, January, February, March, April, May, June

And in the rows we have the values.

And I need my data in .csv where as the columns are:

GEOID, Month, Value

How can I accomplish this?

Answer 1

You can use the free Microsoft Excel add-In Power Query (from Excel 2010) to unpivot your data and get a list in tabular form.

To make the transformation, follow the description on the MS Website.

1. Import the data in Power Query (Ribbon Power Query -> from Table, cursor must be somewhere in your data)
2. Select the first column (GEOID), on the Ribbon Transform, click on Unpivot Columns > Unpivot Other Columns
3. Close and load to Excel (Ribbon Home)

Answer 2

Is the answer as simple as Save As > select CSV as the file type?

Once saved as a CSV, edit the document in notepad or preferred text editor to check format and make further tweaks to the columns as desired.

I find TextPad to very powerful. Try utilizing the find and replace feature in TextPad to find commas and replace them with returns(new line) \n. Paste the results into Excel and your Month columns will now be in rows.

Answer 3

If understand problem correctly, then the issues is you have 13 columns of data and want to transform it into a 2 column for use with .csv. I will use the name manager as it makes it easier to do, but everything could be done with normal formulas

Step 1> make a new tab called csv or export or other name like fy16 that makes sense. The .csv will only output 1 sheet. Save it twice. Once as a .xlsx to save all the data and formulas, then when ready for csv, select csv tab and then save as csv.

Step 2> (Formula Ribbon -> Name manager) Make 2 names

• Step=13 (Go into name manage create new, name it step, and at bottom enter in =1) This is the number of months
• name Spot for the cell that has GEOID, if this was in sheet1, cell a1 it would be =Sheet1!$A$1

on the csv sheet, enter "GEOID,Month,Value" in A1 to c1

Now just need the equation for each column. This formula uses a offset formula and calculates the row and column

GEOID, I assume you need the same GEOID for each entry and not just the first one Put in A2 and copy down the following formula

• =+OFFSET(Spot,INT((ROW()-1)/step)+1,0)

What this does is start at Spot and moves down a row every 12 times (the use of int and / step) the -1 and +1 is used to get the first cell [A2] correct

Month This will just cycle on the top row Put in B2 qnd copy down the following formula

• =+OFFSET(Spot,0,1+MOD(ROW()-2,step)) Same as before except moving across columns from spot instead of down rows. The Mod with step causes it to cycle

Value will be moving both down rows and across columns. Both the row and the column will be similar to the first two Put in C2 and copy down

• =+OFFSET(Spot,1+INT((ROW()-2)/step),1+MOD(ROW()-2,step))

If you do not want to use the name manager, then use 12 where there is a step in the formula. And use the appropriate Sheet1!$A$1 reference where it is crucial to use both $