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