Convert repeating row headers into columns

K

Kerevek

I have data that I've copied and the data within the spreadsheet is within 2
columns. The first column repeats the headers while the second column has the
data

For example

Name: John Smith
Address: 1234 5th Ave
City: Redmond
State: WA
Zip Code: 99934

Name: Bob Thomas
Address: 12111 Third St
City: Seattle
State: WA
Zip Code: 98101

How can I conver the first column into the first row as headers and take the
data for each record and place into each row?
 
O

OssieMac

I have a macro which will do this if you want it. If yes then do you need
instructions to copy the macro into a workbook and run it?

Regards,

OssieMac
 
O

OssieMac

Forgot this before. If you want instructions to use the macro, what version
of xl are you using?
 
W

Wild Bill

Are the same 5 headings (strictly) always present, and is your data
(strictly) always in the pattern of those 5 lines followed by a blank
line?
 
O

OssieMac

After my last post asking for your version of xl I decided to combine the
instructions so they cover xl2007 and the earlier versions.

WARNING:- Create a backup copy of your workbook in case something goes wrong.

Your data is to be in column A only. (Delete the column with the headers so
that the data is only in column A.)

It is a generic macro to convert the type of data you have and it will run
with any number of rows in the address.

Instructions for inserting the macro and running it.

All versions of Excel:-

1. Alt/F11 to open VBA Editor.

2. Click on menu item Insert then Module.

3. Copy the macro from this posting into the VBA editor. (The large white
blank area on the right after you inserted the module). Ensure that you get
all of the macro from the first line
‘Sub Process_Name_Address()’ to last line ‘End Sub’.

4. Change windows back to the Excel Worksheet. (Ensure that you are on the
worksheet with the raw data because the macro runs from the active sheet.)

Xl2007 Instructions:- (For pre XL2007 go to Step 9)

5. Click on Developer tab to display the Developer ribbon. If Developer tab
is not visible then do the following:-

6. Click the Microsoft Office Button (Large button top left of screen) and
then click Excel Options towards bottom right of dialogue box.

7. Click Popular, and then select the Show Developer tab in the Ribbon check
box. (Then Click on Developer tab to display the Developer ribbon.)

8. Click on Macros button then Click Process_Name_Address then Run.
Goto step 10.

Pre xl2007 Instructions:-

9. Select Tools->Macro->Macros->Process_Name_Address->Run

10. In the Input Box enter the number of rows of data in each Name and
Address group. If there is a blank line between the groups then include that
in the count. (In the example you gave it should be 6 if there is a blank
line between groups of 5 if there is no blank line.)

11. In the next Input Box enter the row number where the actual data starts.
If there is no column header then it should be 1 but if there is a column
header it should be 2.

12. The Macro will create a new worksheet and copy the data to it. The
column headers will be generic so all you have to do is edit the headers as
required.


Sub Process_Name_Address()
'Create horizontal list of addresses from
'vertical list of addresses.
'Original data to be in column A
'Run macro from original data worksheet

Dim wsOrigData As Worksheet
Dim wsOutput As Worksheet
Dim rngOrig As Range
Dim addressRows As Integer
Dim startRow As Integer
Dim i As Integer
Dim j As Integer

'User to enter number of rows in each address group
addressRows = Application.InputBox _
(prompt:="How many rows of data in each address group?", _
Type:=1)
If addressRows < 1 Then
MsgBox "Invalid input"
End
End If

'User to enter the number of the row on which the data starts.
startRow = Application.InputBox _
(prompt:="Enter the row number where the data commences" _
& Chr(13) & "Example:If no column header then 1" _
& Chr(13) & "If column header exists then 2", _
Type:=1)
If startRow < 1 Then
MsgBox "Invalid input"
End
End If

'Creates variable for original data worksheet
Set wsOrigData = ActiveSheet

'Creates a variable for the range of original data
With wsOrigData
Set rngOrig = .Range(.Cells(startRow, 1), _
.Cells(.Rows.Count, 1).End(xlUp))
End With

