Combining Names in Customer List

Mardy McFly

New Member
Joined
Nov 27, 2016
Messages
2
Hello Eveyone,

I have a customer list from which I am trying to create mailing labels. The list is generated with 1 line per customer (First Name, Last Name, Address). I need to have the output of the names on the label different depending on the situation.

1. If the same address has all the same last name then I need the out put to be "The [Last Name] Family"
2. If the same address has more than one last name then I need the out put to be "[First Name] [Last Name] & [First Name] [Last Name]..." for as many names as are at the address

I have tried If AND/OR formulas, but I cannot get the 2. scenario to out put if there are more than 2 last names at an address. I'm wondering if there is a macro that could do it?

Any help would be appreciated.

Sample Data:

first_namelast_nameAddressCityStateZip
NicholasRobertson0 Barnett CircleWacoTX76796
BenjaminRobertson0 Barnett CircleWacoTX76796
MariaWoods0 Bluestem JunctionMobileAL36622
KarenKelly0 Browning CrossingPasadenaCA91199
TheresaLawrence0 Browning CrossingPasadenaCA91199
RubyCarter0 Browning CrossingPasadenaCA91199
JuliaScott0 Dorton TrailCorpus ChristiTX78465
CarlosBlack0 Fairfield PlazaLas VegasNV89150
KathrynBlack0 Fairfield PlazaLas VegasNV89150
GeraldBlack0 Fairfield PlazaLas VegasNV89150
EugeneHernandez0 Hanover PlazaWilmingtonDE19897
AnneOrtiz0 Hoepker ParkwaySan DiegoCA92110
CynthiaHoward0 Manufacturers CenterSan AntonioTX78260
LouisBurke0 Manufacturers CenterSan AntonioTX78260
RachelPatterson0 Meadow Valley AvenueSan DiegoCA92170
JonathanBradley0 Novick PassDallasTX75372
AnnieBradley0 Novick PassDallasTX75372
TimothyBradley0 Novick PassDallasTX75372
BarbaraSpencer0 Sachs PlazaDetroitMI48232
AmandaGomez0 Spohn ParkwayNorth PortFL34290
BillyKim0 Sutteridge TrailCamdenNJ8104
CherylNichols0 Sutteridge TrailCamdenNJ8104
JacquelineRodriguez0 Sutteridge TrailCamdenNJ8104
AntonioWeaver00 Lunder PassSan DiegoCA92160
DorothyWeaver00 Lunder PassSan DiegoCA92160
AlbertWeaver00 Lunder PassSan DiegoCA92160
JeanWeaver00 Lunder PassSan DiegoCA92160

<tbody>
</tbody>
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hello

i think this formula will help you.

=IF(AND(COUNTIF(B:B,B2)>1,COUNTIF(C:C,C2)>1),"The "&B2&" family",A2&" "&B2)

Please let me know if there any problem.
 
Upvote 0
This UDF should help.

The AddressFor UDF returns the address of the indicated cells. i.e. =AddressFor(A2:F2) returns "0 Barnett Circle, Waco Tx 76796"
Note that the optional StreetColumn, CityColumn, StateColumn and ZipColumn arguments can specify which columns hold which kind of data. The default to 3,4,5,6 respectively.

That UDF is needed for the AddressTo UDF.
Note the optional DataRange argument of AddressTo. If omitted, it defaults to the entire column of the rngThisData argument.
However if the DataRange argument is omitted, the function becomes volatile. It is preferred that it be specified, even if whole column references are used, this is faster than having a volatile function.

AddressTo also has optional StreetColumn, CityColumn, StateColumn and ZipColumn arguments.

I added another resident to the Texas address to show mixed family and other resident results.

