hi,

i would like to have filtered output in concatenate, i don't know how i can
do this :(

col A has make of Car's and col B has Models and i would like to
concatenanate all the filtered models in one cell of col C.

i am stuck with this.

In what format do you want the output?
how many rows do you have?
if not many and
if all you want is a space between the models
=if(A1=Car,B1&" ","")&if(A2=Car,B2&" ",""& ...
or set up a helper column (C) with
=if(A1=car,B1&" ","")

copy down

then = concatinate(C:C)
the more complicated the format, the more complicated the formula.


"nandkishor" wrote:

hi,

i would like to have filtered output in concatenate, i don't know how i can
do this :(

col A has make of Car's and col B has Models and i would like to
concatenanate all the filtered models in one cell of col C.

i am stuck with this.

I'm pretty sure = concatenate(C:C) won't concatenate everything in column C.
Each cell would have to be put into the formula (eg =Concatenate(C1,C2,C3,
etc)), which, of course, is not practical.

I played w/a UDF, which seems to work okay. To the OP, try pasting into a
standard module, then use =Concatvisible(C1:C20) or whatever your range is.
When the filter is not on, it returns blank. If you are new to VBA, David
McRitchie has some instructions on his site:
http://www.mvps.org/dmcritchie/excel/excel.htm


Function ConcatVisible(rngData As Range) As String
Dim rngCell As Range
Dim strConcat As String

On Error GoTo ExitSub
Application.Volatile True

If Application.Caller.Parent.FilterMode Then
For Each rngCell In rngData.Cells
If rngCell.EntireRow.Height 0 Then
strConcat = strConcat & rngCell.Value & " "
End If
Next rngCell
ConcatVisible = Trim$(strConcat)
Else: ConcatVisible = ""
End If

ExitSub:

End Function



"bj" wrote:

In what format do you want the output?
how many rows do you have?
if not many and
if all you want is a space between the models
=if(A1=Car,B1&" ","")&if(A2=Car,B2&" ",""& ...
or set up a helper column (C) with
=if(A1=car,B1&" ","")

copy down

then = concatinate(C:C)
the more complicated the format, the more complicated the formula.


"nandkishor" wrote:

hi,

i would like to have filtered output in concatenate, i don't know how i can
do this :(

col A has make of Car's and col B has Models and i would like to
concatenanate all the filtered models in one cell of col C.

i am stuck with this.