Arrays

[ryan wells]

Hello...

I was be so grateful if some Excel Expert could offer a solution to
this problem I have understanding how to handle arrays in Excel.

I have a worksheet contains a list of events down the page [in column
one], and a list of athletes aross the page [in row one].

I need to note which athletes will compete in which events by placing
a character in the corresponding cell to make an array of data showing
athlete participation in events.

Athletes can be in more than one event.

I need to then work out how to write a formula or function to generate
a list from this array showing which athletes are in which event, ie.
a (semi-colon) delimited cell per event containing all the (summary
of) athlete names in that event, eg.

Cell 1,1: "400m" Cell 1,2: "Arthur Burke; Audrey Smith; Betty Brown"
Cell 2,1: "800m" Cell 2,2: "Arthur Burke; Betty Brown; Charlie Smith"

etc.

Thanks!
Ryan

 

[Frank Kabel]

Hi
it will require some VBA. below one way:
1. Copy column A on a separate sheet (containing all events):

2. Download some add-ins to help you combine the data:
- download Alan Beban's array functions:
http://home.pacbell.net/beban
- download the free add-in Morefunc.xll
http://longre.free.fr/english/


3. Now enter the following formula in sheet2, in cell B1:
=MCONCAT(HLOOKUPS($A1,'sheet1'!$A$1:$X$100,ROW(2:2)),", ")
and copy this down

 

[Harlan Grove]

it will require some VBA. below one way:
1. Copy column A on a separate sheet (containing all events):

2. Download some add-ins to help you combine the data:
- download Alan Beban's array functions:
http://home.pacbell.net/beban
- download the free add-in Morefunc.xll
http://longre.free.fr/english/

3. Now enter the following formula in sheet2, in cell B1:
=MCONCAT(HLOOKUPS($A1,'sheet1'!$A$1:$X$100,ROW(2:2)),", ")
and copy this down

....

First, OP wants a semicolon-separated list of participants in each event,
not a comma-separated list. Second, OP would be entering some character,
like 'X', in the grid formed by the events in the first column and the
athletes in the first row, then using those characters to create the list of
participants in each event. Given those particulars, your formula doesn't
come close to doing anything useful. Is your HLOOKUPS call supposed to be
referring to an event in $A1? If so, wouldn't you need a VLOOKUPS call?

This requires neither VBA nor Alan Beban's library. It does require
MOREFUNC.XLL or some other add-in that provides a function like
MOREFUNC.XLL's MCONCAT.

=SUBSTITUTE(TRIM(MCONCAT(IF(INDEX(Sheet1!$A$2:$X$100,
MATCH($A1,Sheet1!$A$2:$A$100,0),0)="x",Sheet1!$B$1:$X$1,"")
&" "))," ","; ")

 

[Frank Kabel]

Hi Harlan

Harlan Grove wrote:
....

First, OP wants a semicolon-separated list of participants in each
event, not a comma-separated list. Second, OP would be entering some
character, like 'X', in the grid formed by the events in the first
column and the athletes in the first row, then using those characters
to create the list of participants in each event. Given those
particulars, your formula doesn't come close to doing anything
useful. Is your HLOOKUPS call supposed to be referring to an event in
$A1? If so, wouldn't you need a VLOOKUPS call?

This requires neither VBA nor Alan Beban's library. It does require
MOREFUNC.XLL or some other add-in that provides a function like
MOREFUNC.XLL's MCONCAT.

=SUBSTITUTE(TRIM(MCONCAT(IF(INDEX(Sheet1!$A$2:$X$100,
MATCH($A1,Sheet1!$A$2:$A$100,0),0)="x",Sheet1!$B$1:$X$1,"")
&" "))," ","; ")

thanks for the correction. Missed the semicolon part and forgot to
replace $A1 with a character. So my solution should have been:
=MCONCAT(HLOOKUPS("x",'sheet1'!$A$1:$X$100,ROW(2:2)),"; ")

Though your solution is better as it requires only Morefunc.xll
Frank

 

[Harlan Grove]

thanks for the correction. Missed the semicolon part and forgot to
replace $A1 with a character. So my solution should have been:
=MCONCAT(HLOOKUPS("x",'sheet1'!$A$1:$X$100,ROW(2:2)),"; ")

....

No. You approach doesn't work. Back to the OP's specs: "I have a worksheet
contains a list of events down the page [in column
one], and a list of athletes aross the page [in row one]."

What do you get from HLOOKUPS? Doesn't HLOOKUPS work similar to HLOOKUP by
comparing its 1st argument to all entries across all columns in the first
row of its 2nd argument? What's in its 1st row? Athletes' names. So would
there ever be any matches using your proposed LOOKUPS call? Only if some
athlete were named 'x' (and only 'x', so 'X,Malcolm' wouldn't be matched).
Then there's the fact that now you're not matching the event.

You obviously haven't tested this, and you obviously can't see where you're
screwing up. You need to match the event first, then you need to find all
x's in the row corresponding to that event, then return the athletes' names
corresponding to those x's. You need two matching operations, not one.

 

[Frank Kabel]

thanks for the correction. Missed the semicolon part and forgot to
replace $A1 with a character. So my solution should have been:
=MCONCAT(HLOOKUPS("x",'sheet1'!$A$1:$X$100,ROW(2:2)),"; ")

...

No. You approach doesn't work. Back to the OP's specs: "I have a
worksheet contains a list of events down the page [in column
one], and a list of athletes aross the page [in row one]."

What do you get from HLOOKUPS? Doesn't HLOOKUPS work similar to
HLOOKUP by comparing its 1st argument to all entries across all
columns in the first row of its 2nd argument? What's in its 1st row?
Athletes' names. So would there ever be any matches using your
proposed LOOKUPS call? Only if some athlete were named 'x' (and only
'x', so 'X,Malcolm' wouldn't be matched). Then there's the fact that
now you're not matching the event.

You obviously haven't tested this, and you obviously can't see where
you're screwing up. You need to match the event first, then you need
to find all x's in the row corresponding to that event, then return
the athletes' names corresponding to those x's. You need two matching
operations, not one.

Hi Harlan
yes I screwed this up. Thanks for the correction. Misread the specs
twice :-(
Frank