 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2008Move data based on data type
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. > Howdy! > [quoted text clipped - 11 lines] > 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 > How do you know when a record set ends? Is there a blank row. is the data > always three rows? [quoted text clipped - 17 lines] > > 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. > As soon the number only row ends and a mixed (text, numeric) row begins > [quoted text clipped - 21 lines] > > > 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 > Can you post some data so I can see > [quoted text clipped - 25 lines] > > > > 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 > Joel, > Below a sample; first four rows would indicate one data set, the second data [quoted text clipped - 48 lines] > > > > > 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 > 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. > [quoted text clipped - 84 lines] > > > > > > 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. > Joel, > [quoted text clipped - 95 lines] > > > > > > > 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 > Joel, > Below a sample; first four rows would indicate one data set, the second data [quoted text clipped - 48 lines] > > > > > 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? > try this code instead. I realized you replace the real Last names with the > word LastName. [quoted text clipped - 86 lines] > > > > > > 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. > 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 [quoted text clipped - 90 lines] > > > > > > > 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 > 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. [quoted text clipped - 93 lines] > > > > > > > > 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 > Joel, > Sorry had to leave the office for service calls; just received the message I [quoted text clipped - 100 lines] > > > > > > > > > I know there is a way, but my knowledge of macros is still bound to > > > > > > > > > recording :( |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.