# Populate Excel field based on two other fields

Customer Interaction                              Customer Interaction                         Customer Number
Customer Called                                      Customer Called                                     12345
Customer Not Called                              Customer Not Called                               12345
Customer Emailed                              Customer Emailed                           12345
Customer Called                                                                                                     11111
Customer Called                                                                                                   11111
Customer Called                                                                                                   11111
Customer Called                                                                                                   11111
Customer Called                                                                             11111

I have a spreadsheet that i manually enter data into a field.  The table above shows 3 columns.

1 - Client Interaction
2 - Decision
3 - Customer Number

I am trying to autopopulate Column2 (Decision) based on the customer number.  Basically, if Column1 (Customer Interaction) contains the term Customer Not Called I would like to populate Column2 with what is in Column1.   But only do this while the Customer number is consistent.  If this term does not show up in Column1 while the Customer Number is consistent then don't populate column2.  Does this make sense?

Basically, the Customer Number is the Do While condition and i can't figure that out.  Help please!
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Is CustomerNumber always incremented by 1 in sequence?

What I understand is, it could be 12345, 6789, 456, and could not be 2143, 648, 8637.

Right?
Author Commented:
Sorry, no customer number isn't incremented at all.  Those are just numbers i entered to fill space.  Customer number is the key.

It is something like "do this while customer number = (n)"
Commented:
I'm not sure about the "consistent" part, but I can start to break it down for you:

>if Column1 (Customer Interaction) contains the term Customer Not Called I would like to populate Column2 with what is in Column1

this consists of several parts:

"Column1 (Customer Interaction) contains the term Customer Not Called"
=FIND("Customer Not Called", A1)

This will either return a number of where it's found (position 1) or a N/A error. So the next step is to check the result to get a true or false to be used in an IF:

=ISNA({above condition})

If true, then the text is not found. So next is to create the decision:

=IF({above check}, "{skip}", A1)

If the text is not found, {skip} (probably just leave it empty so "", otherwise take the text and copy it.  So at this point we get:

=IF(ISNA( FIND("Customer Not Called", A1) ), "", A1)

Now, what's the next part?
>But only do this while the Customer number is consistent

Please clarify what you mean by consistent?
Author Commented:
So, i want to populate the second column while the customer number is the same.  So, the code would first look at customer number, and while customer number is 12345 (in the example) AND Customer Interaction contains the term "Customer Not Called" fill in Decision Column.

then the second customer number in the example is 11111, but column 1 does not contain the term "Customer Not Called" then don't fill out column 2.

In my working copy i have thousands of rows with hundreds of customer numbers.
Commented:
so it seems that the next part is to further restrict by comparing column C of the current row against the previous row, like this:

C2=C1

Since this will create a "complex" condition, we'll nest the functions:

Starting in B2:
=IF(C2<>C1,"",IF( ISNA( FIND("Customer Not Called", A2) ), A2, ""))

(Also, I think I had your original condition backwards so it's corrected here.)
I'm also assuming that the customer IDs are sorted so they appear in sequence.

Experts Exchange Solution brought to you by