VBA to flip horizontal data into multiple rows based on column data

[Nightlytic]

Hi all,

I would really appreciate some help on the macro, if it is possible to do even.

My problem is that I have datasets organised in a lovely, compact horizontal view, and systems that prefer vertical, nasty long extracts and don't want to play with my horizontal data. My goal is to convert the more usable data to those long extracts that specify various criteria combinations.

Because there are many potential combinations and a lot of rows, manually this is near impossible to do. So far closest references I found were https://stackoverflow.com/questions/18883725/in-excel-vba-create-new-rows-based-on-column-data
https://stackoverflow.com/questions/30159713/create-new-excel-rows-based-on-column-data

Still, could not really get them to work ;/

I attached a detailed file here.

 

[Chihiro]

Version of Excel? Best approach to this will depend on it.

If you have PowerQuery/Get & Transform, that's the easiest solution.

Otherwise, you'll need either VBA or use PivotTable wizard to consolidate range and flatten it out.
EDIT: You can also do it with formula, but may cause more of overhead.

FYI - In most data analysis scenario, it is far better to start out with flat structure like your desired output rather than cross tab structure you have. Flat table can easily be summarized using Pivot Table etc.

 

[Nightlytic]

Version of Excel? Best approach to this will depend on it.

If you have PowerQuery/Get & Transform, that's the easiest solution.

Otherwise, you'll need either VBA or use PivotTable wizard to consolidate range and flatten it out.
EDIT: You can also do it with formula, but may cause more of overhead.

FYI - In most data analysis scenario, it is far better to start out with flat structure like your desired output rather than cross tab structure you have. Flat table can easily be summarized using Pivot Table etc.

Hi Chihiro,

Ah yes, probably useful to provide that, I'm on excel 2010, no power query and can't install it.
Have powerpivot and power bi. I don't think I ever took advantage of a pivot wizard. Is that standard?

 

[p45cal]

I go along with Chihiro's PowerQuery solution being the easiest.

If you want to use VBA instead then the attached has a button which will do it placing the new data at A32 and below.

Note that I've changed the headers at E2:G2 to actual Excel dates, being the first of the month, and gave it a year. This makes the final data more useful, in addition to handling different years correctly. (For appearance's sake I formatted these to show just month and year.)

Essentially, the macro takes each of the cells in the area I've shaded light grey and makes a row of data from each cell using the cells in the column and row headers of that table.

The code is:

Sub blah()
Range("A32:F32").Value = Array("Reference", "Company", "Industry", "Country", "Period", "Turnover")  'place the headers.
Set Destn = Range("A33")  'the first cell where data will be placed.
For Each cll In Range("E3:G8")  'this is just the data/values part of your table
  Destn.Resize(, 4).Value = Cells(cll.Row, "A").Resize(, 4).Value
  Destn.Offset(, 4).Value = Cells(2, cll.Column).Value
  Destn.Offset(, 4).NumberFormat = "mmmm yyyy" 'formats the dates, but they're still full Excel dates.
  Destn.Offset(, 5).Value = cll.Value
  Set Destn = Destn.Offset(1)  'move the destination cell 1 cell down.
Next cll
End Sub

 

[Chihiro]

pivot wizard is bit hidden, most users won't ever see it.
See link for detail on how to use it.
https://www.launchexcel.com/pivot-table-flatten-crosstab/

 

[r2c2]

@Nightlytic

you can use normal pivot tables to transform this data quite easily. Follow below steps.

1. Select your data and insert pivot
2. Add Reference, Company, Industry, Country to row labels area
3. Add Turnover months to values area.
4. Go to PivotTable Tools > Design ribbon and set report layout as "Tabular" and enable "Repeat row labels" option.
5. Turn off grand totals and sub totals.
6. Now, using the pivot table field list, drag "Ʃ values" from column label to row label area.
7. You will get desired output. Disable +/- buttons from Options.
8. When a new month appears, just add that month data to values and report will be updated.

See attached workbook.

 

[Nightlytic]

I go along with Chihiro's PowerQuery solution being the easiest.

If you want to use VBA instead then the attached has a button which will do it placing the new data at A32 and below.

Note that I've changed the headers at E2:G2 to actual Excel dates, being the first of the month, and gave it a year. This makes the final data more useful, in addition to handling different years correctly. (For appearance's sake I formatted these to show just month and year.)

Essentially, the macro takes each of the cells in the area I've shaded light grey and makes a row of data from each cell using the cells in the column and row headers of that table.

The code is:

Sub blah()
Range("A32:F32").Value = Array("Reference", "Company", "Industry", "Country", "Period", "Turnover")  'place the headers.
Set Destn = Range("A33")  'the first cell where data will be placed.
For Each cll In Range("E3:G8")  'this is just the data/values part of your table
  Destn.Resize(, 4).Value = Cells(cll.Row, "A").Resize(, 4).Value
  Destn.Offset(, 4).Value = Cells(2, cll.Column).Value
  Destn.Offset(, 4).NumberFormat = "mmmm yyyy" 'formats the dates, but they're still full Excel dates.
  Destn.Offset(, 5).Value = cll.Value
  Set Destn = Destn.Offset(1)  'move the destination cell 1 cell down.
Next cll
End Sub

That works perfect, thank you!

pivot wizard is bit hidden, most users won't ever see it.
See link for detail on how to use it.
https://www.launchexcel.com/pivot-table-flatten-crosstab/

Will look into it, thank you Chihiro, worked off 2010 too much completely missed this hidden gem

@Nightlytic

you can use normal pivot tables to transform this data quite easily. Follow below steps.

1. Select your data and insert pivot
2. Add Reference, Company, Industry, Country to row labels area
3. Add Turnover months to values area.
4. Go to PivotTable Tools > Design ribbon and set report layout as "Tabular" and enable "Repeat row labels" option.
5. Turn off grand totals and sub totals.
6. Now, using the pivot table field list, drag "Ʃ values" from column label to row label area.
7. You will get desired output. Disable +/- buttons from Options.
8. When a new month appears, just add that month data to values and report will be updated.

See attached workbook.

That's very clever, might be a good way to do it for transparency, appreciate it r2c2

Thanks everyone, I did not anticipate this level of support to my question