'Adds a worksheet for output
Sheets.Add

'Names the output data worksheet
'ActiveSheet.Name = "Output Data"

'Creates variable for output worksheet
Set wsOutput = ActiveSheet

'Inserts headers in the output worksheet
With wsOutput
For j = 1 To addressRows
.Cells(1, j) = "Output " & j
Next j
End With


'Copy data from vertical format to to _
'to horizontal format on output sheet
For i = 1 To rngOrig.Count Step addressRows 'Each address group
With wsOutput
For j = 1 To addressRows 'Number of rows in address
'Populate each cell across the row
'.Cells(Rows.Count, j).End(xlUp).Offset(1, 0) _
= wsOrigData.Cells(i + j - 1, 1)
.Cells(Rows.Count, j).End(xlUp).Offset(1, 0) _
= rngOrig.Cells(i + j - 1, 1)


Next j
End With

Next i
wsOutput.Select
Range(Columns(1), Columns(addressRows)).AutoFit
Range("A1").Select

End Sub


Hope it works as you want.

Regards,

OssieMac
 
K

Kerevek

That is correct.

Wild Bill said:
Are the same 5 headings (strictly) always present, and is your data
(strictly) always in the pattern of those 5 lines followed by a blank
line?
Click to expand...
 
K

kounoike

I assume headers reside in column A and data in column B. First select all
headers of first record set, e.g. A1:A5, and then run macro
Convertdatalayout below, it would place all records into rows in a new
worksheet named "temp".

Sub Convertdatalayout()
Dim dstwk As Worksheet, srcwk As Worksheet
Dim data As Range, dataf As Range, head As Range
Dim dstrow As Long
Dim dataco As Long
Dim ret

On Error Resume Next
Set data = Selection.Resize(, 2)
Set dataf = data
dataf.Interior.ColorIndex = 6
ret = MsgBox("Your first Records set reside in " & data.Address _
& Chr(13) & "Do you want to continue? ", vbYesNo)
If ret = vbNo Then
dataf.Interior.ColorIndex = xlColorIndexNone
Exit Sub
End If
Application.ScreenUpdating = False
Set srcwk = ActiveSheet
Set dstwk = Worksheets("temp")
If dstwk Is Nothing Then
Set dstwk = Worksheets.Add(after:=ActiveSheet)
dstwk.Name = "temp"
srcwk.Select
End If
dstrow = dstwk.Cells(Cells.Rows.Count, "a").End(xlUp).row
If dstwk.Cells(dstrow, "A").Value <> "" Then
dstrow = dstrow + 1
End If
Set head = data.Resize(1, 1)
dataco = data.Rows.Count
data.Copy
dstwk.Cells(dstrow, "A").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
dstrow = dstrow + 2
Do
Set data = Columns(head.Column).Find(head.Value, after:=head)
If data Is Nothing Then
Exit Do
ElseIf data.row <= head.row Then
Exit Do
Else
Set head = data
Set data = data.Offset(0, 1).Resize(dataco, 1)
data.Copy
dstwk.Cells(dstrow, "A").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
dstrow = dstrow + 1
Set data = Nothing
End If
Loop
Application.CutCopyMode = False
dataf.Interior.ColorIndex = xlColorIndexNone
End Sub

keizi
 
P

Patricia Seward

Iam using micosoft office word, I need to convert the information on a spreedsheet

Example:

Mr. Berry D hudons
pob 00
334 County route 22
Parish, Ny 44444
333-333-3333
(e-mail address removed)
This needs to be on a spreedsheet going horizontally
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Mailing address block: sorting 1 row to columns 1
Expert Ron Needed.... 1
Excel 2013: New row added automatically but first 2 columns Drop down lists not available... 0
convert columns to rows 6
Emailing from cells 1
Moving data from a string 1
Help: Find Matching Cells, If - Then Move Cell With Self 2
How to use matching values in two worksheets to return another value 5

Top