Looping through rows to consolidate mismatched data?

[DouglasMacArthur]

Hi all,

I can here last week for help on this and while I did get some helpful responses, I believe the way I was going about my issue was a bit convoluted so hopefully my current idea is more straight forward. I believe the code shouldn't be too hefty, but I am very new to VBA so I really need all of the help I can get.

My problem:

Basically, I have a sheet with about 30K rows and about 20 columns with different values for contacts. The first two columns contain the first and last name of the contact and the rest are various pieces of information like phone number, email address, etc. I am attempting to remove duplicates in this data, but there's an important catch: I need to keep as much data for each person as possible, and some rows have more data than others. The built-in deduping tool keeps the first row and removes all duplicates after it regardless of content (and I can only use first and last name to compare, as not every column will always be filled) so this will not work.

My perceived solution:

I'm imagining a loop that goes through each cell in a row to determine if it is empty. If it is empty, there is a nested loop to check all rows containing the same first and last name for the missing cell data. If this data is found, it is placed into the empty cell, if it isn't nothing happens. By the end of the row, all data that exists for a person is in the first row that exists for them (differing values can be ignored). The main loop would then go to the next set of duplicated rows. Then I would run the built-in duplicate remover because I've consolidated the data to the first row that's kept anyway. There is one value that I would like to keep even over duplicate names but for simplicity I'm ignoring that for now.

Here's a crude diagram of the solution for one set of duplicated rows:
solution.png

The red line is the main loop, the blue line is the first run of the nested loop, the yellow/green line (I'm colorblind) is the second run.

And here's the desired result of that example (every cell is filled in the result, but this will not always be the case):
result.png

I really need help with two main things:

1) Is my solution at least a good way to do this? It doesn't have to be the best way, as long as it's decent enough to get this task done (it only needs to be done once).

2) How would I go about coding this? I'm pretty much a novice at programming in general, but I really know very little about VBA. I wouldn't even know how to iterate through the cells/rows.

Thanks for any help that can be provided!

[DouglasMacArthur]

Also, a thought I just had: Would it be helpful to anyone if I wrote the code I think I need in Java first? Obviously it wouldn't work, but I figure someone might be able to simply translate it to VBA?

[TMS]

I don't have time to work on this but, in principle, I do something like this:

1. Read all the dataset into an array.
2. Loop through the array and,
2a. if the first two elements are different to stored values, write a new entry in a second array.
2b. if the first two elements are the same as the stored values, loop through the elements and, if they are not blank, replace the equivalent element
3. When all entries have been processed, write the second array to a new sheet.

Simple in principle

I suspect you can do it with collections and dictionaries but not my forte.

[DouglasMacArthur]

Well unfortunately, like I said, my knowledge of VBA is very limited. What I've done is I took some time to design and write the code in Java instead of VBA and hopefully it isn't too difficult for someone who knows VBA well to just translate it. Basically, if I didn't know what the VBA equivilent of something was, I just took some liberty with the Java syntax so that someone translating it would know what I mean. For instance, I don't know what a row variable type is in VBA, so I just called it VBARowVar. Or I don't know how to go down a row so I just invoked the arbitrary method "addRow()" to signify that some VBA code to do that would go there. It became a bit tangled (a few nested structures), but nothing too bad. I added a few comments to explain some logic, but if anyone has questions about some part of it I can of course clarify. I apologize in advance for any Java-based syntax errors, it's been about a year since I've used Java and even then I wasn't the best.

Please Login or Register  to view this content.