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.
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!
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!
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)"
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?
>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?
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What I understand is, it could be 12345, 6789, 456, and could not be 2143, 648, 8637.
Right?