formula : =CONCATIF($A$2:$A$18,D2,$B$2:$B$65)
vba code of ConcatIf UDF :
Function ConcatIf(Rang1 As Range, Crite As Range, _
Optional Rang2 As Range = Nothing, _
Optional Dlmtr As String = "; ") As String
'---------------------------------------
' siti Vi / UDF CONDITIONAL CONCATENATE
' bluewater, indonesia May 13 2010
'---------------------------------------
Dim Cel As Range, Hasil As String, n As Long
If Rang2 Is Nothing Then Set Rang2 = Rang1
For n = 1 To Rang1.Rows.Count
If Rang1(n, 1) = Crite Then
If Rang2(n, 1) <> "" And Rang2(n, 1) <> "?" Then
Hasil = Hasil & Dlmtr & Rang2(n, 1)
End If
End If
Next n
If Len(Hasil) > 0 _
Then ConcatIf = Right(Hasil, Len(Hasil) - Len(Dlmtr)) _
Else ConcatIf = Hasil
End Function
From: maryann <jiangqiyangf...@gmail.com>
Date: Wed, 12 May 2010 07:56:52 -0700 (PDT)
Local: Wed, May 12 2010 9:56 pm
Subject: $$Excel-Macros$$ Delete rows and concatenate values.
Dear VBA guru,
I have a table like below:
col A ColB
1 ?
1 ABD
2 ADC
2
2 ADD
I would like to have results as:
1 ABD
2 ADC; ADD
In other words, I would like to delete the rows with empty cell and
question marks, or other unwanted values (ideally, there will be a pop
up window shows the value criteria that I want to delete the rows).
Also, if possible, concatenate the value in column B so that values in
column A is unique.
Thanks a lot!
Maryann
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
ctv_UDF_ConcatIf.xls
Description: MS-Excel spreadsheet
