[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Combining Text based on Matching Criteria

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?
1 Solution
Michael FowlerSolutions ConsultantCommented:
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
Juan OcasioApplication DeveloperCommented:
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.
mchapman286Author Commented:
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.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Would it be more appropriate to use client number as the unique identifier, rather than address?
Saqib Husain, SyedEngineerCommented:
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

Saqib Husain, SyedEngineerCommented:
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.

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now