Hi,

I was wondering if anyone could help me with this problem. I am a
track coach and I created a matrix to list athletes and events. I was
wondering if you could write a formula to scan a row of events and find
an event then display the athlete in a new column list by event.

Like this

Name Event 1 Event 2 Event 3 Event 4
Bob 100 200 1600 3200
Jim 400 100 1600 200
Larry 110 200 400 3200


Show a List as follows

100 200 400 1600 3200
Bob Bob Jim Bob Bob
Jim Jim Larry Jim Larry
Larry

Thank you for the help.

I have tried vlookup, hlookup, match and index with little success.


--
grizzly1733
------------------------------------------------------------------------
grizzly1733's Profile: http://www.excelforum.com/member.php...o&userid=33069
View this thread: http://www.excelforum.com/showthread...hreadid=528822

As per the attached,

=IF(ISNA(MATCH(B$8,$B3:$E3,0)),"",$A3)

Hope this helps



grizzly1733 Wrote:
Hi,

I was wondering if anyone could help me with this problem. I am a
track coach and I created a matrix to list athletes and events. I was
wondering if you could write a formula to scan a row of events and find
an event then display the athlete in a new column list by event.

Like this

Name Event 1 Event 2 Event 3 Event 4
Bob 100 200 1600 3200
Jim 400 100 1600 200
Larry 110 200 400 3200


Show a List as follows

100 200 400 1600 3200
Bob Bob Jim Bob Bob
Jim Jim Larry Jim Larry
Larry

Thank you for the help.

I have tried vlookup, hlookup, match and index with little success.


+-------------------------------------------------------------------+
|Filename: Contestant.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4562 |
+-------------------------------------------------------------------+

--
Door
------------------------------------------------------------------------
Door's Profile: http://www.excelforum.com/member.php...o&userid=33068
View this thread: http://www.excelforum.com/showthread...hreadid=528822

Thanks Door,

It works

you're awesome

matt

P.S. Any chance you could explain how it works?


--
grizzly1733
------------------------------------------------------------------------
grizzly1733's Profile: http://www.excelforum.com/member.php...o&userid=33069
View this thread: http://www.excelforum.com/showthread...hreadid=528822

A3:E5 is your data in the form Person, Event, Event, Event etc

B10:H12 is a row for each contestant (from A3:A5) and a column for each
event (the purple cells, you need to type in each event name)

=IF(ISNA(MATCH(B$8,$B3:$E3,0)),"",$A3) to
=IF(ISNA(MATCH(H$8,$B5:$E5,0)),"",$A5)

If(ISNA is to ignore any #N/A items

Match says find B$8 (the event) in $B$3:$E$3 (along the row 3 from B to
E)

,"", is the 'true' for ISNA - put blank where the event is not found
on the row

,$A3 is pick up the Name from column A or the row in question

the $ is so that when the formula is dragged along the row, and then
downwards for the required number of rows, the correct cell references
will remain.

If a contestant enters more events try
=IF(ISNA(MATCH(B$8,$B3:$Z3,0)),"",$A3)
where $Z is the last column to search.
(put that in B10 and formula-drag it over all required cells/rows)

You can insert more contestant lines (at line 6, Insert) but then
remember to copy the formula, currently B12:H12 down more rows (Select
B12:H12, and drag the small + in the bottom right corner of the
highlight to repeat the formulas on more rows)
You need at least as many rows as you have contestants, more rows will
simply reflect blanks (ie, it is better to have more rows in the second
block than less rows)

Hope this helps.



grizzly1733 Wrote:
Thanks Door,

It works

P.S. Any chance you could explain how it works?


--
Door
------------------------------------------------------------------------
Door's Profile: http://www.excelforum.com/member.php...o&userid=33068
View this thread: http://www.excelforum.com/showthread...hreadid=528822