Comparing to Arrays and Sorting

I'm hoping to compare a list of customers with an existing list. My thought would be to first setup an array of strings so that I could check each name and determine how many letters off a name might be from an existing name (in case there is a typo, name cut off short in raw data, etc.).

I have labeled the worksheet "Data" with all the customer names from the raw data, and the customers are located in column A. The EXISTING customer list is in worksheet "Monthly Sales" and also in column A. It'd be nice to have a separate list then created of the names that don't match the existing customers so that the user could decide if this was a typing error or they are in fact not on the list of existing customers.

I started with a simple loop but have not gotten very far as I'm not sure how to compare the two lists properly:

Sub Sort()

Dim myCustomerArray(50) As String
Dim counter As Integer
Dim i As Integer
Set i = 1

For counter = 2 To 33
If Worksheets("Data").Cells(counter, 8) = "US40" Then
myCustomerArray(i) = Worksheets("Data").Cells(counter, 1)
i = i + 1
MsgBox myCustomerArray(counter)
End If
Next
End Sub

This gets the raw data customer list, but how would i compare this array with another existing array and remove matches/sort alphabetically?

Use a collection with the data as the key, then you can trap the error of adding a duplicate key, i.e. a match.

Look at help for collections.

Originally Posted by nathansav

Use a collection with the data as the key, then you can trap the error of adding a duplicate key, i.e. a match.

Look at help for collections.

Do you mean in Excel itself?

Originally Posted by PingPing

Do you know any SQL? If so then comparing the lists is a trivial exercise using different JOINs.

If you don't then I would recommend the following approach:
1. Sort both lists alphabetically;
2. Remove any duplicate values in a list so that each list contains only unique values;
2. Use a Nested Loop model to identify matches and non-matches.

The Nested Loop method is:
1. start with the first entry in the first list and the first entry in the second list;
2. compare the two values;
3. if matched then store the value in a third list ("matched");
4. if not matched then look at the next entry in the second list and do this until either you find a match (add to "matched" list) or you don't (add to a fourth list: "unmatched");
5. move on to the next entry in the first list and repeat the above until you've compared everything.

Again, SQL is much simpler (and faster) for this kind of work.

I don't know SQL unfortunately, but how might one go about using it if I'm new to it?

Thanks for the response.

No, collections in VBA, they are like arrays, but you can give them Keys to stop duplicates. So you could have a collection called colFormatting, and have keys for Bold, Underline, Color etc, then you can refer to them as font.bold = colFormatting("Bold").value

or something like that, not in Excel at the minute to do.

Can't you just use VLookup and filter on the Errors?

Originally Posted by Kyle123

Can't you just use VLookup and filter on the Errors?

How might you do that Kyle?

In your sheet of Business names, add a formula that checks the business name against the secondlist, so assuming Business Name is in A1 and your second list is in Range G1:G100
B1:

PHP Code: 

=VLookup(A1,$G$1:$G$100,1,False) 


Drag it down

Then use Autofilter, anything with an Error, isn't in your second list

Originally Posted by Kyle123

In your sheet of Business names, add a formula that checks the business name against the secondlist, so assuming Business Name is in A1 and your second list is in Range G1:G100
B1:

PHP Code: 

=VLookup(A1,$G$1:$G$100,1,False) 


Drag it down

Then use Autofilter, anything with an Error, isn't in your second list

Would the remaining list then be able to be used/adapted to a new data table? I guess I'm worried about using Autofilter as it will take me out of the macro and force the user to do things instead of making the entire process automated.

Originally Posted by Kyle123

I'm intrigued, in what way would SQL solve this more easily than VLookup?

You could copy the list to a new sheet and automate the process - that way the autofilter won't interfere with anything. You can then copy all the visible cells into a new sheet/wherever you want them.

Having re-read your question (should have read it properly to start with ) things are a little more complex if the business names aren't exactly the same, you'd need to implement a "fuzzy" matching routine - have a look here: http://www.mrexcel.com/forum/excel-q...planation.html

Interesting, I will give it a look. Thank you for the responses.

You think this is a more viable option than the SQL technique?

yes, a SQL implementation for this sounds like a bad idea to me, it's complex and you can't join on partial matches.

Originally Posted by PingPing

Well, I'd just import the data into Access (or copy and paste into tables) and run the code from there. I could stick with VBA and use ADO but that would be the wrong tool for the job IMHO.

'not sure about the 'business names' thing. I couldn't see where the OP mentioned them.

The business names might not exactly match what is currently listed in the "current customers" list, there may be a typo or a name might be truncated from the full name by a few letters.

Originally Posted by PingPing

Okay, so by definition they don't match and will be returned as such in the query's result set. I'm missing something here. Isn't that what you want?

As to partial matches, you can always alter the JOIN predicate to accomodate matching a set number of characters, eg. to match on the 1st 10 characters in each list:

Please Login or Register  to view this content.

I think the partial match is the way to go. That sounds like a good idea.

The end goal is to have a list that will match up these customers despite the typos/truncation so that their sales quantities and dollars can be added to the existing lists. Any new customers or new products ordered by existing customers will require the new rows added in the updating sales spreadsheet.

Originally Posted by PingPing

I could stick with VBA and use ADO but that would be the wrong tool for the job IMHO.

Me too since you can't query the current workbook with ADO. As PingPing mentions, you shouldn't really allow people to free type existing data, use drop downs instead - makes things much easier

Originally Posted by Kyle123

Me too since you can't query the current workbook with ADO. As PingPing mentions, you shouldn't really allow people to free type existing data, use drop downs instead - makes things much easier

Well the main issue is the raw data comes from a separate program's ouput, but that output can be sent out as an Excel spreadsheet. Once I get that output, it's in a set format so I at least know what I'm going to get, but the output from the program has some customer names that are truncated or not labeled exactly as the existing customer list.

I do think there is a way to make an un-exact match in VB that can match most of the letters in the existing customer list.