With you data in Sheet1 ColA to ColAA; try the below formulas

In Sheet2 cell A1 enter the below formula and copy/drag down as required
=INDEX(Sheet1!A:A,(FLOOR(ROW(B1)-1,26)/26)+1)

In Sheet2 cell B1 enter the below formula and copy/drag down as required
=OFFSET(Sheet1!$B$1,(FLOOR(ROW(A1)-1,26)/26),MOD(ROW(A1)-1,26))


If this post helps click Yes
---------------
Jacob Skaria


"Buyone" wrote:

Hello,

Unfortunately that's the way i've been doing it, and takes me ages as I have
to do it for each row individually.

The data i get through comes for a number of dates and the times are not a
consistant number, typically they range from 1-18 times per day.

So i need a way of automating the number of rows to match the columns and
move the next set of data down automatically so it stays in date order.

Apologies, that may not be the clearest explanation but it's the best I have.

Thanks

"Ms-Exl-Learner" wrote:

Yes use the Transpose option to do it.

Just insert a column in between A&B Column. Now your data will start from C
column and end on column AB. Now select the data from Column C to Column AB
and do copy. Place the cursor in B Column (i.e. newly inserted column) and
do Right ClickPaste Special€¦Check the Transpose Field.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Buyone" wrote:

Hello,

I have a set of data that i need to modify.
A B C D E ......AA
14-Oct 03:23 04:23 06:20 06:34

I need to transpose B:AA into a single column while keeping the date in A.

So it should look like

A B
14-Oct 03:23
14-Oct 04:23
14-Oct 06:20

This data goes on for a number of rows so doing it manually (as i have been
doing) can take hrs.

Can anyone save me from my excel hell?

Thanks in advance