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

On Feb 6, 11:05*am, "PR" wrote:
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


"Pete_UK" wrote in message
...
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" wrote:
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 you copy the cells and paste them into Notepad. Then copy them back again
from Notepad and paste them into fx box in excel.

"PR" wrote:

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

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" wrote:
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

"Pete_UK" wrote in message

...
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" wrote:



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- Hide quoted text -

- 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!

"PR" wrote:

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


"Pete_UK" wrote in message
...
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" wrote:
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PR" wrote in message
...
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
"Don Guillett" wrote in message
...
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 Guillett
Microsoft MVP Excel
SalesAid Software

"PR" wrote in message
...
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,
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


"Pete_UK" wrote in message
...
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" wrote:
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

"Pete_UK" wrote in message

...
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" wrote:



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- Hide quoted text -

- Show quoted text -

Sorry Don,

The code actually takes the cell below the empty cell...

Regards - paul

"Don Guillett" wrote in message
...
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 Guillett
Microsoft MVP Excel
SalesAid Software

"PR" wrote in message
...
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

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.....
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PR" wrote in message
...
Sorry Don,

The code actually takes the cell below the empty cell...

Regards - paul

"Don Guillett" wrote in message
...
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 Guillett
Microsoft MVP Excel
SalesAid Software

"PR" wrote in message
...
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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PR" wrote in message
...
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


"Pete_UK" wrote in message
...
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" wrote:
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

"Pete_UK" wrote in message

...
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" wrote:



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- Hide quoted text -

- Show quoted text -

Don,
Yes I have done what you have said, a blank row above and below...

Regards - Paul

"Don Guillett" wrote in message
...

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.....
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PR" wrote in message
...
Sorry Don,

The code actually takes the cell below the empty cell...

Regards - paul

"Don Guillett" wrote in message
...
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 Guillett
Microsoft MVP Excel
SalesAid Software

"PR" wrote in message
...
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

Sorry, here is a better example of the data... see attached...

Regards - Paul


"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PR" wrote in message
...
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


"Pete_UK" wrote in message
...
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" wrote:
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

"Pete_UK" wrote in message

...
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" wrote:



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- Hide quoted text -

- Show quoted text -

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 clickformatalignmentuncheck
merge cells. Voila

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PR" wrote in message
...
Sorry, here is a better example of the data... see attached...

Regards - Paul


"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PR" wrote in message
...
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


"Pete_UK" wrote in message
...
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" wrote:
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

"Pete_UK" wrote in message

...
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" wrote:



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- Hide quoted text -

- Show quoted text -

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PR" wrote in message
...
Don,
Yes I have done what you have said, a blank row above and below...

Regards - Paul

"Don Guillett" wrote in message
...

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.....
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PR" wrote in message
...
Sorry Don,

The code actually takes the cell below the empty cell...

Regards - paul

"Don Guillett" wrote in message
...
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 Guillett
Microsoft MVP Excel
SalesAid Software

"PR" wrote in message
...
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