 |
 | 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 / New Users / February 2008Multiple Rows Into One
Is there a quick way of turning text that is on multiple rows into one row, one problem that have with this, is that I have text say in 4-7 that I what all in row 4, then the next maybe row 8 & 9 into row 8, then the next maybe row 12-14 into row 12 and so on. They is no pattern to this... Regards - Paul If there is no pattern, then how would you expect an Excel formula to deduce that a particular row is the start of a new record? What other data do you have? Can this be used to determine a new record? Is it possible for you manually to enter a character like "Y" in appropriate cells to indicate start of new record? (i.e. how much data do you have?) Pete > Is there a quick way of turning text that is on multiple rows into one row, > one problem that have with this, is that I have text say in 4-7 that I what > all in row 4, then the next maybe row 8 & 9 into row 8, then the next maybe > row 12-14 into row 12 and so on. They is no pattern to this... > > Regards - Paul Pete, Many thanks for your reply, yes I do understand what you say... I can indicate the start of a new record, I do have other data against the row of data, but this fine. In total I have 500 rows which is about 250 ish records.. Regards - Paul If there is no pattern, then how would you expect an Excel formula to deduce that a particular row is the start of a new record? What other data do you have? Can this be used to determine a new record? Is it possible for you manually to enter a character like "Y" in appropriate cells to indicate start of new record? (i.e. how much data do you have?) Pete On Feb 6, 11:05 am, "PR" <paul.raeb...@vodafone.net> wrote: > Is there a quick way of turning text that is on multiple rows into one > row, [quoted text clipped - 5 lines] > > Regards - Paul Well. if you would like a formula suggesting, then please give some details about the layout of your data. Pete > Pete, > Many thanks for your reply, yes I do understand what you say... I can [quoted text clipped - 26 lines] > > - Show quoted text - Pete, here is a sample of my data see... Sheffield Stadium The Global Windows Queen Mother Memorial Cup Heats 19 February 2008 Winner £100 660m Flat, 24 greyhounds & 4 resvs Entries close Noon 15 February 2008 5% of the winners' prize money to be donated to the Sheffield Retired Greyhound Fund. Sheffield Stadium The Global Windows Queen Mother Memorial Cup Semi Finals 26 February 2008 Winner £150 660m Flat, 12 greyhounds Sheffield Stadium The Global Windows Queen Mother Memorial Cup Final 02 March 2008 Winner £4000 and trophy 2nd £500 660m Flat, 6 greyhounds Monmore Green Stadium The Ladbrokes Spring Festival 630 Heats 03 March 2008 Winner £175 2nd £50 630m Flat, 18 greyhounds & 6 resvs Entries close 11.00am 28 February 2008 Regards - Paul Well. if you would like a formula suggesting, then please give some details about the layout of your data. Pete On Feb 6, 11:30 am, "PR" <paul.raeb...@vodafone.net> wrote: > Pete, > Many thanks for your reply, yes I do understand what you say... I can [quoted text clipped - 28 lines] > > - Show quoted text - You could have actually had a FIND macro looking for part to find stadium. Assuming stadium is said only once. I have done this for clients in the past  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Pete, > here is a sample of my data see... [quoted text clipped - 55 lines] >> >> - Show quoted text - You probably need a macro to fix the problems. The best way to fix the problem is to correct the problem before the data is put into Excel. Once it is in excel you need to find a pattern with the data to cure the problem. Sometimes KEY words in the text can be used to help figure out which rows need to be combined. There is no EASY way to fix these problems unless you the data has some sort of pattern! > Pete, > Many thanks for your reply, yes I do understand what you say... I can [quoted text clipped - 23 lines] > > > > Regards - Paul If you copy the cells and paste them into Notepad. Then copy them back again from Notepad and paste them into fx box in excel. > Is there a quick way of turning text that is on multiple rows into one row, > one problem that have with this, is that I have text say in 4-7 that I what > all in row 4, then the next maybe row 8 & 9 into row 8, then the next maybe > row 12-14 into row 12 and so on. They is no pattern to this... > > Regards - Paul Here is one I designed sometime ago to consolidate formulas broken up by email. You will need a blank row above and below the rows to consolidate. Then place cursor on top row and fire macro. I have assigned to a button on my toolbar. Sub FixLongFormulas() 'goto a remote area of ws & select 1st line x = ActiveCell.Row y = ActiveCell.Column z = ActiveCell.End(xlDown).Row For Each C In Range(Cells(x, y), Cells(z, y)) 'Cells(x - 1, y) = Cells(x - 1, y) & C mstr = mstr & C Next Cells(x - 1, y) = mstr End Sub SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Is there a quick way of turning text that is on multiple rows into one > row, one problem that have with this, is that I have text say in 4-7 that > I what all in row 4, then the next maybe row 8 & 9 into row 8, then the > next maybe row 12-14 into row 12 and so on. They is no pattern to this... > > Regards - Paul Don, Thank you very much for this it works well... Thanks to all that have replied... Regards - Paul > Here is one I designed sometime ago to consolidate formulas broken up by > email. You will need a blank row above and below the rows to consolidate. [quoted text clipped - 18 lines] >> >> Regards - Paul Sorry Don, The code actually takes the cell below the empty cell... Regards - paul > Here is one I designed sometime ago to consolidate formulas broken up by > email. You will need a blank row above and below the rows to consolidate. [quoted text clipped - 18 lines] >> >> Regards - Paul I said you would need a blank cell above and place the cursor on the top row of what you are converting. Look at the macro and you will see that x is the active row and x-1 is where the consolidation is placed. Hence the need for a blank cell above the top line..... SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Sorry Don, > [quoted text clipped - 25 lines] >>> >>> Regards - Paul Don, Yes I have done what you have said, a blank row above and below... Regards - Paul > I said you would need a blank cell above and place the cursor on the top > row of what you are converting. Look at the macro and you will see that x [quoted text clipped - 29 lines] >>>> >>>> Regards - Paul Your data was NOT laid out as it appeared on these posts. You have it showing one line under the other when in fact it was in the next column. GEEZ Sheffield Stadium The Global Windows Queen Mother Memorial Cup Heats SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Don, > Yes I have done what you have said, a blank row above and below... [quoted text clipped - 34 lines] >>>>> >>>>> Regards - Paul |
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.