Converting data into rows and columns

[Georgine]

I am trying to figure out an efficient way to convert data into Excel. The data is not in a row/column format. The format that I received the data in is as follows (2 columns; column labels repeating for each name in column A, and the corresponding data in column B. See below. Any ideas on how to best to convert this data into column labels across the top and data in rows under each column heading? Any tips would be appreciated!
Column A Column B
Name John Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name John
Last Name Doe
BLANK ROW
Name Jane Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name Jane
Last Name Doe

 

[London_Calling]

Mr VoG,

I'm enjoying trying to understand your code. Could you help me with this:

   Cells(j, k).Value = .Offset(, 1).Value

In my understanding of offset, there's a value missing ?

I'm also trying to understand "k" but I might get there . . . eventually.

Thank you for the lesson!

 

[VoG]

Before

<b>Sheet1 (3)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:74px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">B</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td >Name</td><td >E</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td >Address</td><td >G</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td >City</td><td >B</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td >State</td><td >D</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td >Zip</td><td >A</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td >First Name</td><td >Z</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td >Last Name</td><td >E</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >8</td><td >Country</td><td >V</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >9</td><td >Continent</td><td >C</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >10</td><td >Division</td><td >N</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >12</td><td >Name</td><td >E</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >13</td><td >Address</td><td >I</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >14</td><td >City</td><td >H</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >15</td><td >State</td><td >F</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >16</td><td >Zip</td><td >K</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >17</td><td >First Name</td><td >M</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >18</td><td >Last Name</td><td >K</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >19</td><td >Country</td><td >L</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >20</td><td >Continent</td><td >C</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >21</td><td >Division</td><td >N</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >22</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >23</td><td >Name</td><td >N</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >24</td><td >Address</td><td >T</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >25</td><td >City</td><td >K</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >26</td><td >State</td><td >E</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >27</td><td >Zip</td><td >C</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >28</td><td >First Name</td><td >Z</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >29</td><td >Last Name</td><td >A</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >30</td><td >Country</td><td >X</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >31</td><td >Continent</td><td >G</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >32</td><td >Division</td><td >O</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

After

<b>Sheet1 (3)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">D</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">F</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">G</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td >Name</td><td >Address</td><td >City</td><td >State</td><td >Zip</td><td >First Name</td><td >Last Name</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td >E</td><td >G</td><td >B</td><td >D</td><td >A</td><td >Z</td><td >E</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td >E</td><td >I</td><td >H</td><td >F</td><td >K</td><td >M</td><td >K</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td >N</td><td >T</td><td >K</td><td >E</td><td >C</td><td >Z</td><td >A</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

Revised code

Sub SortData()
Dim LR As Long, i As Long, j As Long, k As Long
Application.ScreenUpdating = False
Range("C1:I1").Value = Array("Name", "Address", "City", "State", "Zip", "First Name", "Last Name")
LR = Range("A" & Rows.Count).End(xlUp).Row
j = 2
For i = 1 To LR
    With Range("A" & i)
        If .Value = "" Then
            j = j + 1
        Else
            Select Case .Value
                Case "Name": k = 3
                Case "Address": k = 4
                Case "City": k = 5
                Case "State": k = 6
                Case "Zip": k = 7
                Case "First Name": k = 8
                Case "Last Name": k = 9
                Case Else: k = 0
            End Select
            If k <> 0 Then Cells(j, k).Value = .Offset(, 1).Value
        End If
    End With
Next i
Columns("A:B").Delete
Application.ScreenUpdating = True
End Sub

 

[VoG]

Mr VoG,

I'm enjoying trying to understand your code. Could you help me with this:

   Cells(j, k).Value = .Offset(, 1).Value

In my understanding of offset, there's a value missing ?

I'm also trying to understand "k" but I might get there . . . eventually.

Thank you for the lesson!

j is the row number, k is the column number and .Offset(, 1).Value refers to the value one column to the right of Range("A" & i), i.e. Range("B" & i).

 

[pto160]

Vog,
I am only picking up row 23 to row 29 and not the other rows. I have Excel 2003. Do I need to turn on a setting?

 

[VoG]

Vog,
I am only picking up row 23 to row 29 and not the other rows. I have Excel 2003. Do I need to turn on a setting?

You need blank cells to separate the groups of data. See my example.

 

[Georgine]

Wow! This works perfectly! I love it! I can use this for a number of conversions I have where the data is formatted this way. This will solve all my problems if I can edit the program. If I want to add columns and/or rename the columns would I be able to edit your program to make those revisions and use it for another set of data? Is there a way the program could take the text from column A thus making the program work for any number of columns until it hits the blank line that separates each group?

Thank you SO much for your help.

 

[pto160]

Vog,
<title>Excel Jeanie HTML</title>Sheet1

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 71px;"> <col style="width: 18px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>Name</td> <td>E</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>Address</td> <td>G</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>City</td> <td>B</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>State</td> <td>D</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>Zip</td> <td>A</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>First Name</td> <td>Z</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td>Last Name</td> <td>E</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td>Country</td> <td>V</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td>Continent</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td>Division</td> <td>N</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td>Name</td> <td>E</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td>Address</td> <td>I</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td>City</td> <td>H</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td>State</td> <td>F</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td>Zip</td> <td>K</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">18</td> <td>First Name</td> <td>M</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">19</td> <td>Last Name</td> <td>K</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">20</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">21</td> <td>Country</td> <td>L</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">22</td> <td>Continent</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">23</td> <td>Division</td> <td>N</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">24</td> <td></td> <td></td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">25</td> <td>Name</td> <td>N</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">26</td> <td>Address</td> <td>T</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">27</td> <td>City</td> <td>K</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">28</td> <td>State</td> <td>E</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">29</td> <td>Zip</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">30</td> <td>First Name</td> <td>Z</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">31</td> <td>Last Name</td> <td>A</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">32</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">33</td> <td>Country</td> <td>X</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">34</td> <td>Continent</td> <td>G</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">35</td> <td>Division</td> <td>O</td></tr></tbody></table>
Yes, using this format returns the correct values. Thank you.
Excel tables to the web >> Excel Jeanie HTML 4

 

[VoG]

Wow! This works perfectly! I love it! I can use this for a number of conversions I have where the data is formatted this way. This will solve all my problems if I can edit the program. If I want to add columns and/or rename the columns would I be able to edit your program to make those revisions and use it for another set of data? Is there a way the program could take the text from column A thus making the program work for any number of columns until it hits the blank line that separates each group?

Thank you SO much for your help.

Try this. It should work on any set of similar data provided that all possible column headings are contained in the first block of data (i.e. from A1 to the first blank cell).

Let me know if it works (or not).

Sub SortData()
Dim LR As Long, i As Long, j As Long, k As Long, l As Integer, m As Integer
Dim XR As Integer, Headers() As Variant
Application.ScreenUpdating = False
XR = Range("A1").End(xlDown).Row
ReDim Headers(1 To XR)
For l = 1 To XR
    Headers(l) = Range("A" & l).Value
Next l
Range("C1").Resize(, XR).Value = Application.Transpose(Application.Transpose(Headers))
LR = Range("A" & Rows.Count).End(xlUp).Row
j = 2
For i = 1 To LR
    With Range("A" & i)
        If .Value = "" Then
            j = j + 1
        Else
            k = 2
            For m = 1 To XR
                If .Value = Headers(m) Then
                    k = k + m
                    Exit For
                End If
            Next m
            If k <> 2 Then Cells(j, k).Value = .Offset(, 1).Value
        End If
    End With
Next i
Columns("A:B").Delete
Application.ScreenUpdating = True
End Sub

 

[Georgine]

You are brilliant! I tested on a few data files and it works perfectly. I am very grateful for your help! I will test it some more but so far so good!

Again, please accept my humble thanks.

 

[VoG]

Jolly good. Thanks for the feedback