Combining Text based on Matching Criteria

Posted on 2011-05-09
Last Modified: 2012-05-11
I have a sales spreadsheet that contains every sale made to clients.  What I would like to do is summarize the dollar amount of sales per household.  Each person has their own client number, name, address and amount of sale.  I would like the summary (on a different workshop) to show one row per household with the following headings:

Family Name, First Names (this would list all members of the household), Address, Total Sales.

How can I have the First Names section populated with each persons first name if clients are at the same address?
Question by:mchapman286
    LVL 23

    Expert Comment

    A macro will do his but....

    Does the data have instances that fall outside the requirements


    Mulitple clients listed at the same address who are unrelated or where people have moved and the new owners/tenets have become clients

    Do you instances where the surname name has changed but not the address between sales
    LVL 14

    Expert Comment

    by:Juan Ocasio
    Jus to add to Michael74's comment another possible issue would be in the addresses were the same, but not written the same for example

    123 Main Street
    123 M. Street
    123 Main St.

    How do you handle this situation?  With a macro, each of these would be separate line items.

    Author Comment

    I'm ok with the possiblity of people at the same address that are not related.  99% of the time if they are at the same address they will need to be counted as the same household.  Because of the type of sales these are, I don't think that the new tenants would become clients without the previous tenants address being changed.  As for data consistancy, I'm not sure how to handle if the address is not exactly the same, but the address is really the only way I can identify if someone is at the same address.  Hopefully, most will be consistant, I will be using the remove duplicates command and hopefully the user can do a quick scan of the data to see if there are possible duplicates and modify the entries as necessary.
    LVL 4

    Expert Comment

    Would it be more appropriate to use client number as the unique identifier, rather than address?
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Assuming you have taken care of inconsistencies in the typing of addresses you can try this macro.

    I have tried to attach a file also but at the moment it is not going through. Shall post it later.

    Sub addresswise()
    Dim adcoll As New Collection
    Dim adcol As New Collection
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    n = 1
    On Error Resume Next
    For Each adr In sh1.Range("C2:C" & sh1.Range("C2").End(xlDown).Row)
    fnm = Left(adr.Offset(0, -1) & " ", InStr(adr.Offset(0, -1) & " ", " "))
    lnm = Replace(adr.Offset(0, -1), fnm, "")
    sv = adr.Offset(0, 1)
    found = False
    For i = 2 To n
        If sh2.Cells(i, 3) = adr.Value Then
            found = True
            sh2.Cells(i, 1) = lnm
            sh2.Cells(i, 2) = Trim(sh2.Cells(i, 2) & " " & fnm)
            sh2.Cells(i, 4) = sh2.Cells(i, 4) + sv
        End If
    Next i
    If Not found Then
            n = n + 1
            sh2.Cells(i, 1) = lnm
            sh2.Cells(i, 2) = Trim(fnm)
            sh2.Cells(i, 3) = adr.Value
            sh2.Cells(i, 4) = sv
    End If
    Next adr
    End Sub

    Open in new window

    LVL 43

    Accepted Solution

    I am sorry I had lost track of this question and that I was supposed to post a file.

    Here is the file. You can run the macro to regroup the data on sheet1 and place the results on sheet2 which has a pre-entered header row.

    LVL 24

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now