VBA ConcatIf

[anthonyexcel]

Good evening. I have some data below that I need to be concatanated kind of a Concatif. Column A has School ID, Column B has # of Students and column C has Grade what I need is the setup below. Thanks in advance! This is kind of the VBA function that I am looking for something like =concat(IF(($A$2:$A$20=$E2)*($C$2:$C$20=F$1),B2:B20),",")

******** ******************** src="<A href="http://www.interq.or.jp/sun/puremis/colo/popup.js%22%3E%3C/SCRIPT%3E%3CCENTER%3E%3Ctable" target=_blank>http://www.interq.or.jp/sun/puremis/colo/popup.js">*********><CENTER>

Microsoft Excel - Book4 (2).xlsx ___Running: 14.0 : OS = Windows XP <TBODY> </TBODY>
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout <FORM name=formCb755237><INPUT value="Copy Formula" type=button name=btCb873980 *******='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);'></FORM> <TBODY> </TBODY>
<SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705><OPTION selected value="">A1</OPTION></SELECT> = <INPUT value=SCHOOLID size=80 name=txbFb426622> <TBODY> <FORM name=formFb078704> </FORM> </TBODY>
	<CENTER>A</CENTER>	<CENTER>B</CENTER>	<CENTER>C</CENTER>	<CENTER>D</CENTER>
<CENTER>1</CENTER>	SCHOOLID	# of Students	Grade
<CENTER>2</CENTER>	800	22	K
<CENTER>3</CENTER>	800	22	K
<CENTER>4</CENTER>	800	20	K
<CENTER>5</CENTER>	800	21	K
<CENTER>6</CENTER>	800	18	1
<CENTER>7</CENTER>	800	19	1
<CENTER>8</CENTER>	800	23	2
<CENTER>9</CENTER>	800	17	2
<CENTER>10</CENTER>	801	22	K
<CENTER>11</CENTER>	801	21	1
<CENTER>12</CENTER>	801	13	2
<CENTER>13</CENTER>	801	21	3
<CENTER>14</CENTER>	801	17	4
<CENTER>15</CENTER>	801	14	4
<CENTER>16</CENTER>	801	21	4
<CENTER>17</CENTER>	801	19	5
<CENTER>18</CENTER>	801	25	5
<CENTER>19</CENTER>	801	26	5
<CENTER>20</CENTER>	801	24	5
Sheet1 <TBODY> </TBODY>

cellSpacing=0 cellPadding=0 align=center><TBODY>
</TBODY>

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>


******** ******************** src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********><CENTER>

Microsoft Excel - Book4 (2).xlsx ___Running: 14.0 : OS = Windows XP <TBODY> </TBODY>

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp [URL="http://www.mrexcel.com/forum/#javascript<strong></strong>:void(0)"](A)bout <TBODY>

cellSpacing=0 cellPadding=0 align=center><TBODY>

<FORM name=formCb605117><INPUT value="Copy Formula" type=button name=btCb942116 *******='window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);'></FORM> </TBODY>
<SELECT onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name=sltNb447362><OPTION selected value="">E1</OPTION></SELECT> = <INPUT value=SCHOOLID size=80 name=txbFb150492> <TBODY> <FORM name=formFb202339> </FORM> </TBODY>
	<CENTER>E</CENTER>	<CENTER>F</CENTER>	<CENTER>G</CENTER>	<CENTER>H</CENTER>	<CENTER>I</CENTER>	<CENTER>J</CENTER>	<CENTER>K</CENTER>	<CENTER>L</CENTER>	<CENTER>M</CENTER>	<CENTER>N</CENTER>
<CENTER>1</CENTER>	SCHOOLID	K	1	2	3	4	5	6	7	8
<CENTER>2</CENTER>	800	22,22,20,21	18,19	23,17
<CENTER>3</CENTER>	801	22	21	13	21	17,14,21	19,25,26,24
Sheet1 <TBODY> </TBODY>

</TBODY>

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>[/URL]

 

[StephenCrump]

Here's a VBA function that might help. To concatenate class sizes for all 800, K classes, for example, you would call

=MyConcatenate($A$2:$C$20,2,",",800,,"k")

i.e. to return concatenated numbers from column 2, with delimiter ",".

I've used ParamArray so you can match as many columns as you like within the passed range.

Function MyConcatenate(rng As Range, lCol As Long, sDelim As String, ParamArray vMatches() As Variant) As Variant

    Dim v As Variant
    Dim r As Long, c As Long
    Dim sTemp As String
    Dim bMatch() As Boolean
    
    On Error GoTo MyError
    v = rng.Value
    ReDim bMatch(1 To UBound(v, 1))
    sTemp = ","

    For r = 1 To UBound(v, 1)
        bMatch(r) = True
        For c = 1 To UBound(vMatches) + 1
            If Not IsMissing(vMatches(c - 1)) Then
                If v(r, c) <> vMatches(c - 1) Then
                    bMatch(r) = False
                    Exit For
                End If
            End If
        Next c
        If bMatch(r) Then sTemp = sTemp & v(r, lCol) & sDelim
    Next r
    
    MyConcatenate = Left(sTemp, Len(sTemp) - 1)
    Exit Function

MyError:
    MyConcatenate = CVErr(xlErrNA)

End Function

 

[StephenCrump]

Oops, trying to edit on the fly and making silly mistakes! This is better:

Function MyConcatenate(rng As Range, lCol As Long, sDelim As String, ParamArray vMatches() As Variant) As Variant

    Dim v As Variant
    Dim r As Long, c As Long
    Dim sTemp As String
    Dim bMatch() As Boolean
    
    On Error GoTo MyError
    v = rng.Value
    ReDim bMatch(1 To UBound(v, 1))
    
    For r = 1 To UBound(v, 1)
        bMatch(r) = True
        For c = 1 To UBound(vMatches) + 1
            If Not IsMissing(vMatches(c - 1)) Then
                If v(r, c) <> vMatches(c - 1) Then
                    bMatch(r) = False
                    Exit For
                End If
            End If
        Next c
        If bMatch(r) Then sTemp = sTemp & v(r, lCol) & sDelim
    Next r
    
    If Len(sTemp) Then
        MyConcatenate = Left(sTemp, Len(sTemp) - 1)
    Else
        MyConcatenate = ""
    End If
    Exit Function

MyError:
    MyConcatenate = CVErr(xlErrNA)

End Function

 

[anthonyexcel]

StephenCrump thank you so much!! That is exactly what I needed! I appreciate your help on this! Thank you again!