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?

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

"Kerevek" wrote:

> 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?

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

"OssieMac" wrote:

> 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
>
> "Kerevek" wrote:
>
> > 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?

That would be great! I would need the instructions as well.

Thanks!

Kerevek

"OssieMac" wrote:

> 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
>
> "Kerevek" wrote:
>
> > 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?

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?

On Mon, 20 Aug 2007 21:22:02 -0700, Kerevek
<(E-Mail Removed)> wrote:

>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?

What version of xl please?

"Kerevek" wrote:

> That would be great! I would need the instructions as well.
>
> Thanks!
>
> Kerevek
>
> "OssieMac" wrote:
>
> > 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
> >
> > "Kerevek" wrote:
> >
> > > 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?

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



"OssieMac" wrote:

> What version of xl please?
>
> "Kerevek" wrote:
>
> > That would be great! I would need the instructions as well.
> >
> > Thanks!
> >
> > Kerevek
> >
> > "OssieMac" wrote:
> >
> > > 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
> > >
> > > "Kerevek" wrote:
> > >
> > > > 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?

That is correct.

"Wild Bill" wrote:

> 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?
>
> On Mon, 20 Aug 2007 21:22:02 -0700, Kerevek
> <(E-Mail Removed)> wrote:
>
> >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?
>
>

A solution that does not matter if each item is available in each set.
Creating a Spreadsheet from Database data (#dbdata) within...
SNAKECOLS, How to snake columns to use fewer pages
http://www.mvps.org/dmcritchie/excel...col.htm#dbdata
the code is in
http://www.mvps.org/dmcritchie/excel/code/naddrdb.txt
New column and title will be created when a new title appears in Column A

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

"Kerevek" <(E-Mail Removed)> wrote in message
news:2102BB31-7E1B-47CD-9532-(E-Mail Removed)...
> That is correct.
>
> "Wild Bill" wrote:
>
>> 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?
>>
>> On Mon, 20 Aug 2007 21:22:02 -0700, Kerevek
>> <(E-Mail Removed)> wrote:
>>
>> >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?
>>
>>

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

"Kerevek" <(E-Mail Removed)> wrote in message
news:757BC0D6-A5CF-44C8-B662-(E-Mail Removed)...
>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?