Formula to return All Teams that.......

[Tony Steane]

Greetings,

I am trying to write a formula that will return the team name of ALL
teams that had Kitchen duty the day before they have Garden duty (and
various other combinations) Without using a macro!!!!

For example using a small sample of the data

A B C D E
1
2 Date Cleaning Garden Kitchen Day Off
3 12/05/2005 Team A Team B Team C Team D
4 13/05/2005 Team C Team D Team B Team A
5 14/05/2005 Team C Team B Team D Team A
6 15/05/2005 Team A Team D Team B Team C
7 16/05/2005 Team C Team D Team A Team B
8 17/05/2005 Team D Team A Team B Team C
9
10
etc
etc

I want to return in say cell D10 the names of the teams that meet my
criteria (ie Had Kitchen duty day before Garden duty) so cell D10 would
return "Team B Team D Team A".

I have managed to write a CSE formula to return the number of teams
that it applies to, using the example above in cell C10 I have

{=SUM(IF($C$4:$C$8=E3:E7,1,0))} which returns 3

Is what I'm trying to do possible?

Any solutions?

Cheers

Tony

 

[Bernie Deitrick]

Tony,

Yes, you can do it, but it isn't easy to do since concatenation isn't
enabled with array formulas - CSE formulas, as you describe them. You would
basically need to have an IF for each comparison - and that gets long with a
lot of cells.

Instead, it would be easy using a User-Defined-Function. For example, using
your sample range (by the way, your sample formula should have been
{=SUM(IF($C$4:$C$8=D37,1,0))} and not {=SUM(IF($C$4:$C$8=E3:E7,1,0))}),
if you copy the code below into a code module, and enter

=SameTeam(C4:C8,D37)

you will get

Team B Team D Team A

returned to the cell where you entered the formula.

HTH,
Bernie
MS Excel MVP

Function SameTeam(R1 As Range, r2 As Range) As String
Dim i As Integer
If R1.Cells.Count <> r2.Cells.Count Then
SameTeam = "Cell count inequality"
Exit Function
End If
For i = 1 To R1.Cells.Count
If R1(i).Value = r2(i).Value Then
SameTeam = SameTeam & " " & R1(i).Value
End If
Next i
SameTeam = Application.Trim(SameTeam)
End Function

 

[Tony Steane]

Bernie,

Many thanks for taking the time to look at my problem, and it was a
good
pick-up finding my coding error, I'd like to say I put it there to test
you, but
it was sloppy cut and pasting.

You are right with using IF for each comparision, as the actual range
is
350-400 rows long.

Your UDF solution is one that I was trying to avoid as the person I
intend
to give the spreadsheet to, refuses to open any spreadsheet with a
macro/udf
attached, that said I guess I will just have to create another column
and only
display the teams name in that column if there is a match, and he will
just
have to browse down the column manually.

Thanks for confirming my believe and for your effort.

Cheers

Tony