CONCATENATE in Array formula

[Abhay]

Hello!
I am trying to consolidate a text column in array formula. following is
formula.
{=CONCATENATE(IF(C2:C200=C2,F2:F200,""))}
If I replace CONCATENATE with SUM and column F with values it works
perfectly well. But, I want to join text values.
anyhelp is apprecaiated.
Regards,
Abhay

 

[Max]

Presuming you want to conditionally concat text in col E with that in col F
for values in col C = xxx (say), think you could try this multi-cell array

Select G2:G200
Paste this formula into the formula bar:
=IF($C$2:$C$200="xxx",$E$2:$E$200&" "&$F$2:$F$200,"")
then array-enter the formula by pressing CTRL+SHIFT+ENTER

The same formula will appear in every cell within G2:G200, but it'll return
the desired concat results on the lines where col C = xxx. Adapt to suit.

 

[Abhay]

Max,

I have multiple rows with one column common C (Three rows with "Abhay") &
Column F has A, B, C as project names in three rows.
End result I wish to have is a single row with Abhay and "A,B,C"

Regards,
Abhay

 

[Max]

Ah, I see. Think there's no single formula which can do that. Vba is needed.
Hang around awhile for responders conversant in vba to pitch in here.

 

[Max]

Tinkered with a sub by Tom Ogilvy re:
http://tinyurl.com/2leq2z
which seems to do what you're after

Try the adaptation below on a spare copy of your sheet
Col C must be sorted first before running the sub

Sub ConcatColF()
'adapted from Tom Ogilvy posting:
'http://tinyurl.com/2leq2z
'Col C must be sorted

Dim lastrow As Long
Dim i As Long
lastrow = Cells(Rows.Count, 3).End(xlUp).Row
i = lastrow
Do While i > 1
If Cells(i, 3).Value = Cells(i - 1, 3).Value Then
Cells(i - 1, 6).Value = Cells(i - 1, 6).Value & ", " & _
Cells(i, 6).Value
Cells(i, 3).EntireRow.Delete
End If
i = i - 1
Loop
End Sub


---

 

[Abhay]

Thanks Max. I thought may be some tricky way to handle using array formula.
What you have given addresses issue. regards,
Abhay

 

[Max]

welcome, Abhay. glad it helped.