Excel/From rows and columns to one column
I have data for each hour over a whole year. The hours go from 1 to 24 columns and days 365 rows down. I need to get all the cells in one column so that the first hour of the second day comes right after the last hour of the first day and so on.
I want to get this: 1 2 3
4 5 6
7 8 9
(So 1 2 3 would represent the first day and 4 5 6 the second)
into this: 1
Can you please help me?
Assume you have numbers or daa in cells A1:X365 on Sheet4 and you want these all in column 1 of another sheet.
first you need to be able to have the columns references count from 1 to 24, then repeat. You can do that with this formula
Now that is dependent on the scenario I stated. If you don't want to tie it to the physical row you are in, you can put in A1 as an argument to row
that is an adjustment for the destination sheet. If you want to start at a different column in the source sheet, you would change the +1 to reflect that column.
Now we need a formula that will reference the first row in the source sheet for 24 rows in the destination sheet.
so we can combine these with address
to produce the address of the cell in the source sheet (Sheet4) that we want to appear in the destination sheets
Now to actually pull over that value, we would put that in the Indirect Function
that produced the formula
in cell A1.
I copied it down to cell A8760 and it produced the 365*24 values in a single column.
I can then select that column, do copy, then paste special values and replace the formulas with the values they display. (if you want to do that).
Adjust to fit your actual situation.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here