Auto Populate

**chamce** · 02-17-2010, 01:12 PM

Hello,

I'm having some problems wth formula to auto populating column D-G. Based on Columns B and C, I would like to identify which State is activated or inactivated. If it is in both column B and C, I want to document it as "AI" so I can trouble shoot that IT#.

So far, I used =IF(SEARCH("TX",B2)=1,"A") function and it works if I only applied to only one column but not both.

IT#,Activat,Inactive,TX,VA,OK,LA
1,TX, LA, VA, OK,TX,AI,A,A,A
2,LA TX,TX,AI,null,null,A
3,OK,VA,null,I,A,null

Thanks in advance.

**JBeaucaire** · 02-17-2010, 02:05 PM

In D2 and copied down:

=IF(AND(ISNUMBER(SEARCH(D$1, $B2)), D$1=$C2), "AI", "null")

I don't see instructions to explain the "A" and "I" values in the other columns, but maybe you can take it from there?

**chamce** · 02-17-2010, 02:56 PM

Hi JBeaucaire,

Thank you for your prompt response. I forgot to explain the process. Active and Inactive columns get updated and when I changed the inactive column, the formula you provided didn't worked. I think it is because D$!=$C2 or it may need different formula. I've been trying to manipulate that section but no luck so far. D1-G1 doesn't get changed but column B&C gets changed.

Thank you.

**JBeaucaire** · 02-17-2010, 03:24 PM

There was no exclamation point in my formula, and it was only designed so far for column D.

That's a lot of IFs, but try this in D2 and copy down and across the table:

=IF(NOT(OR(ISNUMBER(SEARCH(D$1,$B2)),D$1=$C2)), "null", IF(ISNUMBER(SEARCH(D$1,$B2)), "A", "") & IF(D$1=$C2, "I", ""))

**chamce** · 02-17-2010, 04:34 PM

Thank you for your answer and it works on column D, but I don't think that will not work since I have many other States to work on. What other options is available?

Thanks bunch!

**JBeaucaire** · 02-17-2010, 04:37 PM

The formula above should continue to work no matter how many state columns you add. It works for 4 columns (D:G), it would work for 40.

**chamce** · 02-17-2010, 05:05 PM

Please see the attached document and change the information around and sometime it works and some times, it doesn't work. Maybe it is me. :-)

Based on your information, I changed around your formula and now it works!

=IF(ISNUMBER(SEARCH(D$1,$B2)), "A", "") &" "& IF(ISNUMBER(SEARCH(D$1,$C2)), "I", "")

You are awesome and thanks bunch!

**JBeaucaire** · 02-17-2010, 06:27 PM

Your first sample only showed one value INACTIVE at a time, so I used an appropriately simple comparison, but you need the complicated comparison in both active and inactive.

Put this in D2, then copy down/across:

=IF(NOT(OR(ISNUMBER(SEARCH(D$1,$B2)), ISNUMBER(SEARCH(D$1,$C2)))), "null", IF(ISNUMBER(SEARCH(D$1,$B2)), "A", "") & IF(ISNUMBER(SEARCH(D$1,$C2)), "I", ""))

**chamce** · 02-18-2010, 09:51 AM

Thank you and it works great!

**JBeaucaire** · 02-18-2010, 02:07 PM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)