returning several matches

JoeMajestee

New Member
Joined
Jul 15, 2009
Messages
42
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>
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

cb12

Board Regular
Joined
Dec 20, 2008
Messages
117
Perhaps something like this might work, assuming your sheet is set up as per your example.

Code:
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
 
Last edited:

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

Code:
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:
Code:
=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

New Member
Joined
Jul 15, 2009
Messages
42
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.

Code:
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

Board Regular
Joined
Dec 20, 2008
Messages
117

ADVERTISEMENT

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

New Member
Joined
Jul 15, 2009
Messages
42
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.

Code:
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Code:
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

New Member
Joined
Jul 15, 2009
Messages
42
Perfect. Thanks a bunch MrKowz.

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

JoeMajestee

New Member
Joined
Jul 15, 2009
Messages
42
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.
 

Forum statistics

Threads
1,141,849
Messages
5,708,965
Members
421,601
Latest member
Garlo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top