Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Concatenate Multiple Instances in Array
I searched the forums, but didn't see anything quite what I need. I am using Excel to handle hourly assignments, and I want to be able to note when more than one person has a meal. Each person has a row; each hour has a column. Like so: A B C D E F G Name 10 11 12 13 14 15 Bob R M B Harry R M R Jenny B M What I want is to be able in the D column to list that BOTH Bob AND Jenny are at lunch--but I can't figure out how. The MATCH function only gets the first entry; I have seen some array formulas that combine MATCH and INDEX, but can't get my head around the ideas. Help! -- sunfish62 ------------------------------------------------------------------------ sunfish62's Profile: http://www.excelforum.com/member.php...o&userid=24541 View this thread: http://www.excelforum.com/showthread...hreadid=381824 |
#2
|
|||
|
|||
Hi!
I plugged your sample data into a sheet and the range it occupied is: A1:G4 Enter this formula in B6 as an array using the key combo of CTRL,SHIFT,ENTER: =IF(OR(COUNTIF(B$2:B$4,"M")<2,ISERROR(SMALL(IF(B$2 :B$4="M",ROW($1:$3)),ROW(1:1)))),"",INDEX($A$2:$A$ 4,SMALL(IF(B$2:B$4="M",ROW($1:$3)),ROW(1:1)))) Copy across to G6 then down to G8. If your actual data set is larger than what you posted (and I'm sure it is), then you need to change each instance of this portion of the formula: ROW($1:$3) If you have 10 names in column A then that would mean you have 10 rows of data (assuming there are no empty rows) so you would need to change ROW($1:$3) to ROW($1:$10). Then, when you copy down you need to copy to enough rows so that every possible instance that meets the conditon will be returned. In other words, how many people could be having a meal at the same time? 5? 20? That's how many rows down you would need to copy the formula to. Biff "sunfish62" wrote in message ... I searched the forums, but didn't see anything quite what I need. I am using Excel to handle hourly assignments, and I want to be able to note when more than one person has a meal. Each person has a row; each hour has a column. Like so: A B C D E F G Name 10 11 12 13 14 15 Bob R M B Harry R M R Jenny B M What I want is to be able in the D column to list that BOTH Bob AND Jenny are at lunch--but I can't figure out how. The MATCH function only gets the first entry; I have seen some array formulas that combine MATCH and INDEX, but can't get my head around the ideas. Help! -- sunfish62 ------------------------------------------------------------------------ sunfish62's Profile: http://www.excelforum.com/member.php...o&userid=24541 View this thread: http://www.excelforum.com/showthread...hreadid=381824 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting/look up data from a list and select multiple instances | Excel Worksheet Functions | |||
Multiple instances across monitors? | Excel Discussion (Misc queries) | |||
Why are there multiple instances of same person on shared workshee | Excel Discussion (Misc queries) | |||
opening multiple instances of excel | Excel Discussion (Misc queries) | |||
Array with multiple results | Excel Worksheet Functions |