
Active Member

September 12, 2018

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?

VIP

Trusted Members

June 25, 2016


Active Member

September 12, 2018

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
1 Guest(s)
