CONCATENATEIF ?

Posted by Dan on December 07, 1999 5:28 PM

I'm looking for something like
=CONCATENATE(IF(A1="apple",B1),IF(A2="apple",B2),...,IF(A100="apple",B100))

Ideally I'd like to do something like =CONCATENATE(IF(A:A="apple",B:B)

I've read and, for the most part, understand CSE functions and those seem to work great on things that return a single value. I'm not sure if they apply here.

Thanks in advanced.
Dan

Posted by kaiowas on December 08, 1999 3:14 AM

Add in an extra column an put =IF(A1="apple",B1,"") in the first row of this column then put =IF(A2="apple",C1&B2,C1) in row 2. Autofill this down the rest of your list. The value you want should then be in the last cell of the new column.

Posted by Chris on December 08, 1999 5:40 AM

Dan,

Put this function in a module:

Function ConcatenateIf(iRange As Range, iLook As String, iNum As Integer)
For Each cell In iRange
If cell.Value = iLook Then
ConcatenateIf = ConcatenateIf & cell.Offset(0, iNum).Value
End If
Next cell
End Function

Then use the following formula to return the value:

=concatenateif(A:A,"apple",1)

HTH,

Chris

Posted by Dan on December 09, 1999 4:32 PM

Thanks!

That was exactly what I was looking for! Thank you very much. I really appreciate it.

-Dan