Suppose I have the following data:
| ADVISOR # | ADVISOR NAME |
| 123 | Steve |
| 123 | Steve |
| 123 | Steve |
| 456 | Steve |
| 456 | Steve |
| 456 | Steve |
| 456 | Steve |
| 456 | Stephen |
For any Advisor #, I want to search the Advisor Name column and if the name is different, change it to the first name entry for that Advisor #.
So in this example, there would be no change for Advisor # 123. However, for Advisor # 456, the name Stephen would be changed to Steve, which is the first entry for that Advisor #. Is there a way to do this?
Hi Tom
Assuming your data is in columns A and B starting from row 1.
In cell C2 enter
=IF(A2=A1,B1,B2)
Copy the formula down column C
Hope this helps
Sunny
Thanks, Sunny. That is close, but where it breaks down is when there are other, different names to be considered. For example, see the sample data below which adds other Advisor Names. When "Steve" is no longer the Advisor, the names get offset by one row (see the 4th column).
| ADVISOR # | ADVISOR NAME | CUSTOMER | |
| 123 | Steve | Rose B | Steve |
| 123 | Steve | Anne W | Steve |
| 123 | Steven | Ralph M | Steve |
| 456 | Steve | Charles R | Steve |
| 456 | Steve | Adelaide A | Steve |
| 456 | Steve | Julian R | Steve |
| 456 | Steve | Cathy D | Steve |
| 456 | Stephen | Isabella B | Clay |
| 789 | Clay | Stephen C | Clay |
| 789 | Clay | Beth B | Robert |
| 1011 | Robert | Carolyn M | Robert |
| 1011 | Robert | Sharon P | Robert |
| 1011 | Robert | Jill H | Robert |
| 1011 | Robert | Marissa C | Robert |
| 1011 | Robert | Sharon P | Robert |
| 1011 | Robert | Staci H | Robert |
| 1011 | Robert | Elizabeth H | Daniel |
| 1213 | Daniel | Gia B | Richard |
| 1415 | Richard | Debra S | Richard |
| 1415 | Richard | Maureen D | Timothy |
| 1617 | Timothy | Beverly K | Timothy |
| 1617 | Timothy | Charles R | Bryan |
| 1819 | Bryan | Deborah M | Bryan |
Ideas?
Thanks,
Tom
Hi Tom
I don't have any problem with the formula. Did you copy the formula to the correct cell?
Please refer my attachment. If it is not correct, please let us know what are the expected results in column F.
Sunny
