You are here:

# Excel/From rows and columns to one column

Question
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
2
3
4
5
6
7
8
9

Thanks,

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

=MOD(ROW()-1,24)+1

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

=MOD(ROW(A1)-1,24)+1

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.

=TRUNC((ROW(A1)-1)/24)+1

does that.

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).

--
Regards,
Tom Ogilvy
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Thanks Tom, you're the best!

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

#### Tom Ogilvy

##### Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

##### Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.