Code:
Function AddressTo(rngThisData As Range, Optional dataRange As Range, _
                                Optional FirstColumn As Long, Optional LastColumn As Long, _
                                Optional StreetColumn As Long, Optional CityColumn As Long, _
                                Optional StateColumn As Long, Optional ZipColumn As Long) As String
    
    Const Delimiter As String = " & "
    Dim i As Long
    Dim ThisAddress  As String, strStreet As String, strCity As String, strState As String, strZip As String
    Dim ThisLast As String, ThisName As String
    
    Application.Volatile (dataRange Is Nothing)
    
    If dataRange Is Nothing Then
        Set dataRange = rngThisData.EntireColumn
    End If
    
    If FirstColumn < 1 Then FirstColumn = 1
    If LastColumn < 1 Then LastColumn = FirstColumn + 1
    If StreetColumn < 1 Then StreetColumn = LastColumn + 1
    If CityColumn < 1 Then CityColumn = StreetColumn + 1
    If StateColumn < 1 Then StateColumn = CityColumn + 1
    If ZipColumn < 1 Then ZipColumn = StateColumn + 1
    
    With dataRange
        Set dataRange = Application.Intersect(.Cells, .Parent.UsedRange)
    End With
    
    With rngThisData
        strStreet = .Cells(1, StreetColumn).Value
        strCity = .Cells(1, CityColumn).Value
        strState = .Cells(1, StateColumn).Value
        strZip = .Cells(1, ZipColumn)
    End With
    
    ThisAddress = AddressFor(rngThisData, StreetColumn, CityColumn, StateColumn, 0)
    
    For i = 1 To dataRange.Rows.Count
        If ThisAddress = AddressFor(dataRange.Cells(i, 1), StreetColumn, CityColumn, StateColumn, 0) Then
            ThisLast = dataRange.Cells(i, LastColumn).Value
            
            With dataRange
            If 1 < WorksheetFunction.CountIfs(.Columns(LastColumn), ThisLast, .Columns(StreetColumn), strStreet, .Columns(CityColumn), strCity, _
                            .Columns(StateColumn), strState) Then
                Rem dup family members
                ThisName = StrConv(ThisLast, vbProperCase) & " family"
            Else
                ThisName = StrConv(.Cells(i, FirstColumn) & " " & ThisLast, vbProperCase)
            End If
            
            If Not (AddressTo Like "*" & ThisName & "*") Then
                AddressTo = AddressTo & Delimiter & ThisName
            End If
            End With
        End If
    Next i
    AddressTo = Mid(AddressTo, Len(Delimiter) + 1)
End Function

Function AddressFor(rngThisData As Range, _
                                Optional StreetColumn As Long, Optional CityColumn As Long, _
                                Optional StateColumn As Long, Optional ZipColumn As Long = -1) As String
    
    If StreetColumn < 1 Then StreetColumn = 3
    If CityColumn < 1 Then CityColumn = StreetColumn + 1
    If StateColumn < 1 Then StateColumn = CityColumn + 1
    If ZipColumn < 0 Then ZipColumn = StateColumn + 1
    
    With rngThisData
        If ZipColumn <> 0 Then
            AddressFor = .Cells(1, StreetColumn).Value & ", " & .Cells(1, CityColumn).Value & " " _
                & .Cells(1, StateColumn).Value & " " & .Cells(1, ZipColumn).Value
        Else
            AddressFor = .Cells(1, StreetColumn).Value & ", " & .Cells(1, CityColumn).Value & " " _
                & .Cells(1, StateColumn).Value
        End If
    End With
    AddressFor = WorksheetFunction.Trim(AddressFor)
    AddressFor = StrConv(AddressFor, vbProperCase)
End Function


Unknown
ABCDEFGHI
1first_namelast_nameAddressCityStateZip
2NicholasRobertson0 Barnett CircleWacoTX76796Robertson family & Mary Hastings0 Barnett Circle, Waco Tx 76796
3BenjaminRobertson0 Barnett CircleWacotx76796Robertson family & Mary Hastings0 Barnett Circle, Waco Tx 76796
4MariaWoods0 Bluestem JunctionMobileAL36622Maria Woods0 Bluestem Junction, Mobile Al 36622
5KarenKelly0 Browning CrossingPasadenaCA91199Karen Kelly & Theresa Lawrence & Ruby Carter0 Browning Crossing, Pasadena Ca 91199
6TheresaLawrence0 Browning CrossingPasadenaCAKaren Kelly & Theresa Lawrence & Ruby Carter0 Browning Crossing, Pasadena Ca
7MaryHastings0 Barnett CircleWacoTX76796Robertson family & Mary Hastings0 Barnett Circle, Waco Tx 76796
8RubyCarter0 Browning CrossingPasadenaCA91199Karen Kelly & Theresa Lawrence & Ruby Carter0 Browning Crossing, Pasadena Ca 91199
9JuliaScott0 Dorton TrailCorpus ChristiTX78465Julia Scott0 Dorton Trail, Corpus Christi Tx 78465
10CarlosBlack0 Fairfield PlazaLas VegasNV89150Black family0 Fairfield Plaza, Las Vegas Nv 89150
11KathrynBlack0 Fairfield PlazaLas VegasNV89150Black family0 Fairfield Plaza, Las Vegas Nv 89150
12GeraldBlack0 Fairfield PlazaLas VegasNV89150Black family0 Fairfield Plaza, Las Vegas Nv 89150
13EugeneHernandez0 Hanover PlazaWilmingtonDE19897Eugene Hernandez0 Hanover Plaza, Wilmington De 19897
14AnneOrtiz0 Hoepker ParkwaySan DiegoCA92110Anne Ortiz0 Hoepker Parkway, San Diego Ca 92110
15CynthiaHoward0 Manufacturers CenterSan AntonioTX78260Cynthia Howard & Louis Burke0 Manufacturers Center, San Antonio Tx 78260
16LouisBurke0 Manufacturers CenterSan AntonioTX78260Cynthia Howard & Louis Burke0 Manufacturers Center, San Antonio Tx 78260
17RachelPatterson0 Meadow Valley AvenueSan DiegoCA92170Rachel Patterson0 Meadow Valley Avenue, San Diego Ca 92170
18JonathanBradley0 Novick PassDallasTX75372Bradley family0 Novick Pass, Dallas Tx 75372
19AnnieBradley0 Novick PassDallasTX75372Bradley family0 Novick Pass, Dallas Tx 75372
20TimothyBradley0 Novick PassDallasTX75372Bradley family0 Novick Pass, Dallas Tx 75372
21BarbaraSpencer0 Sachs PlazaDetroitMI48232Barbara Spencer0 Sachs Plaza, Detroit Mi 48232
22AmandaGomez0 Spohn ParkwayNorth PortFL34290Amanda Gomez0 Spohn Parkway, North Port Fl 34290
23BillyKim0 Sutteridge TrailCamdenNJ8104Billy Kim & Cheryl Nichols & Jacqueline Rodriguez0 Sutteridge Trail, Camden Nj 8104
24CherylNichols0 Sutteridge TrailCamdenNJ8104Billy Kim & Cheryl Nichols & Jacqueline Rodriguez0 Sutteridge Trail, Camden Nj 8104
25JacquelineRodriguez0 Sutteridge TrailCamdenNJ8104Billy Kim & Cheryl Nichols & Jacqueline Rodriguez0 Sutteridge Trail, Camden Nj 8104
26AntonioWeaver00 Lunder PassSan DiegoCA92160Weaver family00 Lunder Pass, San Diego Ca 92160
27DorothyWeaver00 Lunder PassSan DiegoCA92160Weaver family00 Lunder Pass, San Diego Ca 92160
28AlbertWeaver00 Lunder PassSan DiegoCA92160Weaver family00 Lunder Pass, San Diego Ca 92160
29JeanWeaver00 Lunder PassSan DiegoCA92160Weaver family00 Lunder Pass, San Diego Ca 92160
Sheet1
Cell Formulas
RangeFormula
H2=AddressTo(A2:F2)
H3=AddressTo(A3:F3,$A$1:$F$1000)
I2=AddressFor(A2:F2)
I3=AddressFor(A3:F3)
 
