| Home |
| Search |
| Today's Posts |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move data based on data type
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 :( |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Scatterplots with different colors based on Type of Data | Charts and Charting in Excel | |||
| Macro to move data to different column based on data in another co | Excel Discussion (Misc queries) | |||
| create macro to move label type data to column data | Excel Programming | |||
| IF statement based on data type | Excel Worksheet Functions | |||
| Move data from on sheet to another based on month | Excel Programming | |||
Threaded Mode
