Howdy!

I am trying to organize a spreadsheet which originated out of a PDF report.
I cleaned the headers and pagecount etc. and now have the data stacked in
colum A of sheet1. Now the challenge is to loop through the rows and move
data onto sheet2.
Data in sheet1 might be 2 or 3 rows which contains name and address
information (Numeric and Text) followed by a unknow number of rows containing
numbers (Numeric only). This would be one record set till it again starts
with Numeric and Text values.
I would like to move (or copy) a record set onto one row of a sheet2, each
row found from sheet1 as new colum but same row in sheet2.

I know there is a way, but my knowledge of macros is still bound to
recording :(

How do you know when a record set ends? Is there a blank row. is the data
always three rows?

Have you tried on the wroksheet menu "data - Text to Columns" to seperate
data into columns.

"WinPro" wrote:

Howdy!

I am trying to organize a spreadsheet which originated out of a PDF report.
I cleaned the headers and pagecount etc. and now have the data stacked in
colum A of sheet1. Now the challenge is to loop through the rows and move
data onto sheet2.
Data in sheet1 might be 2 or 3 rows which contains name and address
information (Numeric and Text) followed by a unknow number of rows containing
numbers (Numeric only). This would be one record set till it again starts
with Numeric and Text values.
I would like to move (or copy) a record set onto one row of a sheet2, each
row found from sheet1 as new colum but same row in sheet2.

I know there is a way, but my knowledge of macros is still bound to
recording :(

As soon the number only row ends and a mixed (text, numeric) row begins

Thank you

"Joel" wrote:

How do you know when a record set ends? Is there a blank row. is the data
always three rows?

Have you tried on the wroksheet menu "data - Text to Columns" to seperate
data into columns.

"WinPro" wrote:

Howdy!

I am trying to organize a spreadsheet which originated out of a PDF report.
I cleaned the headers and pagecount etc. and now have the data stacked in
colum A of sheet1. Now the challenge is to loop through the rows and move
data onto sheet2.
Data in sheet1 might be 2 or 3 rows which contains name and address
information (Numeric and Text) followed by a unknow number of rows containing
numbers (Numeric only). This would be one record set till it again starts
with Numeric and Text values.
I would like to move (or copy) a record set onto one row of a sheet2, each
row found from sheet1 as new colum but same row in sheet2.

I know there is a way, but my knowledge of macros is still bound to
recording :(

Can you post some data so I can see

Just copy the data from spreadsheet and post on website.

"WinPro" wrote:

As soon the number only row ends and a mixed (text, numeric) row begins

Thank you

"Joel" wrote:

How do you know when a record set ends? Is there a blank row. is the data
always three rows?

Have you tried on the wroksheet menu "data - Text to Columns" to seperate
data into columns.

"WinPro" wrote:

Howdy!

I am trying to organize a spreadsheet which originated out of a PDF report.
I cleaned the headers and pagecount etc. and now have the data stacked in
colum A of sheet1. Now the challenge is to loop through the rows and move
data onto sheet2.
Data in sheet1 might be 2 or 3 rows which contains name and address
information (Numeric and Text) followed by a unknow number of rows containing
numbers (Numeric only). This would be one record set till it again starts
with Numeric and Text values.
I would like to move (or copy) a record set onto one row of a sheet2, each
row found from sheet1 as new colum but same row in sheet2.

I know there is a way, but my knowledge of macros is still bound to
recording :(

Joel,
Below a sample; first four rows would indicate one data set, the second data
set would be row 5 to 12, 13 to 15 the third.

N20100857 LastName, JAMES EDWARD 16418 Street City TX 77049
N20100857 LEASE D9362 last ROBERT T. -2-, Company, 21 M R PALACIOS SEC
176840,
0.00 2007 19.18 1.72 20.90
5.57 2006 27.16 7.06 39.79
R19455 LastName JOHN C, Jr. P O BOX 191433 City TX 213434
R19455 RDIV 047-XLVII-Town BLOCK B, LOT 010-011 & 012(S/50')
0.00 2007 11.21 0.99 12.20
2.83 2006 12.99 3.38 19.20
3.31 2005 13.81 5.26 22.38
3.21 2004 14.20 7.12 24.53
3.47 2003 14.28 8.86 26.61
3.65 2002 13.95 10.32 27.92
N20316576 LastName JOHNNIE P O BOX 207 Town, TX 12434-0207
N20316576 LEASE Q0658 Name -E 8-, OPERATING COMP, 21 M PALACIOS SEC 174440
0.00 2007 3.01 0.27 3.28

Thanks again

"Joel" wrote:

Can you post some data so I can see

Just copy the data from spreadsheet and post on website.

"WinPro" wrote:

As soon the number only row ends and a mixed (text, numeric) row begins

Thank you

"Joel" wrote:

How do you know when a record set ends? Is there a blank row. is the data
always three rows?

Have you tried on the wroksheet menu "data - Text to Columns" to seperate
data into columns.

"WinPro" wrote:

Howdy!

I am trying to organize a spreadsheet which originated out of a PDF report.
I cleaned the headers and pagecount etc. and now have the data stacked in
colum A of sheet1. Now the challenge is to loop through the rows and move
data onto sheet2.
Data in sheet1 might be 2 or 3 rows which contains name and address
information (Numeric and Text) followed by a unknow number of rows containing
numbers (Numeric only). This would be one record set till it again starts
with Numeric and Text values.
I would like to move (or copy) a record set onto one row of a sheet2, each
row found from sheet1 as new colum but same row in sheet2.

I know there is a way, but my knowledge of macros is still bound to
recording :(

I'm using the string "LastName" to start a new block of data. See how this
code works. It moves data from sheet1 to sheet2.


Sub combine_rows()

Sh2RowCount = 1
State = 1
With Sheets("sheet1")
Sh1RowCount = 1
Do While .Range("A" & Sh1RowCount) < ""
data = .Range("A" & Sh1RowCount)
With Sheets("sheet2")
If InStr(data, "LastName") 0 Then
State = 1
.Range("A" & Sh2RowCount) = data
Else
Select Case State
Case 1
State = 2
.Range("B" & Sh2RowCount) = data
Case 2
.Range("C" & Sh2RowCount) = data
.Range("C" & Sh2RowCount).TextToColumns _
Destination:=Range("C" & Sh2RowCount), _
DataType:=xlDelimited, _
Space:=True
Sh2RowCount = Sh2RowCount + 1
End Select
End If
End With
Sh1RowCount = Sh1RowCount + 1
Loop
End With
Sheets("sheet2").Columns("A:G").Columns.AutoFit
End Sub


"WinPro" wrote:

Joel,
Below a sample; first four rows would indicate one data set, the second data
set would be row 5 to 12, 13 to 15 the third.

N20100857 LastName, JAMES EDWARD 16418 Street City TX 77049
N20100857 LEASE D9362 last ROBERT T. -2-, Company, 21 M R PALACIOS SEC
176840,
0.00 2007 19.18 1.72 20.90
5.57 2006 27.16 7.06 39.79
R19455 LastName JOHN C, Jr. P O BOX 191433 City TX 213434
R19455 RDIV 047-XLVII-Town BLOCK B, LOT 010-011 & 012(S/50')
0.00 2007 11.21 0.99 12.20
2.83 2006 12.99 3.38 19.20
3.31 2005 13.81 5.26 22.38
3.21 2004 14.20 7.12 24.53
3.47 2003 14.28 8.86 26.61
3.65 2002 13.95 10.32 27.92
N20316576 LastName JOHNNIE P O BOX 207 Town, TX 12434-0207
N20316576 LEASE Q0658 Name -E 8-, OPERATING COMP, 21 M PALACIOS SEC 174440
0.00 2007 3.01 0.27 3.28

Thanks again

"Joel" wrote:

Can you post some data so I can see

Just copy the data from spreadsheet and post on website.

"WinPro" wrote:

As soon the number only row ends and a mixed (text, numeric) row begins

Thank you

"Joel" wrote:

How do you know when a record set ends? Is there a blank row. is the data
always three rows?

Have you tried on the wroksheet menu "data - Text to Columns" to seperate
data into columns.

"WinPro" wrote:

Howdy!

I am trying to organize a spreadsheet which originated out of a PDF report.
I cleaned the headers and pagecount etc. and now have the data stacked in
colum A of sheet1. Now the challenge is to loop through the rows and move
data onto sheet2.
Data in sheet1 might be 2 or 3 rows which contains name and address
information (Numeric and Text) followed by a unknow number of rows containing
numbers (Numeric only). This would be one record set till it again starts
with Numeric and Text values.
I would like to move (or copy) a record set onto one row of a sheet2, each
row found from sheet1 as new colum but same row in sheet2.

I know there is a way, but my knowledge of macros is still bound to
recording :(

try this code instead. I realized you replace the real Last names with the
word LastName.


Sub combine_rows2()

Sh2RowCount = 1
State = 2
With Sheets("sheet1")
Sh1RowCount = 1
Do While .Range("A" & Sh1RowCount) < ""
data = .Range("A" & Sh1RowCount)
With Sheets("sheet2")
If Not IsNumeric(Left(data, 1)) And _
State < 1 Then

State = 1
.Range("A" & Sh2RowCount) = data
Else
Select Case State
Case 1
State = 2
.Range("B" & Sh2RowCount) = data
Case 2
.Range("C" & Sh2RowCount) = data
.Range("C" & Sh2RowCount).TextToColumns _
Destination:=Range("C" & Sh2RowCount), _
DataType:=xlDelimited, _
Space:=True
Sh2RowCount = Sh2RowCount + 1
End Select
End If
End With
Sh1RowCount = Sh1RowCount + 1
Loop
End With
Sheets("sheet2").Columns("A:G").Columns.AutoFit
End Sub


"WinPro" wrote:

Joel,
Below a sample; first four rows would indicate one data set, the second data
set would be row 5 to 12, 13 to 15 the third.

N20100857 LastName, JAMES EDWARD 16418 Street City TX 77049
N20100857 LEASE D9362 last ROBERT T. -2-, Company, 21 M R PALACIOS SEC
176840,
0.00 2007 19.18 1.72 20.90
5.57 2006 27.16 7.06 39.79
R19455 LastName JOHN C, Jr. P O BOX 191433 City TX 213434
R19455 RDIV 047-XLVII-Town BLOCK B, LOT 010-011 & 012(S/50')
0.00 2007 11.21 0.99 12.20
2.83 2006 12.99 3.38 19.20
3.31 2005 13.81 5.26 22.38
3.21 2004 14.20 7.12 24.53
3.47 2003 14.28 8.86 26.61
3.65 2002 13.95 10.32 27.92
N20316576 LastName JOHNNIE P O BOX 207 Town, TX 12434-0207
N20316576 LEASE Q0658 Name -E 8-, OPERATING COMP, 21 M PALACIOS SEC 174440
0.00 2007 3.01 0.27 3.28

Thanks again

"Joel" wrote:

Can you post some data so I can see

Just copy the data from spreadsheet and post on website.

"WinPro" wrote:

As soon the number only row ends and a mixed (text, numeric) row begins

Thank you

"Joel" wrote:

How do you know when a record set ends? Is there a blank row. is the data
always three rows?

Have you tried on the wroksheet menu "data - Text to Columns" to seperate
data into columns.

"WinPro" wrote:

Howdy!

I am trying to organize a spreadsheet which originated out of a PDF report.
I cleaned the headers and pagecount etc. and now have the data stacked in
colum A of sheet1. Now the challenge is to loop through the rows and move
data onto sheet2.
Data in sheet1 might be 2 or 3 rows which contains name and address
information (Numeric and Text) followed by a unknow number of rows containing
numbers (Numeric only). This would be one record set till it again starts
with Numeric and Text values.
I would like to move (or copy) a record set onto one row of a sheet2, each
row found from sheet1 as new colum but same row in sheet2.

I know there is a way, but my knowledge of macros is still bound to
recording :(