Hard to put a title to what I need, please read (Row color changed/color count)

[ExcelNewbie]

Hello there, new guy here with in need of help.

I have a database that I am working on that after extraction and conversion from another database. I need to color code each row based on the content of 3 different columns.


Column A | Column B | Column C | Column D

1 Name | Location1 | Location2 | Location3

2 Smith | Miami, Boston

3 Philips | Detroit, El Paso | Los Angeles, Seattle | San Francisco, Las Vegas

4 Mercer | Phoenix, Wyoming | Greece, Romania | Rome, Barcelona

5 Ward | Atlanta, New York | Singapore, Malaysia | Cambodia, Philippines


Basically, what I would like is to compare Columns B,C,D and change row color if the following criteria are made:

a) If only one entry was made under Column B,C,D then it will remain unchanged

b) If ALL Columns have entries belonging to the same country (ie: U.S.A) then change it to yellow

c) If 2 Columns have countries/cities from Europe then the row color will change to blue

d) If 2 Columns have countries/cities from Asia then the row color will change to orange


I don’t know how to do all these, taken into account that each Column containing cities/countries have two entries (ie: Miami, Boston).


I understand that there’s need to be 3 separate columns that will hold the USA cities, countries from Europe and countries from Asia in order to make comparison to the table shown above.


On another note, one these steps are done, and rows are colored I would like to make a total count of each row colored the same


Example:

Unchanged Total 1

Orange Total 1

Yellow Total 1

Blue Total 1


I know I’m asking for too much, but I can’t get this to “materialize” at all!

 

[dan_l]

It's doable. Probably the fastest way to achieve this is to get a list of your cities and make a table of what cities are part of what group (IE, USA, Europe). After that, you'll have to do a little text wrangling (like =LEFT(B2,FIND(",",B2,1)-1)) to extract the cities. Once done, it's just building a couple of tests, that mimic your rules, and apply some conditional formatting.

 

[DonMinter]

To add to what dan_l is saying, you should look up text manipulation key words like "mid, len, right, left, find, etc.). Once you understand how to use those, you can break out the data around the commas, and get rid of the comma as well as spaces.


I think you'll also need a lookup table to figure out which cities are where. I don't know of any "=europeCity(x)" function, but who knows with google and excel nowadays?