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

 

[ChetManley]

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)

Hi, unfortunately no..

The issue is that the Country values that I would need to rate according to the Country Rating table, are all spread across different columns and there's no logic behind it.
If the address data was always in the same column then it'd work, issue is they're spread out across the Address1, Address2, Address3, etc.

I would like to find a solution to check each of those fields for each customer and return the country rating from the Rating table to a new table.

 

[ChetManley]

Hi, unfortunately no..

The issue is that the Country values that I would need to rate according to the Country Rating table, are all spread across different columns and there's no logic behind it.
If the address data was always in the same column then it'd work, issue is they're spread out across the Address1, Address2, Address3, etc.

I would like to find a solution to check each of those fields for each customer and return the country rating from the Rating table to a new table.

Also a caveat, the Customer number is required because we are rating customers based on their country information...

 

[ChetManley]

Hi @DanteAmor I think I might be on to something - I'm using:

=SUMPRODUCT(--(COUNTRYRATING[Country]=Sheet1!E8:N8))>0

Where E:N are the various fields where I'm searching for the country string - it is working but returns TRUE or FALSE.
Any way I can return the High, Medium, Low values instead? Or if too complicated, return the country name found?

Thank you for the help!

Cheers
Chet

 

[DanteAmor]

I'm sorry, but I'm not understanding what you want to look for.
With the data you put. You could explain a couple of examples, that is, what data do you have, where do you want to look for it, what do you expect to get, then where do you want to search with what data do you want to look for and finally the expected result.
That is, tell me the sequence of steps you would do manually with a couple of cases.

 

[ChetManley]

Hi @DanteAmor

I essentially have 2 tables of data - one with Customer Number and some details related to nationality, country of operation and 8 fields for address lines (where the address is broken up in different cells).

The second table has a list of countries and a rating associated with them.

The goal is to rate each customer, on a new table, based on their nationality, country of operation and address (where country is present) - my biggest problem is the Address fields because the country may appear in either of the 8 Address fields.

Customer No.	AddressLine1	AddressLine2	AddressLine3	AddressLine4	AddressLine5	AddressLine6	AddressLine7
12	xx	11	Finland	22	33
23	dd	Spain	xx	xx	11	22	44

I tried using this formula
=SUMPRODUCT(--(COUNTRYRATING[Country]=Sheet1!E8:N8))>0

Where "COUNTRYRATING[Country]" is the column with the Country name in the Country Rating table
And
Where "Sheet1!E8:N8" is the range for one of the customers address lines (like the example table above).

The problem is that the formula can only return TRUE or FALSE, if you can teach me a formula that returns the Country Name when matched in the Address cells I would be eternally thankful

Hope this makes it clearer?

I realize the requirements have changed a bit since the first post but I've tried to find my own solution....

Many thanks for your time,
Chet

 

[DanteAmor]

Assuming your data in the "Country rating" sheet like this:

Book1
	A	B
1	COUNTRY	SCORE
2	Finland	Hight
3	Spain	Medium
4	Argentina	low
Country rating

And also assuming your data on sheet1 in this way.
I put the formula in column C.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
7	Customer	No.	Score		AddressLine1	AddressLine2	AddressLine3	AddressLine4	AddressLine5	AddressLine6	AddressLine7
8	12	xx	Hight		11	Finland	22	33
9	23	dd	Medium		Spain	xx	xx	11	22	44
10	45	yy	low		1	1	1	1	1	Argentina	1
Sheet1

Cell Formulas
Range	Formula
C8:C10	C8	{=INDEX(COUNTRYRATING[SCORE],SUMPRODUCT(--(COUNTRYRATING[COUNTRY]=Sheet1!E8:L8)*(ROW(COUNTRYRATING[COUNTRY])))-1)}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[ChetManley]

Hi @DanteAmor - that's great! Any chance that there is a tweak to pick up the country if it's part of a cell with more text than just the country name?
e.g.

Formula works perfect if country name is alone in a cell, like below:

Street XYZ, 111

France

But if it has text around it it returns #VALUE, like below the below examples:

Madrid	Spain, Calle 1111
Street 222	Paris, France
Street 33, United Kingdom, London

Is there any way to tweak the formula to be able to pick up the country string if it has more text in the same cell?

Many many thanks

 

[DanteAmor]

Try this

Book1
	A	B
2	Finland	Hight
3	Spain	Medium
4	Argentina	low
5	United Kingdom	Medium
Country rating

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
7	Customer	No.	Score		AddressLine1	AddressLine2	AddressLine3	AddressLine4	AddressLine5	AddressLine6	AddressLine7
8	12	xx	Hight		11	New Finland	22	33
9	23	dd	Medium		Spain, Madrid	xx	xx	11	22	44
10	45	yy	low		1	1	1	1	1	La Argentina de	1
11	50	ss	Medium		2	2	Street 33, United Kingdom, London	2	2	2	2
Sheet1

Cell Formulas
Range	Formula
C8:C11	C8	{=INDEX(COUNTRYRATING[SCORE],MAX(IF(ISNUMBER(SEARCH(COUNTRYRATING[COUNTRY],Sheet1!E8:L8)),ROW(COUNTRYRATING[COUNTRY])))-1)}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[ChetManley]

Try this

Book1
	A	B
2	Finland	Hight
3	Spain	Medium
4	Argentina	low
5	United Kingdom	Medium
Country rating

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
7	Customer	No.	Score		AddressLine1	AddressLine2	AddressLine3	AddressLine4	AddressLine5	AddressLine6	AddressLine7
8	12	xx	Hight		11	New Finland	22	33
9	23	dd	Medium		Spain, Madrid	xx	xx	11	22	44
10	45	yy	low		1	1	1	1	1	La Argentina de	1
11	50	ss	Medium		2	2	Street 33, United Kingdom, London	2	2	2	2
Sheet1

Cell Formulas
Range	Formula
C8:C11	C8	{=INDEX(COUNTRYRATING[SCORE],MAX(IF(ISNUMBER(SEARCH(COUNTRYRATING[COUNTRY],Sheet1!E8:L8)),ROW(COUNTRYRATING[COUNTRY])))-1)}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Solution verified!

Fantastic job @DanteAmor - you have my many thanks!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.