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.
1
what if the data is large and huge
0
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.
0