Excel - Fill column in list based on criteria [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,

I am trying to fill a column in a list, based on criteria.
Example:

In Table 1, I configure the criteria:
If string contais "aaa" OR "bbb" OR "ccc" then "A"
If string contais "xxx" OR "yyy" OR "zzz" then "B"
...

In Table 2, I want to fill column B, based on the value of column A - looking for the right value in table 1

How can I do that?
I tried vlookup & match, but I would like to add criteria to Table 1 when needed.

Thank you very much

3 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
798
suppose your data is some thing like this from A1 down


aaasd
aaasd
aaasdfg
bbb
bbbbb
bbbk
ccccc
ccccc
xxxxx
xxxxx
yyyaa
zzzer
zzz


then in B1 copy this formula

=IF(OR(ISNUMBER(SEARCH("aaa",A1)),ISNUMBER(SEARCH("bbb",A1)),ISNUMBER(SEARCH("ccc",A1))),"A",IF(OR(ISNUMBER(SEARCH("xxx",A1)),ISNUMBER(SEARCH("yyy",A1)),ISNUMBER(SEARCH("zzz",A1))),"B",""))

copy B1 down as long as data is there in A.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

what if the data is large and huge
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
798
I think you may have to write a macro. But anyhow if the data is huge there will be problem of overflow. how huge do you expect . you have to separate the criteria.

if you post a sheet with aleast ten critera in each cateogry. some thling can be attempted. use speedyshare.com to upload the file and give the web page for downloading.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!