Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Ability of lookup?

Ability of lookup?

Ability of lookup?

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.


OpenOffice 3.1 on Windows Vista

RE: Ability of lookup?

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.


glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Ability of lookup?

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.

RE: Ability of lookup?

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


glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Ability of lookup?

If this is your source table


1 skip
1 sam
2 fred
2 don
3 al
3 len

Let's suppose that your group numbers are in column E


E  F     G
   1     2
1  skip  sam
2  fred  don
3  al    len 
The formula in F2

F2: =INDEX($B:$B,MATCH($E2,$A:$A,0)+F$1-1,1)

copy F2 'n' paste to all appropriate rows in column F & G


glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Ability of lookup?

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.

RE: Ability of lookup?

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

Here's the output result starting in column E


E  F  G    H
      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

F2: =MATCH($E2,$A:$A,0)

This is the forumla in G2 copied to rows in column G

G2: =INDEX($B:$B,$F2,1)

This is the formula in H2 copied to rows in column H

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)


glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Ability of lookup?

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?


glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Ability of lookup?

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.



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close