Link to home
Start Free TrialLog in
Avatar of farmingtonis
farmingtonis

asked on

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!
Avatar of Karthik Ramachandran
Karthik Ramachandran

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?
Avatar of farmingtonis

ASKER

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)"
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?
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.
ASKER CERTIFIED SOLUTION
Avatar of rspahitz
rspahitz
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial