Transpose Excel columns while stacking grouped columns

[culper76]

I've looked through quite a few similar postings and articles but so far haven't found anything that really solves the issue (this very well could be due to my inexperience with the formulas and lack of macros). Essentially, I'm trying to transpose a large amount of rows but maintaining groups. I almost had luck with an =Offset fix I saw but was struggling to get the rows to repeat in the groupings. The issue I've had here is it still seems to go to one column and I'd have to redo or manually slice up the overall column. If I transpose the entire data set then I have the columns in the correct order, but would still have to rearrange the columns and rows in a zigzagging manner. Here's an example of my data and where I need to get it:

Original:

<table><tbody><tr><th> </th><th>msa1</th><th>msa2</th><th>msa3</th><th>unem1</th><th>unem2</th><th>unem3</th></tr><tr><td>city1</td><td>1001</td><td>1002</td><td>1003</td><td>6.2</td><td>6.3</td><td>5.5</td></tr><tr><td>city2</td><td>2001</td><td>2002</td><td>2003</td><td>5.7</td><td>5.3</td><td>4.5</td></tr><tr><td>city3</td><td>3001</td><td>3002</td><td>3003</td><td>7.8</td><td>8.3</td><td>7.1</td></tr></tbody></table>

Transposed:

<table><tbody><tr><th> </th><th>city1</th><th>city2</th><th>city3</th></tr><tr><td>msa1</td><td>1001</td><td>2001</td><td>3001</td></tr><tr><td>msa2</td><td>1002</td><td>2002</td><td>3002</td></tr><tr><td>msa3</td><td>1003</td><td>2003</td><td>3003</td></tr><tr><td>unem1</td><td>6.2</td><td>5.7</td><td>7.8</td></tr><tr><td>unem2</td><td>6.3</td><td>5.3</td><td>8.3</td></tr><tr><td>unem3</td><td>5.5</td><td>4.5</td><td>7.1</td></tr></tbody></table>

End product:

<table><tbody><tr><th> </th><th>msa</th><th>unem</th></tr><tr><td>city1</td><td>1001</td><td>6.2</td></tr><tr><td>city1</td><td>1002</td><td>6.3</td></tr><tr><td>city1</td><td>1003</td><td>5.5</td></tr><tr><td>city2</td><td>2001</td><td>5.7</td></tr><tr><td>city2</td><td>2002</td><td>5.3</td></tr><tr><td>city2</td><td>2003</td><td>4.5</td></tr><tr><td>city3</td><td>3001</td><td>7.8</td></tr><tr><td>city3</td><td>3002</td><td>8.3</td></tr><tr><td>city3</td><td>3003</td><td>7.1</td></tr></tbody></table>

Does anyone have any ideas using either the original or transposed data that would get to the end product? Thanks in advance

 

[culper76]

Apologies, I messed up the table postings:

Original Data:

	msa1	msa2	msa3	unem1	unem2	unem3
city1	1001​	1002​	1003​	6.2​	6.3​	5.5​
city2	2001​	2002​	2003​	5.7​	5.3​	4.5​
city3	3001​	3002​	3003​	7.8​	8.3​	7.1​

Transposed Data:

	city1	city2	city3
msa1	1001​	2001​	3001​
msa2	1002​	2002​	3002​
msa3	1003​	2003​	3003​
unem1	6.2​	5.7​	7.8​
unem2	6.3​	5.3​	8.3​
unem3	5.5​	4.5​	7.1​

Desired Result:

	msa	unem
city1	1001​	6.2​
city1	1002​	6.3​
city1	1003​	5.5​
city2	2001​	5.7​
city2	2002​	5.3​
city2	2003​	4.5​
city3	3001​	7.8​
city3	3002​	8.3​
city3	3003​	7.1​

 

[mumps]

Do you want to go from the original data directly to the desired result? Will there always be 3 columns each for msa and unem?

 

[culper76]

Do you want to go from the original data directly to the desired result? Will there always be 3 columns each for msa and unem?

Going from either original or transposed, whichever is easier to manipulate.

Essentially yes, always the same number of columns. In the actual data the final should have 10 columns, each of which will have the same city repeated 17 times stacked by city. As of now, the data set is 30 rows by 170 columns and I'm trying to get it to be what I believe will be 530 rows and 10 columns, but instead of a straight transpose it will transpose row 1, column 1 - 17 to be rows 1 - 17, column 1, then stack row 2, column 1 - 17 starting in row 18 of the new data set, and so on. Column 2 would start with original row 1 column 18 - 34 transposed with the same columns of row 2 - 30 stacked below. The set almost seemed small enough to brute force copy/past transposes but that's proved really slow.

 

[sandy666]

something like this?

city	msa1	msa2	msa3	unem1	unem2	unem3		city	msa	unem
city1	1001	1002	1003	6.2	6.3	5.5		city1	1001	6.2
city2	2001	2002	2003	5.7	5.3	4.5		city1	1001	6.3
city3	3001	3002	3003	7.8	8.3	7.1		city1	1001	5.5
								city1	1002	6.2
								city1	1002	6.3
								city1	1002	5.5
								city1	1003	6.2
								city1	1003	6.3
								city1	1003	5.5
								city2	2001	5.7
								city2	2001	5.3
								city2	2001	4.5
								city2	2002	5.7
								city2	2002	5.3
								city2	2002	4.5
								city2	2003	5.7
								city2	2003	5.3
								city2	2003	4.5
								city3	3001	7.8
								city3	3001	8.3
								city3	3001	7.1
								city3	3002	7.8
								city3	3002	8.3
								city3	3002	7.1
								city3	3003	7.8
								city3	3003	8.3
								city3	3003	7.1

 

