If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Arrays
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 |
#2
|
|||
|
|||
Arrays
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 -- Regards Frank Kabel Frankfurt, Germany ryan wells wrote: 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 |
#3
|
|||
|
|||
Arrays
"Frank Kabel" wrote...
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$1 00, MATCH($A1,Sheet1!$A$2:$A$100,0),0)="x",Sheet1!$B$1 :$X$1,"") &" "))," ","; ") |
#4
|
|||
|
|||
Arrays
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$1 00, 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 |
#5
|
|||
|
|||
Arrays
"Frank Kabel" wrote...
.... 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. |
#6
|
|||
|
|||
Arrays
Harlan Grove wrote:
"Frank Kabel" wrote... ... 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 |
Thread Tools | |
Display Modes | |
|
|