excel VBA finding duplicate values and insert values into another cell based upon how many duplicates found

gwarcher
gwarcher used Ask the Experts™
on
I need some guidance as to where to start with this, pseudocode will be fine.

Here is an example of my sheet

First Name           Last Name          HouseHoldName              HHID

John                    Doe                                                              123
Jane                    Doe                                                              123
Bob                     Frank                                                            456


I have already uniquely identified members of a household, now what I would like to do is have a vba macro that will take the value in the HHID field and search the entire range for duplicate values.  I will then do an If...Then or Select Case that will add values to the HouseHold Name column based upon how many duplicates are found.  I can do this manually in Access, but it can be quite tedious, even for a small file.

I'm not familiar with Excel vba though, so I have difficulty knowing where to start.  Although I have done some Access VBA, as well as VB.net work.

Thanks for the help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ardhendu SarangiSr. Project Manager

Commented:
Hi,
If you are looking to find the number of duplicates, you can use the formula - COUNTIF

Assuming the HHID is in Column D, and the HouseHold Name is Column C, here's the formula u need to enter in cell C2

= COUNTIF("D:D",D2)

Copy the cell and select all the cells in Column C that you need and paste down.

- Ardhendu

Ardhendu SarangiSr. Project Manager

Commented:
If u still need a VBA code, which in my opinion is an overkill here it is...

- Ardhendu
Sub CountDupes()
Dim i As Integer
For i = 2 To Cells(65536, "D").End(xlUp).Row
    Range("C" & i) = Application.WorksheetFunction.CountIf(Range("D:D"), Range("D" & i))
Next
End Sub

Open in new window

Author

Commented:
I'm sorry, probably should have clarified a little more.  If, for example, it is found that HHID 123 exists 2 times in the range, then I would like for the the 1st duplicate instance in column A ("John") and the second duplicate instance in column A ("Jane") to be parsed together in Column C ("John " & " and " & "Jane" & " Doe")

That's why I thought VBA would be necessary
 
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Ardhendu SarangiSr. Project Manager

Commented:
Hi,
Can you please post an example with before and after scenarios?  This is a bit hard to envision...

- Ardhendu

Author

Commented:
That countIF function is really cool though, never used it.  Even if I can't make this other thing work, it's a great tool to do this manually

Author

Commented:
Sure

First Name           Last Name          HouseHoldName              HHID

John                    Doe                                                              123
Jane                    Doe                                                              123
Bob                     Frank                                                            456
Mary                   Tyler                                                             789
Gary                   Tyler                                                             789
Larry                  Tyler                                                             789



First Name           Last Name          HouseHoldName              HHID

John                    Doe                      John and Jane Doe         123
Jane                    Doe                      John and Jane Doe         123  
Bob                     Frank                    Bob Frank                       456
Mary                   Tyler                      The Tyler Family             789
Gary                   Tyler                     The Tyler Family              789
Larry                  Tyler                      The Tyler Family            789


Ardhendu SarangiSr. Project Manager

Commented:
Ok... I think I got it now... give me a few minutes...

- Ardhendu
Ardhendu SarangiSr. Project Manager

Commented:
Hey, quick note - VBA can only display a combination of cells from the excel. It cannot show new data...

I mean John and Jane Doe is possible but "the tyler family" is not possible.

- Ardhendu

Author

Commented:
I can do a simple access query to add those (update If HHID < 2)  It's always been the couples that have given me the most problems
Sr. Project Manager
Commented:
Hi,

Can you try running the following macro and see if this works for you?

- Ardhendu

Note - To install the code, use ALT + F11 to open the VBA Editor. Click on Insert...Module to create a blank module sheet. Paste the code there, then ALT + F11 to return to the spreadsheet.

To run the code, hit ALT + F8 to open the macro window. Select the macro, then click the Run button.

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Sub AddHouseHoldName()

Dim i As Integer, j As Integer, cntr As Integer
Dim Str As String

For i = 2 To Cells(65536, "A").End(xlUp).Row
    cntr = Application.WorksheetFunction.CountIf(Range("D:D"), Range("D" & i)) - 1
    Str = Range("A" & i) & " " & Range("B" & i)
    For j = 1 To cntr
        Str = Range("A" & j + i) & " and " & Str
    Next j
    i = i + j - 1
    Range("C" & i) = Str
Next

End Sub

Open in new window

Author

Commented:
When sorted initially (which is totally fine) this works great.  Thanks for the starting point!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial