Match highest with lowest until all matches have been made

[Eph]

The data I have is in three columns sorted from highest to lowest on the average in column C. i.e.

Player
Number Name Average
1 Alice 9.73
2 Bob 9.44
3 Carol 8.97
4 Dan 8.76
.
.
.
152 Holly 8.33
153 Jill 8.01
154 Mike 7.55
155 Zach 7.32

What I need to do is create a list of teams so that the finished result would look like this in three columns: i.e.

Team
Number Name Name
1 Alice Zach
2 Bob Mike
3 Carol Jill
4 Dan Holly

Please note that the player with the highest average is always paired with the player with the lowest average until all pairings have been made.
I'll have no idea until about 1 hour before the event who will be entering and changes may occur at the last minute so I'd like to make this as simple as possible.
In case you are wondering, I am trying to organize a Scotch Doubles pool tournament and may get about 60 entrants of a possible 155 or so.

Thanks,
Eph

[FDibbins]

I think this is what you need. copy it into D2, and copy down...

=IF(ROWS($B$2:B2)/2<COUNTA(B2:$B$9)/2,INDEX($B$2:$B$9,MATCH(LARGE($C$2:$C$9,ROW(A1)),$C$2:$C$9,0),1)&" & "&INDEX($B$2:$B$9,MATCH(SMALL($C$2:$C$9,ROW(A1)),$C$2:$C$9,0),1),"")

[Moo the Dog]

How about something like this (see attached sheet).

- Moo

[Eph]

Hi Moo,

Very close but I can't remove player numbers from column A without causing an error. I need to be able to allow for players who do not wish to enter the tournament.

Thanks,
Eph

[Moo the Dog]

Eph,

#1 - Thanks for the rep.
#2 - I've been trying to work this out using a formula, but with potential blanks making a mess of things, it looks like VBA/macro is going to be the way to go - and I am what one might call "a beginner" at VBA right now. Hopefully someone on the boards who is a true guru with VBA can assist.

- Moo

[FDibbins]

Eph, did you take a look at the option i suggested?

[Eph]

Hi FDibbins,
I did take a look at it and it seems to work well but I need the output in 3 columns whereas your formula sends it to one column; I'm not sure how to take your formula and change it to do that.
Thank you for your reply.
Eph

[FDibbins]

not sure where the 3rd column comes in, but this will give you 2 of them...
=IF(ROWS($B$2:B2)/2<COUNTA(B2:$B$9)/2,INDEX($B$2:$B$9,MATCH(LARGE($C$2:$C$9,ROW(A1)),$C$2:$C$9,0),1)
=INDEX($B$2:$B$9,MATCH(SMALL($C$2:$C$9,ROW(A1)),$C$2:$C$9,0),1),"")

[Eph]

I think I solved this to my satisfaction but before I post my solution I'll need a little more help.

I would like to validate Col B and ensure that any number the user enters is an exact match as the whole number in the same row in Col A.
I've hidden column A which has whole numbers from 1 to 100i.e.
Col A
1
2
3
4
5
.
.
.
100

I've started validating each cell in Col B but there must be an easier way to validate a range of cells instead of doing one at a time.

Thanks