Multiple Rows Into One

  • Thread starter Thread starter PR
  • Start date Start date
P

PR

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
 
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
 
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.
 
Well. if you would like a formula suggesting, then please give some
details about the layout of your data.

Pete
 
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!
 
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
 
Don,

Thank you very much for this it works well... Thanks to all that have
replied...

Regards - Paul
 
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
 
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.....
 
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
 
Please NEVER attach files to send to THIS newsgroup. It is not appreciated.
Having said that, you have merged cells in col A. If all you want to do is
unmerge then
select the header (where it says A)>right click>format>alignment>uncheck
merge cells. Voila
 
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
 
Back
Top