Excel - Fill column in list based on criteria

Closed
asc - Oct 15, 2010 at 07:03 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Dec 13, 2011 at 10:46 AM
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 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 16, 2010 at 09:08 PM
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.
what if the data is large and huge
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 13, 2011 at 10:46 AM
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.