Help from expert needed with excel sheet please !

**will2k** · 09-30-2012, 06:29 AM

HI All, Any help for a novice appreciated

Q1, Please see attached file (september help) i would like to look at the data in the A & B Columns to be combined
eg to look like this below :

White Coppice
Becky Lou
Summer Dream
Zero Game
Face The Problem
Rowe Park

so i need them in order like
A2
B2
A3
B3
A4
B4

I have about 2000 of these cells to shuffle into this order so any help would be appreciated on this
from all you excel wizards out there !

Thanks in Advance

**Pete_UK** · 09-30-2012, 06:54 AM

You can put this formula in C2:

=INDEX(A:B,INT((ROWS($1:1)-1)/2)+2,MOD(ROWS($1:1)-1,2)+1)

and copy down.

Note, though, that a formula will not be able to return the formatting of the original cells.

Hope this helps.

Pete

**will2k** · 09-30-2012, 07:23 AM

Thanks Pete , That was what i was looking for apart from i need the formatting (colour of font ) but i can get around this by applying your 1st supplied formula
but applying it to the attached file , this then removes the need for the later sorting using coloured fonts .
Is it possible to supply the formula i need for sorting now with the extra columns in the attached file please
you are a star and have saved me hours if you could help

....How do i attach the updated file ?

**will2k** · 09-30-2012, 07:31 AM

Hi Pete , sorry attached file now !

**Pete_UK** · 09-30-2012, 07:38 AM

Do you just want the names from columns A and D, or do you want the odds and places as well?

Pete

**will2k** · 09-30-2012, 07:41 AM

Hi Pete , odds and places also
regards matt

**Pete_UK** · 09-30-2012, 07:46 AM

Okay, put this formula in H2:

=INDEX(A:D,INT((ROWS($1:1)-1)/2)+2,3*MOD(ROWS($1:1)-1,2)+1)

Then you can copy this into I2 and J2. Then modify the formula in J2 to this:

=IF(INDEX(C:F,INT((ROWS($1:1)-1)/2)+2,3*MOD(ROWS($1:1)-1,2)+1)="","",INDEX(C:F,INT((ROWS($1:1)-1)/2)+2,3*MOD(ROWS($1:1)-1,2)+1))

so that you get blanks if the original is blank. Then you can copy these down as far as you need to.

Hope this helps.

Pete

**will2k** · 09-30-2012, 07:59 AM

1 word ! LEGEND

**Pete_UK** · 09-30-2012, 09:01 AM

Well, I suppose that means it worked for you !!

As you are a new poster, you might need to be reminded that you should mark your thread as Solved if you consider it to be so - the FAQ describes how you can do this. Also, it you want to pass on thanks to any contributor who has helped you, you can click on the "star" icon in the bottom left corner of any post that has helped.

Pete