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 :(

Joel,

Fast and efficient move; a lot of learning for me there ;)

It took data from sheet1 and created new colums for each space but did not
move each data set into a single row on sheet2. Sheet2 still has the same
number of rows as sheet1

Regards and thanks,
Josef

"Joel" wrote:

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 :(

did you use the 2nd macro I sent. I can move all the data into seperate
columns, I thought it was bettter the way I did it. It does combine the data
into 3 columns, just each data set has a seperate row. The code also does a
text to columns to split data into seperate columns.

"WinPro" wrote:

Joel,

Fast and efficient move; a lot of learning for me there ;)

It took data from sheet1 and created new colums for each space but did not
move each data set into a single row on sheet2. Sheet2 still has the same
number of rows as sheet1

Regards and thanks,
Josef

"Joel" wrote:

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 :(

Sorry about the last name confusion, even it's from a public record I try to
keep it protected. This one did the trick, almost. Is there any way I can
forward what I need and reimburse you for your trouble?

"Joel" wrote:

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 :(

No reimbursement needed. You can sned me the file to joel warburg at itt dot
com. let me know what changes are needed. Should be simple.

"WinPro" wrote:

Sorry about the last name confusion, even it's from a public record I try to
keep it protected. This one did the trick, almost. Is there any way I can
forward what I need and reimburse you for your trouble?

"Joel" wrote:

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 :(

Joel,
Sorry had to leave the office for service calls; just received the message I
send you - bounced. Is there a dot between your name and is the domain att?

Thanks,
Josef

"Joel" wrote:

No reimbursement needed. You can sned me the file to joel warburg at itt dot
com. let me know what changes are needed. Should be simple.

"WinPro" wrote:

Sorry about the last name confusion, even it's from a public record I try to
keep it protected. This one did the trick, almost. Is there any way I can
forward what I need and reimburse you for your trouble?

"Joel" wrote:

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 :(

left a dot out

joel dot warburg at itt dot com

"WinPro" wrote:

Joel,
Sorry had to leave the office for service calls; just received the message I
send you - bounced. Is there a dot between your name and is the domain att?

Thanks,
Josef

"Joel" wrote:

No reimbursement needed. You can sned me the file to joel warburg at itt dot
com. let me know what changes are needed. Should be simple.

"WinPro" wrote:

Sorry about the last name confusion, even it's from a public record I try to
keep it protected. This one did the trick, almost. Is there any way I can
forward what I need and reimburse you for your trouble?

"Joel" wrote:

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 :(