Me and Excel Again :)

[donbettis]

Excel 2007

Once again I have been asked to do something in excel - anyone that knows me understand that I do not get along with excel :banghead:. VB and Access yes - Excel just messes with my mind :banghead:

Ok here is what I need to do

I have two sheets.

Sheet1 has 4 columns...

Customer ID
Name
Address
Ship To ID

Sheet2 has 4 columns...

Customer ID
Name
Address
Ship_To_ID

I need to fill in sheet1's "Ship To ID" with sheet2's "Ship_To_ID"

This needs to based upon sheet1's Customer ID and Address because there are multiple ship to addresses for each Customer ID - However the addresses could be a little different (ie... 12 Main Rd on sheet1 could be 12 Main Road on sheet2 etc...)

I hope this makes sense.

I have attached an example file hoping to further explain

Any and all help will be greatly appreciated.

 

[noboffinme]

Ho Donbettis,

The matching of the 2 parameters is not too difficult however, the difference in the address name will cause problems.

I can't think of a way to guarantee that the addresses will also be correct when the Ship_ID is copied over.

If there's any sort of other field to use OR if you could confidently say that the first 'X' number of letters are always the same between the 2 w/sheets, it would help, but there is always the potential to copy any address that has a possible duplicate in this number of letters.

Is there any way the addresses could be made to be the same - could they both use the same source for the address detail for example?

 

[donbettis]

Thanks for the reply noboffinme.

Your answer is along the lines of what I had expected.

I broke down and did it the old fashion way. At least (I hope) this should be a one time issue.

 

[Brianwarnock]

If this is not a one off and the files are large you might like to try what I did in a similar situation.
I ran the match and filled in all of those with an exact match leaving the "ship to Id" blank were no exact match. Then using the fact of a blank cell used the customer Id from sheet 1 to extract the data from sheet2 , sort sheet1 with the blanks first and with what was now a much reduced amount of data did the rest the old fashioned way.

Brian