Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging cells in the center of a worksheet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging cells in the center of a worksheet
nevermore627 Wrote: 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 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(ADDRES S($F2+1,COLUMN(A2)))&INDIRECT(ADDRESS($F2+2,COLUMN (A2)))&INDIRECT(ADDRESS($F2+3,COLUMN(A2)))&INDIREC T(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 -- Maistrye ------------------------------------------------------------------------ Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078 View this thread: http://www.excelforum.com/showthread...hreadid=563366 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging Cells that contain data anomaly | Excel Discussion (Misc queries) | |||
Copying a worksheet witrh protected cells to a new worksheet | Excel Worksheet Functions | |||
make multiple cells in 1 worksheet equal multiple cells in another | Excel Worksheet Functions | |||
Merging cells in Excel 2003 | Excel Discussion (Misc queries) | |||
Linking cells in a worksheet to other worksheets in a workbook | Excel Discussion (Misc queries) |