Upvote 0
Hi live_excel,

Thanks for the reply. Unfortunately, the formula doesn't work quite right. Here is the same table with your formula and what I am looking for a formula or macro to do (sorry, should have put this in on the first post):

first_namelast_nameAddressCityState ZipFormulaNeeded
NicholasRobertson0 Barnett CircleWacoTX76796The Robertson familyThe Robertson family
BenjaminRobertson0 Barnett CircleWacoTX76796The Robertson family
MariaWoods0 Bluestem JunctionMobileAL36622Maria WoodsThe Woods Family
KarenKelly0 Browning CrossingPasadenaCA91199The Kelly familyKaren Kelly & Theresa Lawrence & Ruby Carter
TheresaLawrence0 Browning CrossingPasadenaCA91199The Lawrence family
RubyCarter0 Browning CrossingPasadenaCA91199The Carter family
JuliaScott0 Dorton TrailCorpus ChristiTX78465Julia ScottThe Scott Family
CarlosBlack0 Fairfield PlazaLas VegasNV89150The Black familyThe Black Family
KathrynBlack0 Fairfield PlazaLas VegasNV89150The Black family
GeraldBlack0 Fairfield PlazaLas VegasNV89150The Black family
EugeneHernandez0 Hanover PlazaWilmingtonDE19897Eugene HernandezThe Hernandez Family
AnneOrtiz0 Hoepker ParkwaySan DiegoCA92110Anne Ortiz
CynthiaHoward0 Manufacturers CenterSan AntonioTX78260The Howard familyCynthia Howard & Louis Burke
LouisBurke0 Manufacturers CenterSan AntonioTX78260The Burke family
RachelPatterson0 Meadow Valley AvenueSan DiegoCA92170Rachel PattersonThe Patterson Family
JonathanBradley0 Novick PassDallasTX75372The Bradley familyThe Bradley family
AnnieBradley0 Novick PassDallasTX75372The Bradley family
TimothyBradley0 Novick PassDallasTX75372The Bradley family
BarbaraSpencer0 Sachs PlazaDetroitMI48232Barbara SpencerThe Spencer Family
AmandaGomez0 Spohn ParkwayNorth PortFL34290Amanda GomezThe Gomez Family
BillyKim0 Sutteridge TrailCamdenNJ8104The Kim familyBilly Kim & Cheryl Nichols & Jacqueline Rodriguez
CherylNichols0 Sutteridge TrailCamdenNJ8104The Nichols family
JacquelineRodriguez0 Sutteridge TrailCamdenNJ8104The Rodriguez family
AntonioWeaver00 Lunder PassSan DiegoCA92160The Weaver familyThe Weaver family
DorothyWeaver00 Lunder PassSan DiegoCA92160The Weaver family
AlbertWeaver00 Lunder PassSan DiegoCA92160The Weaver family

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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
Back
Top