Searching or Find question

[excelneophyte]

A have a list of participants in a charity cycling event that are all listed
in excel in order of their ride number. Their names and hometowns are in
adjacent columns.

I have a spotter 200y up from the finish who radios back to me at the
finish, the next group of riders about to finish.

Say for instance, 4 riders come around the corner together.... say 36, 49,
23, 11.... is there a way when they radio those numbers to me, that I can
quickly access all 4 of them in that group (in some type of list) so I can
announce their names as they cross the line? Or do I need to look up each
race number individually?

If it can't be done in an excel document, could it be done in some other
type of data filing program?

Thanks for any help....

 

[M Kan]

Here's a quick example I put together:
http://www.kan.org/tips/files/ride_roster_example.xls

You can create an entry area (up to 5 numbers in my example) and then use a
VLOOKUP to match those ride numbers with a specific rider. I use an
IF-ISERROR to turn those into "1's" and blanks. Using the Autofilter, you
can just filter for the 1's. You could also write a quick macro to clear the
5 names in prep for the next group of riders

 

[Billy Liddel]

I'd change M Kan's sheet slightly.

Create the table on a separate sheet than the one you want to lookup. Make
sure that the table is sorted on race numbers.

Create a range name for the area you want to enter the number to lookup.
Select the range and Type "Group" in the Address box to the right of the
formula bar. group is the name

Say this is in B2:B10

In C2 copy K Mays modified formula:
=IF(ISERROR(VLOOKUP($B2,$C$10:$E$29,1,0)),"",VLOOKUP($B2,$C$10:$E$29,2,0))

In D2 copy:
=IF(ISERROR(VLOOKUP($B2,$C$10:$E$29,1,0)),"",VLOOKUP($B2,$C$10:$E$29,3,0))

Note I use the Address of the table of the published workbook if the table
is in sheet2 you would need to include this in the formula.

=IF(ISERROR(VLOOKUP($B2,Sheet2!$C$10:$E$29,1,0)),"",VLOOKUP($B2,Sheet2!$C$10:$E$29,2,0))

Copy these formulas down to B10.

If you gave the table a range name as above then you could use this in the
formula instead of the range. Say the Name for the table is Riders the the
formula would be:

=IF(ISERROR(VLOOKUP($B2,Riders,1,0)),"",VLOOKUP($B2,Riders,2,0))

Finally you need a macro to clear the numbers quickly for the next group
without deleting the formulas by mistake.

Press Alt + F11 to open VB Editor> Insert Module and paste the following code.

Sub clearGroup()
Dim rng As Range
Set rng = Range("Group")
rng.ClearContents
End Sub


Close the VB Module, and return to Excel. You can run the macro from the
Tools Menu or press ALT +F8 and press run But this will take too long on the
day.

Choose View, Toolbars, Forms.

Click on Button, The 4th button on the toolbar and draw it on the worksheet.
You will be prompted to assign a macro so click on Cleargroup and click OK.

Finally practice someone calling out numbers entering the data and clearing
the data. You might want to keep it on the screen so that you can talk about
the names until the next group comes along.

Regards
Peter

 

[excelneophyte]

Peter & M Kan --

First thanks to both of you for getting back to me. Programming is like
reading Japanese to me, but I plan on getting with someone here in San
Antonio who will have a much better handle on what excel can do that I....
and I am taking all my notes when I meet with them.

But to clarify for me (since I can't understand the instructions below)....
if a person were to follow the directions given below...

Would the separate table created, when I type in the 5 riders coming towards
me, be able to magically replicate their names, hometimes, etc. into the
adjacent columns beside the numbers I just typed in so all 5 names are right
there together on one short list?

If that is what you are saying is possible, then I am on cloud nine. To
have to scroll through a list of over 5000 entrants at some of these events,
well you can imagine, using just the find button, I don't hit very high
percentages when it comes to calling out their names. I would love to be
able to recognize more, if not all, of the riders for the incredible effort
they put forth to raise money for this charity.

Again, thank you in advance for your patience and for clarifying the
capability of the table to which you refer to.

Karen

 

[Billy Liddel]

Karen

Yes, That is what VLOOKUP formulas do, extract information from the list. It
is like a policeman entering a car's registration into a database to find the
owner of that car.

The only thing to remember is that the list must be sorted by the Riders
number or the results may give an error. Put your cursor in the Numbers
column and click the A-Z Icon, or use the Data menu, Sort.

If you enter say 5001 by mistake when there are only 5000 entrants the
formula returns a blank cell (because that entrant does not exist).

Happy typing and best of luck

Peter

 

[excelneophyte]

Oh my gosh!!!! I did it!!!!!! On my own -- with your cut and paste
instructions - how could I go wrong? (well, I did on a few things - but that
was my ignorance) You have helped us so, so, much. I hope that when the
"real event" comes -- I will be able to do this again.

What I did today was to "modify" the first table sent by M. Kan. It is
unbelievable how much fun it is to play with this setup. When the event
organizers give me the "real list".... I may be back on here for more help.

You are both worth a million.... Thanks so much!!