Column Header To Rows (1 Viewer)

[Charles2019]

I have data that is in Format A and need the data to be like Format B. There are 400 columns in Format A and 19 rows. How can I get the data into Format B?

Format A: This is the format that the data is in

HRS-Billing	MLTC - 14200	Indianola
3881229	18211	\+15551056305
28393	\+15551018287	\+15551056303
27961	\+15551018214	\+15551056304
28375	18212	\+15551056307
28311	\+15551018208	\+15551056301
	\+15551018205	\+15551056308
	18215	\+15551056306
	\+15551018230	\+15551056302
	\+15551018213
	\+15551018210

Format B: Need it in this format

Group Name	Number
HRS-Billing	3881229
HRS-Billing	28393
HRS-Billing	27961
HRS-Billing	28375
HRS-Billing	28311
MLTC - 14200	18211
MLTC - 14200	\+15551018287
MLTC - 14200	\+15551018214
MLTC - 14200	18212
MLTC - 14200	\+15551018208
MLTC - 14200	\+15551018205
MLTC - 14200	18215
MLTC - 14200	\+15551018230
MLTC - 14200	\+15551018213
MLTC - 14200	\+15551018210
Indianola	\+15551056305
Indianola	\+15551056303
Indianola	\+15551056304
Indianola	\+15551056307
Indianola	\+15551056301
Indianola	\+15551056308
Indianola	\+15551056306
Indianola	\+15551056302

 

[Gasman]

Google Excel Transpose and see if that will do it for you.

 

[Charles2019]

I tried using the Transpose feature in Excel. It moves the header row to a column, but the numbers are in a single row instead of a column.

 

[Gasman]

Is this a one off process or continuous process, as all I can think of is some automation if the latter case.

 

[Pat Hartman]

If you want to make the reformat permanent, create three append queries. Each append query selects only a single column with non null values.

If you want to do this on the fly, use three select queries inside a Union query.

Alternatively, you can use the Union query inside an append query to perform the permanent reformat.

If you have more than three columns, you need a separate append query for each column. In this case, you need to decide if you want to create x append queries or if you want to write a VBA code loop to do the append for each non null column. It is a trade off and your decision will depend on.
1. whether this is a once only or a perpetual task
2. how many columns and therefore how many queries you need
3. how good you are with VBA

 

[Gasman]

400 columns Pat
If a One off, I would just do it manually.

 

[Charles2019]

If you want to make the reformat permanent, create three append queries. Each append query selects only a single column with non null values.

If you want to do this on the fly, use three select queries inside a Union query.

Alternatively, you can use the Union query inside an append query to perform the permanent reformat.

If you have more than three columns, you need a separate append query for each column. In this case, you need to decide if you want to create x append queries or if you want to write a VBA code loop to do the append for each non null column. It is a trade off and your decision will depend on.
1. whether this is a once only or a perpetual task
2. how many columns and therefore how many queries you need
3. how good you are with VBA

With the number of columns (400+) the multiple queries is going to get messy. This will be something that I'll need to create over the next 6 months. I have minimal experience with VBA, but will have to look into it.

 

[Gasman]

Always break it down into small steps.
is the volume of data going to increase/decrease or just change values? If the lattter you could record a macro for a few steps and then repeat those steps.

Otherwise.
Calculate how many columns. That will be your loop limit.
Insert two columns at the start. This will be your output area.
Calculate the starting row, which at the start will be 1. I will call this iLastDestRow
Starting from column 3 row 1, grab that cell value and place in the starting row in column A
Find the last row of data in your source column (currently C) and put into a variable I will call iLastSourceRow
Then copy that range from C2:C&ilastSourceRow to ilastDestRow to column B at iLastDestRow+1
Now recalculate the next free row in column A and that becomes iLastDestRow
Repeat for each column until you have looped as many times as there are columns.

Break it all down into small steps.

 

[Charles2019]

Always break it down into small steps.
is the volume of data going to increase/decrease or just change values? If the lattter you could record a macro for a few steps and then repeat those steps.

Otherwise.
Calculate how many columns. That will be your loop limit.
Insert two columns at the start. This will be your output area.
Calculate the starting row, which at the start will be 1. I will call this iLastDestRow
Starting from column 3 row 1, grab that cell value and place in the starting row in column A
Find the last row of data in your source column (currently C) and put into a variable I will call iLastSourceRow
Then copy that range from C2:C&ilastRow to ilastDestRow to column B at iLastDestRow
Now recalculate the next free row in column A and that becomes iLastDestRow
Repeat for each column until you have looped as many times as there are columns.

Break it all down into small steps.

The data might increase, but I don't expect it to, but do expect some minor value changes.

 

[June7]

So these columns are not related? I.e., each row is not a record?

1. copy/paste columns and fill down the Group Name with each paste - 400 columns might take a couple hours
2. maybe Power Query unpivot - I don't know if there is a limit on number of columns but I haven't been able to figure this out at all yet as unpivot option is not showing for me, maybe need a version later than Excel 2010
3. VBA - getting code right might take as long as copy/paste

