Merging cells in the center of a worksheet

[nevermore627]

How do I merge cells in the center of a worksheet?

For example, I have 5 rows (call them 190 thru 195) which I want to
turn into 1 row.
The data is in the first 5 columns.
In column A, the data is in row 194
In column B, there is no data
In column C, the data is in rows 190, 191 and 192
In column D, the data is in all 5 rows
In column E, the data is in row 190 and 193

All data is text. Each set of data takes up anywhere from 3-5 rows,
each one different. I ended up with the spreadsheet in this format by
cutting and pasting from a word spreadsheet. Wherever there was a
bullet point the data jumped to a new row. I no longer have the
original word doc.

The end product should be a single row, with each column containing the
original data from all of its rows, appended.

I have a total of about 250 rows which need to be closer to 50-60 rows,
so cutting and pasting into a new spreadsheet would take all day. Any
help would be very appreciated. Thanks!

Mike Simard
[email protected]

[Maistrye]

How do I merge cells in the center of a worksheet?

For example, I have 5 rows (call them 190 thru 195) which I want to
turn into 1 row.
The data is in the first 5 columns.
In column A, the data is in row 194
In column B, there is no data
In column C, the data is in rows 190, 191 and 192
In column D, the data is in all 5 rows
In column E, the data is in row 190 and 193

All data is text. Each set of data takes up anywhere from 3-5 rows,
each one different. I ended up with the spreadsheet in this format by
cutting and pasting from a word spreadsheet. Wherever there was a
bullet point the data jumped to a new row. I no longer have the
original word doc.

The end product should be a single row, with each column containing the
original data from all of its rows, appended.

I have a total of about 250 rows which need to be closer to 50-60 rows,
so cutting and pasting into a new spreadsheet would take all day. Any
help would be very appreciated. Thanks!

Mike Simard
[email protected]

You might find this slightly cumbersome; if so, someone else may come up with something better. :-)

I'm assuming based on what you said that your data is only in columns A, B, C, D, E and that the rest are blank.

I'm also assuming that you have a header row with titles. You'll have to make adjustments if these assumptions aren't true.

In F2, put the first row that you have data in (probably 2).
In F3, put =F2+5.
Drag down as far as necessary. This represents the starting row to grab data from.

In G2, put =INDIRECT(ADDRESS($F2,COLUMN(A2)))&INDIRECT(ADDRESS($F2+1,COLUMN(A2)))&INDIRECT(ADDRESS($F2+2,COLUMN(A2)))&INDIRECT(ADDRESS($F2+3,COLUMN(A2)))&INDIRECT(ADDRESS($F2+4,COLUMN(A2)))
Someone may have something better than this formula, but it does work.
Drag this from G2 over to K2.
Drag G2:K2 down as far as necessary (probably down to G51:K51 if you have 250 rows).

At this point, open up the new worksheet that you want it in.
Copy your titles for each column to the new sheet.
Then highlight G2:K51 (or however far down it goes) and Copy.
Then go to the other worksheet and Paste Special where you want the data to go.

Hope this helps more than it confuses,
Scott