returning several matches

[JoeMajestee]

Sample data below.

Want to be able to accumulate all the zipcodes that a single owner might have from all his properties. The real data contains over 6000 lines and is not sorted.

So something like this:
<TABLE style="WIDTH: 227pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=302 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 179pt; mso-width-source: userset; mso-width-alt: 8704" width=238><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17 width=64>OwnerB</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 179pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 width=238>60110 60102 60112 60103</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17>OwnerC</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 x:num>60601</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Owner</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Property</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Zip</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropA1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60101</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropA2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60102</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropA3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60103</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropA4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60104</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropA5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60105</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>7</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropA6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60106</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>8</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropA7</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60107</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropA8</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60108</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropA9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60109</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerB</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropB1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60110</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerB</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropB2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60102</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>13</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerB</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropB3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60112</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>14</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerB</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropB4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60103</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>15</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerC</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropC1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60101</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 align=right x:num>16</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">OwnerD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">PropD1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>60115</TD></TR></TBODY></TABLE>

 

[cb12]

Perhaps something like this might work, assuming your sheet is set up as per your example.

SSub zip_codes()
Dim i, j, k, l, m, targetcolumn As Integer
Dim owner, zip, prevowner As String
    j = Selection.CurrentRegion.Rows.Count
    k = 2
    l = 2
    m = 1
    targetcolumn = 4
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A:C")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Cells(2, 1).Activate
    For i = 1 To j
    owner = ActiveCell.Value
        On Error Resume Next
        If Columns(targetcolumn).Find(owner, , xlValues, xlWhole).Row < 1 Then
            Cells(k, targetcolumn).Value = owner
            k = k + 1
            ActiveCell.Offset(1, 0).Select
        Else: ActiveCell.Offset(1, 0).Select
        End If
    Next i

    For i = 1 To j
    Cells(l, 1).Select
    owner = ActiveCell.Value
    zip = ActiveCell.Offset(0, 2).Value
        If prevowner <> owner Then
            m = 1
        End If
    Columns(targetcolumn).Find(owner, , xlValues, xlWhole).Select
    ActiveCell.Offset(0, m).Value = zip
    prevowner = owner
    l = l + 1
    m = m + 1
    Next i

End Sub

 

[MrKowz]

Try out this UDF.

To install the UDF, Press Alt+F11 to open the VBA editor. Go to Insert>Module, and copy/paste the code into that window.

Public Function ConcatIf(rng As Range, cond As Variant, Optional concatrng As Range, Optional delimiter As String) As String
   Dim i    As Long
 
   If Len(delimiter) = 0 Then
      delimiter = ""
   End If
 
   If concatrng Is Nothing Then
      Set concatrng = rng
   End If
 
   For i = 1 To rng.Rows.Count
      If rng.Cells(i, 1).Value <> "" And rng.Cells(i, 1).Value = cond Then
         ConcatIf = ConcatIf & concatrng.Cells(i, 1).Value & delimiter
      End If
   Next i
 
   If Len(ConcatIf) > 0 Then
      ConcatIf = Left(ConcatIf, Len(ConcatIf) - Len(delimiter))
   Else
      ConcatIf = ""
   End If
 
End Function

After you do that, you can now use this function in your worksheet. It has the syntax:

=CONCATIF(range_to_look_at, value_to_match, [range_to_concatenate], [delimiter])

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #FFFFFF;;">Owner</td><td style="background-color: #FFFFFF;;">Property</td><td style="border-right: 1px solid black;background-color: #FFFFFF;;">Zip</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA1</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60101</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style=";">OwnerA</td><td style=";">60101, 60102, 60103, 60104, 60105, 60106, 60107, 60108, 60109</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA2</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60102</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style=";">OwnerB</td><td style=";">60110, 60102, 60112, 60103</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA3</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60103</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style=";">OwnerC</td><td style="text-align: right;;">60101</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA4</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60104</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style=";">OwnerD</td><td style="text-align: right;;">60115</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA5</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60105</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA6</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60106</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA7</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60107</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA8</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60108</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA9</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60109</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="background-color: #FFFFFF;;">OwnerB</td><td style="background-color: #FFFFFF;;">PropB1</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60110</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="background-color: #FFFFFF;;">OwnerB</td><td style="background-color: #FFFFFF;;">PropB2</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60102</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="background-color: #FFFFFF;;">OwnerB</td><td style="background-color: #FFFFFF;;">PropB3</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60112</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="background-color: #FFFFFF;;">OwnerB</td><td style="background-color: #FFFFFF;;">PropB4</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60103</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="background-color: #FFFFFF;;">OwnerC</td><td style="background-color: #FFFFFF;;">PropC1</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60101</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="border-bottom: 1px solid black;background-color: #FFFFFF;;">OwnerD</td><td style="border-bottom: 1px solid black;background-color: #FFFFFF;;">PropD1</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">60115</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=concatif(<font color="Blue">$A$2:$A$16,E2,$C$2:$C$16,", "</font>)</td></tr></tbody></table></td></tr></table><br />

 

[JoeMajestee]

Thanks CP. The data is not exactly like the example and I was hoping for something more like a custom function that I could use. Here is what I came up with that doesn't work and after wrestling for too long I thought I would try here.

When I use the below code, I get run-time error '91': Object Variable or With block variable not set on the Loop While... line. ofound is nothing at that point, although the testvalue I'm using is in that range several times.

