CONCATENATE in Array formula
 

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

CONCATENATE in Array formula
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abhay" wrote:
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

CONCATENATE in Array formula
 

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" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abhay" wrote:
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

CONCATENATE in Array formula
 

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abhay" wrote in message
...
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

CONCATENATE in Array formula
 

Tinkered with a sub by Tom Ogilvy
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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

CONCATENATE in Array formula
 

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


"Max" wrote:

Tinkered with a sub by Tom Ogilvy
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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

CONCATENATE in Array formula
 

welcome, Abhay. glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abhay" wrote in message
...
Thanks Max. I thought may be some tricky way to handle using array
formula.
What you have given addresses issue. regards,
Abhay