VBA: Search for Text strings and return value from table

[ChetManley]

Hi all,

Long time lurker, first time poster - let me thank you all for years of troubleshooting and learning with pro's how to be a bit better in Excel.

My question is around VBA - not very well versed in it - and will try to explain as best as I can.

I have a list of countries with a score against each country, let's call it "Countries" table.
I also have a list of customers with multiple columns for address, nationality, etc that all revolve around location, let's call this table "Customers".

What I would like to learn how to do (if possible) is to:

Run a VBA code to search each relevant column in the "Customers" table for the countries in the "Countries" table and return the country score for each respective customer on a separate table, let's call this one "Customer Score".

Is this possible at all? Or am I better off using formulas? Issue is I will have to do this on a quarterly basis and I'd like to make it as resilient as possible (in case of absence)..

Much appreciated,
Chet

 

[DanteAmor]

Hi @ChetManley, welcome to the board!

You could post some examples of how your data is on the sheets.
If is confidential information, you could replace it with generic data.

To upload an excel range:
XL2BB - Excel Range to BBCode

 

[ChetManley]

Hi @DanteAmor replaced with generic data - attached as picture.

The idea would be to return the result onto the Customer Score table.

Apologies for using screenshots - currently not on my machine and I can't install the add-on at the moment.

 

[DanteAmor]

The images are very small and do not look good.
The customer score table is empty, you could fill it with the sample data and explain how to fill it.
Reduce the number of examples to make the image clearer.

 

[ChetManley]

Hi @DanteAmor sorry about that.
The crux of the problem is that I have a list of every country in the world with a rating against them - e.g.

Country	Rating
Albania	High
Argentina	High
Andorra	Medium
Australia	Low
Austria	Low
Azerbaijan	High

And on the other side I have a customer list with their profiles (can be multiple for the same person) and their addresses, e.g.

Customer No.	Country of Nationality	AddressLine 1	AddressLine 2	AddressLine 3	AddressLine 4	AddressLine 5
123	Argentina	XYZ	123	Buenos Aires	Argentina
123	Argentina	444	Wien	Wien, Austria
123	Australia	215	QWERTY	12345	Perth, Australia
345	Albania	ZXCV	12345	Sydney	Australia
567	Austria	Andorra	444, Andorra
100	Andorra	QWERTY	Azerbaijan	Baku	222333

My goal is to use the country rating list to rate customers based on their address and nationality and return the highest rating (from all the above fields) to fill a table similar to the below:

Customer No.	Rating
123	High
123	High
123	Low
345	High
567	Medium
100	High

Hope this makes it easier to follow? I am oversimplifying because my original data set has dozens more columns and thousands of customers, but if you can point the way I can probably extrapolate.
The way I am thinking to approach this is not very sophisticated - something like searching for the particular strings in the multiple fields I have with address data (albeit I don't know if possible).

Any help anyone can provide will be immense as I am stuck in this for a week now...

Many thanks
Chet

 

[DanteAmor]

Could you explain the following example?
How, with only the number 123, can you identify if the customer is from Argentina or is from Australia?

Book1
	A	B
1	Customer No.	Rating
2	123	High
3	123	High
4	123	Low
Sheet3

 

[ChetManley]

Could you explain the following example?
How, with only the number 123, can you identify if the customer is from Argentina or is from Australia?

Book1
	A	B
1	Customer No.	Rating
2	123	High
3	123	High
4	123	Low
Sheet3

You can't - because in most cases in my scenario the customers are companies and sometimes they have multiple accounts from different jurisdictions.
Which I circumvented by listing every single customer account - i.e. if customer 123 has 4 accounts, I will list them and aim to bring in the 4 respective jurisdictions - hope this helps?

 

[DanteAmor]

Could you have something like this in your results sheet in columns A and B. And that the formula or macro fill column C?

Book1
	A	B	C
1	Customer No.	Country	Rating
2	123	Argentina	High
3	123	Argentina	High
4	123	Autralia	Low
5	345	Albania	High
6	567	Austria	Medium
7	100	Andorra	High
Sheet3

 

[ChetManley]

Could you have something like this in your results sheet in columns A and B. And that the formula or macro fill column C?

Book1
	A	B	C
1	Customer No.	Country	Rating
2	123	Argentina	High
3	123	Argentina	High
4	123	Autralia	Low
5	345	Albania	High
6	567	Austria	Medium
7	100	Andorra	High
Sheet3

Yes that would work ?

 

[DanteAmor]

Not sure if this can help you.
If on Sheet 3 you have the country, then the customer number is not necessary, check the following:

Book1
	A	B
1	Country	Rating
2	Albania	High
3	Argentina	High
4	Andorra	Medium
5	Autralia	Low
6	Austria	Low
7	Azerbaijan	High
Sheet1

Book1
	A	B	C
1	Customer No.	Country	Rating
2	123	Argentina	High
3	123	Argentina	High
4	123	Autralia	Low
5	345	Albania	High
6	567	Austria	Low
7	100	Andorra	Medium
Sheet3

Cell Formulas
Range	Formula
C2:C7	C2	=VLOOKUP(B2,Sheet1!$A$1:$B$7,2,0)