Excel Forum

Change text in column to match earlier row

Tom Borg

Active Member

Members

Forum Posts: 5

Member Since:
September 12, 2018

Offline

September 12, 2018 - 3:16 am

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?

SunnyKow

Puchong, Malaysia

VIP

Members

Trusted Members

Forum Posts: 1432

Member Since:
June 25, 2016

Offline

September 12, 2018 - 9:19 am

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

Tom Borg

Active Member

Members

Forum Posts: 5

Member Since:
September 12, 2018

Offline

September 13, 2018 - 12:01 am

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