2 Row's into 1 Row

[martinoconnor]

I have 2 rows of data in a spreadsheet which i need as 1 row, it's pretty simple to achieve this if i didn't have a few thousand row's to deal with.

Example of current data laytout

January 28th 1989
1 17 18 30 33 35
January 21st 1989
3 8 14 17 27 32
January 14th 1989
6 7 12 25 29 30
January 7th 1989
1 3 5 6 15 34
December 29th 1990
6 7 10 26 31 35
December 26th 1990
3 7 14 17 27 33
December 22nd 1990
5 16 20 21 32 35

Required Layout, 9 Col by 7 Row's

January 28th 1989 1 17 18 30 33 35
January 21st 1989 3 8 14 17 27 32
January 14th 1989 6 7 12 25 29 30
January 7th 1989 1 3 5 6 15 34
December 29th 1990 6 7 10 26 31 35
December 26th 1990 3 7 14 17 27 33
December 22nd 1990 5 16 20 21 32 35

[Pete_UK]

It would help if you attached a sample Excel workbook, so we can see exactly which columns you are using at the moment.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

[KOKOSEK]

Are these data all in Col. A? Or date and 1 number from next row in in col. A and the rest numbers are in B,C,D,E,F?

[Richard Buttrey]

Are your dates one text in column A or or are the month, date and year in columns A:C?
Are there always 6 numbesrs for each date?
Are the numbers always in columns A:F?

Remember it's always easier for us if you help by uploading a sample of your data in the workbook rather than just some text.

[martinoconnor]

Hopefully the attachment has worked

[Richard Buttrey]

Do your results therefore occupy 9 cells on a row? i.e. 3 for the date and 6 for the numbers? You didn't confirm whether there are always 6 numbers.

[martinoconnor]

3 cells for the date and 6 cells for the other numbers

[Richard Buttrey]

One way

In H1 copied to J7

Please Login or Register  to view this content.

In K1 copied to P7

Please Login or Register  to view this content.

[Pete_UK]

If you have a few thousand rows to deal with, you might find the OFFSET solution makes the file become a bit sluggish, as it is a volatile function. To avoid this, you can use INDEX, as follows:

H1: =INDEX(A:A,INT((ROWS($1:1)-1)*2)+1)

Copy across into I1:J1. Put this formula in K1:

=INDEX(A:A,INT((ROWS($1:1)-1)*2)+2)

and copy across into L1:P1.

Then copy the formulae from H1:P1 down as far as required (until you just get rows of zeros).

Hope this helps.

Pete