Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how to combine several columns into a single column
I have data in 50 columns and 30 rows.
I would like to know how to either copy or move the data in the next columns to the next row of the previous column without using "copy and paste" because there are too many columns and I think there is a better way to do this. What I mean is that I want to have only one column combining all data from 50 different columns into one column. So it looks like this after moving or copying the data from columns into one column. column A ------------ A1 .... A30 B1 ( data in the column B is now in the next row in the column A at the 31st row) .... B30 ...... .... AX1 ( data in the column AX is now in the next row in the column A at the 1471st row) .... Ax30 Thank you Jim |
#2
|
|||
|
|||
One play ..
Assume source data is in Sheet1, A1:AX30 In Sheet2 Put in A1: =OFFSET(INDIRECT("Sheet1!"&CHAR(INT((ROWS(Sheet1!$ A$1:A1)-1)/30)+65)&"1"),MO D(ROWS(Sheet1!$A$1:A1)-1,30),) Copy A1 down to A780 (Above strips cols A to Z) Put in A781: =OFFSET(INDIRECT("Sheet1!A"&CHAR(INT((ROWS(Sheet1! $A$1:A1)-1)/30)+65)&"1"),M OD(ROWS(Sheet1!$A$1:A1)-1,30),) Copy A781 down to A1500 (Above strips cols AA to AX) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "jims" wrote in message ... I have data in 50 columns and 30 rows. I would like to know how to either copy or move the data in the next columns to the next row of the previous column without using "copy and paste" because there are too many columns and I think there is a better way to do this. What I mean is that I want to have only one column combining all data from 50 different columns into one column. So it looks like this after moving or copying the data from columns into one column. column A ------------ A1 ... A30 B1 ( data in the column B is now in the next row in the column A at the 31st row) ... B30 ..... ... AX1 ( data in the column AX is now in the next row in the column A at the 1471st row) ... Ax30 Thank you Jim |
#3
|
|||
|
|||
Jim,
Put this in A31, and copy down. =OFFSET(A31,-(INT((ROW()-1)/30))*30,INT((ROW()-1)/30),1,1) Empty cells will give you zero. You can then convert the formulas to hard data: Copy the entire column, then paste it over itself with Edit - Paste special - Values. Now you don't need the stuff in columns B, C, etc. -- Earl Kiosterud www.smokeylake.com "jims" wrote in message ... I have data in 50 columns and 30 rows. I would like to know how to either copy or move the data in the next columns to the next row of the previous column without using "copy and paste" because there are too many columns and I think there is a better way to do this. What I mean is that I want to have only one column combining all data from 50 different columns into one column. So it looks like this after moving or copying the data from columns into one column. column A ------------ A1 ... A30 B1 ( data in the column B is now in the next row in the column A at the 31st row) ... B30 ..... ... AX1 ( data in the column AX is now in the next row in the column A at the 1471st row) ... Ax30 Thank you Jim |
#4
|
|||
|
|||
"Earl Kiosterud" wrote
.... Put this in A31, and copy down. =OFFSET(A31,-(INT((ROW()-1)/30))*30,INT((ROW()-1)/30),1,1) Much neater, Earl ! Perhaps with just a typo corrected, in A31, copied down to A1530: =OFFSET(A31,-(INT((ROW()-1)/30))*30,INT((ROW()-1)/30)-1,1,1) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#5
|
|||
|
|||
Dear Mr. Earl and Max,
It worked fine. You gentlemen are just great. Thank you very much, Jim "Max" wrote: "Earl Kiosterud" wrote .... Put this in A31, and copy down. =OFFSET(A31,-(INT((ROW()-1)/30))*30,INT((ROW()-1)/30),1,1) Much neater, Earl ! Perhaps with just a typo corrected, in A31, copied down to A1530: =OFFSET(A31,-(INT((ROW()-1)/30))*30,INT((ROW()-1)/30)-1,1,1) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#6
|
|||
|
|||
You're welcome, Jim !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "jims" wrote in message ... Dear Mr. Earl and Max, It worked fine. You gentlemen are just great. Thank you very much, Jim |
#7
|
|||
|
|||
Max,
Actually, it wasn't a typo. Your version captured the entire table. Mine only started after the first column of 30 cells, A1:A30, picking up the remaining (column B and on), putting it starting in A31. A1:A30 would remain. Just a little lazy. My intent was to use the formulas to grab columns B:AD, and delete them after the formula cells had been converted to hard values with paste special - values. Lotsa ways to git 'r done (Larry the cable guy). -- Earl Kiosterud www.smokeylake.com "Max" wrote in message ... "Earl Kiosterud" wrote ... Put this in A31, and copy down. =OFFSET(A31,-(INT((ROW()-1)/30))*30,INT((ROW()-1)/30),1,1) Much neater, Earl ! Perhaps with just a typo corrected, in A31, copied down to A1530: =OFFSET(A31,-(INT((ROW()-1)/30))*30,INT((ROW()-1)/30)-1,1,1) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#8
|
|||
|
|||
"Earl Kiosterud" wrote:
.... Actually, it wasn't a typo .. Ah, I see it now. Sorry for the earlier mis-interp, Earl. I was wondering where the deuce the formula extract for A1:A30 went to <g -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#9
|
|||
|
|||
Max,
Actually, I think your change is the better, more generalized, solution. It gets the entire table, not just the remainder. A bit neater and cleaner. -- Earl Kiosterud Virginia Beach, VA USA, GMT-5 www.smokeylake.com "Max" wrote in message ... "Earl Kiosterud" wrote: ... Actually, it wasn't a typo .. Ah, I see it now. Sorry for the earlier mis-interp, Earl. I was wondering where the deuce the formula extract for A1:A30 went to <g -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#10
|
|||
|
|||
Thanks for the view !
Interesting site btw, and I don't mean just the Excel part of it <g Liked the short 20% demo on "Car wash" .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Count of Single Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
How to combine two columns? | Excel Worksheet Functions | |||
combine columns | Excel Worksheet Functions | |||
merge data from multiple columns to single column | Excel Worksheet Functions | |||
Convert one row and 50 columns of info to a single printable page | Excel Worksheet Functions |