Public Function ZIPsInMgtCo(MgtCoName As String)
Dim wbLookInWb As Workbook, wsLookInWS As Worksheet, rngMgtCos As Range, iOffset As Integer
Dim oFound, FirstAddress As String
        Set wbLookInWb = Workbooks.Open("allorgs.csv", ReadOnly:=True)
        Set wsLookInWS = wbLookInWb.Sheets(1)
        Set rngMgtCos = wsLookInWS.Range(Cells(2, 5), _
            Cells(Cells(60000, 1).End(xlUp).Row, 5))
'use example from FindNext help
        With rngMgtCos
            Set oFound = .Find(MgtCoName, Lookat:=xlPart)
            If Not oFound Is Nothing Then
                iOffset = 2
                FirstAddress = oFound.Address
                Do
                    ZIPsInMgtCo = ZIPsInMgtCo & ", " & oFound.Offset(0, iOffset).Value
                    Set oFound = .FindNext(oFound)
                Loop While Not oFound Is Nothing And oFound.Address <> FirstAddress
            End If
        End With
ZIPSsInMgtCo = "None Found"
End Function

 

[cb12]

I had incorrectly assumed you would want to return the various zip codes in different cells, and therefore a piece of code would be easier. Best of luck.

 

[JoeMajestee]

MrKows.
This works pretty well. Maybe now would be a good time to mention that there are duplicate zipcodes in the list and we wouldn't want to get those and there are properties with no zipcodes and we wouldn't want to get those.

I seem to have handled the no zipcode problem, but am having a problem with the duplicate problem. The commented out line works to eliminate blanks.

For i = 1 To rng.Rows.Count
      If rng.Cells(i, 1).Value <> "" Or rng.Cells(i, 1).Value = cond Then
         If concatrng.Cells(i, 1).Value <> "" And InStr(1, concatrng.Cells(i, 1).Value, ConcatIf, vbTextCompare) <> 0 _
            Then ConcatIf = ConcatIf & concatrng.Cells(i, 1).Value & delimiter
'         If concatrng.Cells(i, 1).Value <> "" _
            Then ConcatIf = ConcatIf & concatrng.Cells(i, 1).Value & delimiter
      End If
   Next i

 

[MrKowz]

Give this a shot - it combines all requirements into a single line on the IF statement since every condition must be met:

Public Function ConcatIf(rng As Range, cond As Variant, Optional concatrng As Range, Optional delimiter As String) As String
   Dim i    As Long
 
   If Len(delimiter) = 0 Then
      delimiter = ""
   End If
 
   If concatrng Is Nothing Then
      Set concatrng = rng
   End If
 
   For i = 1 To rng.Rows.Count
      If rng.Cells(i, 1).Value <> "" And rng.Cells(i, 1).Value = cond And concatrng.Cells(i, 1).Value <> "" And InStr(ConcatIf, concatrng.Cells(i, 1).Value) = 0 Then
         ConcatIf = ConcatIf & concatrng.Cells(i, 1).Value & delimiter
      End If
   Next i
 
   If Len(ConcatIf) > 0 Then
      ConcatIf = Left(ConcatIf, Len(ConcatIf) - Len(delimiter))
   Else
      ConcatIf = ""
   End If
 
End Function

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #FFFFFF;;">Owner</td><td style="background-color: #FFFFFF;;">Property</td><td style="border-right: 1px solid black;background-color: #FFFFFF;;">Zip</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA1</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60101</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style=";">OwnerA</td><td style=";">60101, 60104, 60105, 60106, 60107, 60108, 60109</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA2</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60101</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style=";">OwnerB</td><td style=";">60110, 60102, 60112, 60103</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA3</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style=";">OwnerC</td><td style="text-align: right;;">60101</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA4</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60104</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style=";">OwnerD</td><td style="text-align: right;;">60115</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA5</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60105</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA6</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60106</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA7</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60107</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA8</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60108</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="background-color: #FFFFFF;;">OwnerA</td><td style="background-color: #FFFFFF;;">PropA9</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60109</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="background-color: #FFFFFF;;">OwnerB</td><td style="background-color: #FFFFFF;;">PropB1</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60110</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="background-color: #FFFFFF;;">OwnerB</td><td style="background-color: #FFFFFF;;">PropB2</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60102</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="background-color: #FFFFFF;;">OwnerB</td><td style="background-color: #FFFFFF;;">PropB3</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60112</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="background-color: #FFFFFF;;">OwnerB</td><td style="background-color: #FFFFFF;;">PropB4</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60103</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="background-color: #FFFFFF;;">OwnerC</td><td style="background-color: #FFFFFF;;">PropC1</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">60101</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="border-bottom: 1px solid black;background-color: #FFFFFF;;">OwnerD</td><td style="border-bottom: 1px solid black;background-color: #FFFFFF;;">PropD1</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">60115</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=concatif(<font color="Blue">$A$2:$A$16,E2,$C$2:$C$16,", "</font>)</td></tr></tbody></table></td></tr></table><br />

 

[JoeMajestee]

Perfect. Thanks a bunch MrKowz.

Can you tell me how you put worksheet examples into your posts?

 

[MrKowz]

I use the MrExcel HTML Maker

 

[JoeMajestee]

Thanks. Last time I tried to get that it was for some reason unavailable. Just downloaded it. I'm sure you've heard this before, but you ROCK.