Concatenate Multiple Instances in Array

[sunfish62]

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!

 

[Biff]

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