Ability of lookup?

[penguinspeaks]

I am not sure if I will be able to do what I want to do is pair I want, but let me try to explain.

I have a list of names in a column. The column to the left will record numbers drawn by the person in the column to the right.

This is to pair up people for a tournament in case you are wondering.
Moving on. When a player draws a number, the number is recorded in the left column. Say this number is 4. Players continue to draw numbers and they are recorded and so on. What I want to do is pair the players with the same number. So if BOB drew 5 first, then TOM drew 5 later, I want a field 2 columns to the right to show BOB / TOM in a single field. I know how to merge two different fields to show the names and the / but I do not know how to retrieve the names based on the same number.

If anyone understands what I am trying to do, please, if you can, help me with this.

TIA

Bam
OpenOffice 3.1 on Windows Vista
Bambamn

 

[SkipVought]

I'd know exactly how to accomplish this in Excel.

But why don't your SORT on the column that contains this number. Your 2 rows will be adjacent, that is assuming that there will be 2 and only 2 rows per number.

If that's not enough (the sort is a necessary step of you proceed furtner) you can use the OFFSET fuction to display the 2 players in the same row in a separate table, where you would have one row for each grouping number.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[penguinspeaks]

I can use the sort, but then what?
I guess I could then use data from the specific cells since they would always be located in the same position.

Let me look into this a bit. Thanks for the ideas.

 

[SkipVought]

Post 1) some representative sample source data and 2) corresponding report data based on the group number and expected return values for players.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

If this is your source table
[tt]
A B
1 skip
1 sam
2 fred
2 don
3 al
3 len
[/tt]
Let's suppose that your group numbers are in column E

[b]
E  F     G
   1     2[/b]
1  skip  sam
2  fred  don
3  al    len

The formula in F2
[tt]
F2: =INDEX($B:$B,MATCH($E2,$A:$A,0)+F$1-1,1)
[/tt]
copy F2 'n' paste to all appropriate rows in column F & G


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[penguinspeaks]

I see where you are going with this.

I know it seems dumb, but I do not want to have to sort this. The reason is this: this will be shown on a large screen for the entire place to see. I want them to see things as they happen. Their name is in a place that depends on the position they signed up. They draw a number which is recorded to the left of their name. Then the table to the right shows their name with their partner.

It seems strange but anything that may happen with a sort could cause them to think the tourney is rigged or fixed or something. People are like this. THis is why nothing can change on the screen... I hope that makes sense.

 

[SkipVought]

okay, here's a solution that does not require sorting the source data.

Here's the output result starting in column E

[b]
E  F  G    H[/b]
      1    2
1  5  sam  skip
2  2  don  fred
3  1  al  len

This is the formula in F2 copied to rows in column F
[tt]
F2: =MATCH($E2,$A:$A,0)
[/tt]
This is the forumla in G2 copied to rows in column G
[tt]
G2: =INDEX($B:$B,$F2,1)
[/tt]
This is the formula in H2 copied to rows in column H
[tt]
H2: =INDEX(OFFSET($A$1,$F2,1,COUNTA($A:$A)-$F2,1),MATCH($E2,OFFSET($A$1,$F2,0,COUNTA($A:$A)-$F2,1),0),1)
[/tt]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[penguinspeaks]

I am attaching my sheet. Please try to work you example into this at least the first row.
Of course with OO you replace the , with ; in the formulas.

 

[SkipVought]

You did not attach your sheet! You attached a PICTURE of your sheet!

Well you already have enough specific information to infer a solution.

What have you tried with your specific sheets? What results did you get?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[penguinspeaks]

Ok. This is what I did and it works.

I assign the number each player draws which gives me pairs of numbers from 1 through half of the amount of players.
Then I wrote a macro that copies the player with their drawn number to another table. Here I sort it by the drawn number asc and then I put each player in order in the table I already have. It works perfectly. I will still be working on this to get it working how I want it to, but for now I have a band aid on it and it runs fine.

Thanks for your idea on a sort, I wouldn't have thought of doing this way with out that.

Thanks

Bam