formula for combining multiple dates, from columns to rows

Somewhere else on your sheet put:

=LARGE($A$1:$D$3,ROW(A1))

And copy down

Hi Andrew-R, thank you for the response. I tried what you said but it did not work. I edited my post so it can be more clear, thank you!

I think I'm with you, but it would be much easier if you could post a sample workbook.

Hi Andrew-R, thank you for the reply, I jus added the workbook, the day must change automatically when copied down.

OK, in cell F2 put the formula:

=OFFSET($A$2,INT((ROW(A1)-1)/3),MOD(ROW(A1)-1,3))

And copy down

Originally Posted by Andrew-R

OK, in cell F2 put the formula:

=OFFSET($A$2,INT((ROW(A1)-1)/3),MOD(ROW(A1)-1,3))

And copy down

Thank you! that worked really good! I have another question:

I already had a similar formula: =OFFSET($C$2,INT((ROW()-1)/2),MOD(ROW()+1,2)) but ii only works for only two columns and the one that you gave me works for three columns but it is hard to understand the formula. Could you explain the formula so i can add "n" number of culumns?

Originally Posted by juliomanco

I already had a similar formula: =OFFSET($C$2,INT((ROW()-1)/2),MOD(ROW()+1,2)) but ii only works for only two columns and the one that you gave me works for three columns but it is hard to understand the formula. Could you explain the formula so i can add "n" number of culumns?

The function OFFSET(<start cell>,<rows>,<columns>) returns the value that is in the cell <rows> down and <columns> across from the starting point, so OFFSET(A1,2,1) returns the value that is in cell B3 (two rows down and one row across from A1)

The Rows() formula just returns the row that the reference cell is in - we start that at A1 and Excel updates it when we copy the formula down a row to A2, to A3 when the formula is copied down 2 rows and so on. So, basically, it's just a counter for how many copies of the formula we have.

The OFFSET starts in the top left cell of your data and so we initially want the offset to be zero rows and zero columns, in the next row down we want it to be zero rows and one column, then zero rows and two columns and so on. So it moves across one column for every copy of the formula, looping back round to zero when it exceeds the number of columns of data, and moving down a row.

So, in the formula, the numbers I've put *'s around tell it how many columns of data there are:

=OFFSET($A$2,INT((ROW(A1)-1)/*3*),MOD(ROW(A1)-1,*3*))

As long as A2 is the top-left corner of your data you can set that number to however many columns you have and the formula will work.