Option 1 is certainly not appealing if this is a recurring task and VBA is great for that.

 

[Gasman]

The data might increase, but I don't expect it to, but do expect some minor value changes.

If just the values change then the hardcoded ranges from a few macro recorder steps will be enough?
If the number of rows/or columns change in any way, then VBA is the only way I would know how to do it, as I explained earlier.

 

[June7]

See if this gets you started. Assumes there are no blank cells in each column until end and no blank columns between.

Option Explicit

Sub Unpivot()
Dim c As Integer, r As Integer, x As Integer, y As Integer
Dim xlW1 As Worksheet, xlW2 As Worksheet
Set xlW1 = ThisWorkbook.Worksheets("Sheet1")
Set xlW2 = ThisWorkbook.Worksheets("Sheet2")
With xlW1
x = .Cells(1, Columns.Count).End(xlToLeft).Column
r = 2
For c = 1 To x
    y = .Cells(1, c).End(xlDown).Row
    .Range(.Cells(2, c), .Cells(2 + y - 2, c)).Copy Destination:=xlW2.Range(xlW2.Cells(r, 2), xlW2.Cells(r + y - 1, 2))
    xlW2.Range("A" & r & ":" & "A" & r + y - 2).Value = .Cells(1, c).Value
    r = r + y - 1
Next
End With
End Sub

 

[Pat Hartman]

With the number of columns (400+)

Then the VBA solution is your best option. It really isn't a lot of code. I'm guessing, less than 20 lines and you don't need to actually save the queries. You just build them on the fly, run them and build the next one. I don't work in Excel or I would take a stab at it for you.

 

[June7]

Stabbed in post 12.

Went with Range Copy instead of building queries. But think I'll look at that now as well. CopyFromRecordset method will be handy for this.
Here is a recordset approach. I didn't see any performance difference with small data sample. One advantage is blank cells in column should be ignored as long as they don't actually contain empty string or other non-printing characters.

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Dim cn As Object, rs As Object
Dim c As Integer, r As Integer, x As Integer, xlW As Worksheet

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & ThisWorkbook.FullName & ";" & _
    "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text"""
Set xlW = ThisWorkbook.Worksheets("Sheet1")
With xlW
x = .Cells(1, Columns.Count).End(xlToLeft).Column
r = 2
For c = 1 To x
    rs.Open "SELECT '" & .Cells(1, c).Value & "' AS F1,[" & .Cells(1, c).Value & "] " & _
            "FROM [Sheet1$] WHERE NOT [" & .Cells(1, c).Value & "] IS NULL", cn, adOpenStatic, adLockOptimistic, adCmdText
    Worksheets("Sheet2").Range("A" & r).CopyFromRecordset rs
    r = r + rs.RecordCount
    rs.Close
Next
End With

 

[Pat Hartman]

Stabbed in post 12.

Apparently, I had left my computer window and answered the question without refreshing and so I didn't see yours. I was right about how short the code would be though

With 400 columns, I wouldn't for a second consider building and saving queries. However, was thinking "Access" which is why I said query at all. Obviously, you don't use queries in Excel, you just add rows to s sheet. And that is why i didn't even make an effort to build the code.

 

[arnelgp]

here is another sample.
click the Transpose button and the data will be transposed to Sheet2.

 

[Gasman]

Then the VBA solution is your best option. It really isn't a lot of code. I'm guessing, less than 20 lines and you don't need to actually save the queries. You just build them on the fly, run them and build the next one. I don't work in Excel or I would take a stab at it for you.

That was my thinking this morning as well.
Only I was thinking of linking to the excel sheet, then getting the fields count, then selecting Top1 as the first field and the rest where not = F1 and the field number field is not null. Then export back to excel.

Very much like @June7's example.

 

[Charles2019]

Stabbed in post 12.

Went with Range Copy instead of building queries. But think I'll look at that now as well. CopyFromRecordset method will be handy for this.
Here is a recordset approach. I didn't see any performance difference with small data sample. One advantage is blank cells in column should be ignored as long as they don't actually contain empty string or other non-printing characters.

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Dim cn As Object, rs As Object
Dim c As Integer, r As Integer, x As Integer, xlW As Worksheet

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & ThisWorkbook.FullName & ";" & _
    "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text"""
Set xlW = ThisWorkbook.Worksheets("Sheet1")
With xlW
x = .Cells(1, Columns.Count).End(xlToLeft).Column
r = 2
For c = 1 To x
    rs.Open "SELECT '" & .Cells(1, c).Value & "' AS F1,[" & .Cells(1, c).Value & "] " & _
            "FROM [Sheet1$] WHERE NOT [" & .Cells(1, c).Value & "] IS NULL", cn, adOpenStatic, adLockOptimistic, adCmdText
    Worksheets("Sheet2").Range("A" & r).CopyFromRecordset rs
    r = r + rs.RecordCount
    rs.Close
Next
End With

Thanks for the VBA Solution. This got me what I needed. Thanks again.