[culper76]

something like this?

city	msa1	msa2	msa3	unem1	unem2	unem3		city	msa	unem
city1	1001	1002	1003	6.2	6.3	5.5		city1	1001	6.2
city2	2001	2002	2003	5.7	5.3	4.5		city1	1001	6.3
city3	3001	3002	3003	7.8	8.3	7.1		city1	1001	5.5
								city1	1002	6.2
								city1	1002	6.3
								city1	1002	5.5
								city1	1003	6.2
								city1	1003	6.3
								city1	1003	5.5
								city2	2001	5.7
								city2	2001	5.3
								city2	2001	4.5
								city2	2002	5.7
								city2	2002	5.3
								city2	2002	4.5
								city2	2003	5.7
								city2	2003	5.3
								city2	2003	4.5
								city3	3001	7.8
								city3	3001	8.3
								city3	3001	7.1
								city3	3002	7.8
								city3	3002	8.3
								city3	3002	7.1
								city3	3003	7.8
								city3	3003	8.3
								city3	3003	7.1

More or less, yeah, there would just be 3 rows for each city in the example. msa1/2/3 and unem1/2/3 would be related to years so the data would then be more in a panel form with city 1 repeated for each year and the variables as columns and so on, if that makes sense.

 

[sandy666]

if that makes sense.

maybe if you post representative source data and expected result

 

[mumps]

Change the sheet names (in red) to suit your needs.

Sub TransposeData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, msaCol As Long, unemCol As Long, srcWS As Worksheet, desWS As Worksheet, city As Range
    msaCol = Rows(1).Find("msa", , , , xlByColumns, xlPrevious).Column
    unemCol = Rows(1).Find("unem", , , , xlByColumns, xlPrevious).Column
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each city In srcWS.Range("A2:A" & LastRow)
        With desWS
            .Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(msaCol - 1) = city
            .Cells(Rows.Count, "B").End(xlUp).Offset(1).Resize(msaCol - 1).Value = WorksheetFunction.Transpose(Cells(city.Row, 2).Resize(, msaCol - 1).Value)
            .Cells(Rows.Count, "C").End(xlUp).Offset(1).Resize(unemCol - msaCol).Value = WorksheetFunction.Transpose(Cells(city.Row, msaCol + 1).Resize(, unemCol - msaCol).Value)
        End With
    Next city
    Application.ScreenUpdating = True
End Sub

 

[Norie]

You could do this with Power Query/Get and transform.

1 Enter a header in A1, e.g. City.

2 Select the range of data.

3 On the Data tab select From Table/Range from the Get & Transform section to open the Power Query editor.

4 in the editor select the unem1, unem2 and unem3 columns and on the Transform tab select Unpivot columns.

5 Repeat 4 for the msa1, msa2 and msa3 columns.

6 Delete the 'attribute' columns and rename the 'value' columns.

7 From the File menu select Close & Load to return the transformed data to Excel.

 

[lrobbo314]

I'm guessing that @mumps VBA result is what we're going after. If so, here is a PQ version of mumps' solution.

XXXL
	A	B	C	D	E	F	G	H	I	J	K
1	Column1	msa1	msa2	msa3	unem1	unem2	unem3		city	msa	unem
2	city1	1001	1002	1003	6.2	6.3	5.5		city1	1001	6.2
3	city2	2001	2002	2003	5.7	5.3	4.5		city1	1002	6.3
4	city3	3001	3002	3003	7.8	8.3	7.1		city1	1003	5.5
5									city2	2001	5.7
6									city2	2002	5.3
7									city2	2003	4.5
8									city3	3001	7.8
9									city3	3002	8.3
10									city3	3003	7.1
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Types = Table.TransformColumnTypes(Source,{{"msa1", Int64.Type}, {"msa2", Int64.Type}, {"msa3", Int64.Type}, {"unem1", type number}, {"unem2", type number}, {"unem3", type number}}),
    unem = Table.UnpivotOtherColumns(Types, {"Column1", "unem1", "unem2", "unem3"}, "Attribute", "Value"),
    msa = Table.UnpivotOtherColumns(unem, {"Column1", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    remove = Table.RemoveColumns(msa,{"Attribute", "Attribute.1"}),
    group = Table.Group(remove, {"Column1", "Value"}, {{"table", each _, type table}}),
    AddedIndex = Table.AddIndexColumn(group, "Index", 0, 1),
    InsertedModulo = Table.AddColumn(AddedIndex, "Modulo", each Number.Mod([Index], 3), type number),
    extract = Table.AddColumn(InsertedModulo, "unem", each Table.Column([table],"Value.1"){[Modulo]}),
    ROC = Table.SelectColumns(extract,{"Column1", "Value", "unem"}),
    rename = Table.RenameColumns(ROC,{{"Value", "msa"}, {"Column1", "city"}})
in
    rename