Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Combining workshhets with different number of colums and column wi
I need to combine four small text-only spreadsheets into one. Each of the
four have different amounts of columns, as well as different column widths. There has got to be a way to do this in Excel on one sheet, isn't there? |
#2
|
|||
|
|||
One formulas play to tinker with ..
Assume the source data are in 4 sheets named as: Sheet1, Sheet2, Sheet3, Sheet4 And for the 4 sheets, the max row is row10, max col is col C i.e. all data within any sheet would lie within the range A1:C10 In a sheet: Summary ------- Put in A1: =IF(OFFSET(INDIRECT("Sheet"&INT((ROWS($A$1:A1)-1)/10)+1&"!$A$1"),MOD(ROWS($A $1:A1)-1,10),COLUMNS($A$1:A1)-1)=0,"",OFFSET(INDIRECT("Sheet"&INT((ROWS($A$1 :A1)-1)/10)+1&"!$A$1"),MOD(ROWS($A$1:A1)-1,10),COLUMNS($A$1:A1)-1)) Copy A1 across to C1, fill down to C40 (10 rows per sheet x 4 sheets = 40 rows to copy down) The above will extract and "stack" data from Sheet1's A1:C10, then from Sheet2's A1:C10, and so on, viz: Sheet1's A1:C10 Sheet2's A1:C10 Sheet3's A1:C10 Sheet4's A1:C10 Copy paste special formats over, resize column widths, etc manually Adapt to suit: Change the number "10" within the OFFSET to a number equal to the max rows to be extracted over from each sheet. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "TX DPS" <TX wrote in message ... I need to combine four small text-only spreadsheets into one. Each of the four have different amounts of columns, as well as different column widths. There has got to be a way to do this in Excel on one sheet, isn't there? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions | |||
How do I sort a column a unique number? | Excel Worksheet Functions | |||
Convert week number into calendar month? | Excel Worksheet Functions |