Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
concatenation
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
concatenation
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
concatenation
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Concatenation | Excel Worksheet Functions | |||
Concatenation | Excel Worksheet Functions | |||
Concatenation | Excel Discussion (Misc queries) | |||
concatenation | Excel Discussion (Misc queries) | |||
Concatenation using IF AND - Possible? | Excel Worksheet Functions |