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. |
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. |
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. |
All times are GMT +1. The time now is 11:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com