Converting data into rows and columns

G

Georgine

New Member
Joined
Jan 12, 2010
Messages
10
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Try running this macro with a copy of your sheet:

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
            End Select
            Cells(j, k).Value = .Offset(, 1).Value
        End If
    End With
Next i
Columns("A:B").Delete
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Or, If your Data has a Blank row every 8 rows and the Data starts in "A1", try this:-
Results on sheet (2)
Code: 
[COLOR="Navy"]Sub[/COLOR] MG12Jan41
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] Rw = 1 To Rng.Count [COLOR="Navy"]Step[/COLOR] 8
  [COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]If[/COLOR] Rw = 1 [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        .Range("A" & c).Resize(, 8) = Application.Transpose(Range("A" & Rw).Resize(8))
        c = c + 1
        .Range("A" & c).Resize(, 8) = Application.Transpose(Range("B" & Rw).Resize(8))
    [COLOR="Navy"]Else[/COLOR]
        c = c + 1
        .Range("A" & c).Resize(, 8) = Application.Transpose(Range("B" & Rw).Resize(8))
    [COLOR="Navy"]End[/COLOR] If
  [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] Rw
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Vog and MikeG
Thanks for this great code. This will work great with importing data from an ERP system and organizing this into a database format using this code. In your opinion, what is the best way to learn VBA? Is there a book or website?
 
Upvote 0
If there is other column headings between Last name and name that you do not want to include, or between name and last name the code will not create the column headings. So if there is country and division in the range, it will not create the column headings for the other ones that are specified in the code.
HTML: 
Name	
Address	
City	
State	
Zip	
First Name	
Last Name	
Country	
Continent	
Division	
Name	
Address	
City	
State	
Zip	
First Name	
Last Name

Is there a way to modify the code that will create column headings even if there is other column headings that you want to exclude?
 
Upvote 0
Just include all the headings that you want where marked in red

Rich (BB 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
            End Select
            Cells(j, k).Value = .Offset(, 1).Value
        End If
    End With
Next i
Columns("A:B").Delete
Application.ScreenUpdating = True
End Sub

You can add or remove items in the Select Case statement.
 
Upvote 0
Works perfect. What did you change in your original code to make this work?
 
Upvote 0
Vog I should have tested it more. I did not have Excel Genie in front of me. Here is the data with the column headings:
<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: 64px;"></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 style="text-align: right;">1</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 style="text-align: right;">2</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 style="text-align: right;">3</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 style="text-align: right;">4</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 style="text-align: right;">5</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 style="text-align: right;">6</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 style="text-align: right;">7</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td>Country</td> <td style="text-align: right;">8</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td>Continent</td> <td style="text-align: right;">9</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td>Division</td> <td style="text-align: right;">10</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td>Name</td> <td style="text-align: right;">11</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td>Address</td> <td style="text-align: right;">12</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td>City</td> <td style="text-align: right;">13</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td>State</td> <td style="text-align: right;">14</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td>Zip</td> <td style="text-align: right;">15</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td>First Name</td> <td style="text-align: right;">16</td></tr></tbody></table>
After the macro, here is the result:
<title>Excel Jeanie HTML</title>Sheet2

<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: 41px;"> <col style="width: 55px;"> <col style="width: 30px;"> <col style="width: 38px;"> <col style="width: 24px;"> <col style="width: 71px;"> <col style="width: 70px;"></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> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</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>Address</td> <td>City</td> <td>State</td> <td>Zip</td> <td>First Name</td> <td>Last Name</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="text-align: right;">11</td> <td style="text-align: right;">12</td> <td style="text-align: right;">13</td> <td style="text-align: right;">14</td> <td style="text-align: right;">15</td> <td style="text-align: right;">16</td> <td style="text-align: right;">10</td></tr></tbody></table>
So, it is only executing the code for row 11 to to 16, since there is no country and division in the array.

Is there a way to include the data values for the other rows, while excluding the rows for country, continent and division?
Excel tables to the web >> Excel Jeanie HTML 4

Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
need help finding cells with different values that contain the same mailing address
Replies
6
Views
568
Micron
M
D
  • Question
Inserting an image based on the content of the adjacent cell
Replies
0
Views
488
DarrelKelly
D
A
  • Solved
Transpose Multiple Columns to Rows by Fixing the value from 1 column and excluding Blanks
Replies
6
Views
464
kevin9999
K
O
  • Question
Upper limit of geography data type conversion?
Replies
3
Views
1K
Bosquedeguate
Bosquedeguate
S
  • Solved
CountIfs Formula help
2
Replies
10
Views
437
Fluff
Fluff

Forum statistics

Threads
1,215,133
Messages
6,123,234
Members
449,092
Latest member
SCleaveland

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top