CODEWITHSUNDEEP
excel
Tamas Karpati
Tamas Karpati

Reputation: 85

Excel - Replace cell value based on a value in another column

I am wondering if there is a way to replace a cell value from another list without having to write a VB script.

Here is the problem I am trying to resolve:

I have last names in a column:

Smith
Jones
Taylor
etc.

I have another column with IDs e.g.

Smith_ID
Taylor_B
Jones_C

I would like to replace Smith with Smith_ID, Jones with Jones_C etc. Obviously my list is much longer than 3 entries.

Upvotes: 5

Views: 108700

Answers (7)

Nasrin
Nasrin

Reputation: 1

I use this formula when searching cell B1 for the letter "s", if found then I concatenate the word "addition" to the value of cell A1 otherwise just take the value of cell A1.

=IF(ISNUMBER(SEARCH("s",B1))=TRUE,CONCAT(A1, "  addition"),A1)

Upvotes: 0

Evan Knutson
Evan Knutson

Reputation: 11

I had 2 columns, one was an Vendor Name, the 2nd was the Vendor Address but it also contained the Vendor Name. I needed to strip out the Vendor Name which would leave me with the Vendor Address without the Name.

For example if one column had "Con Ed", the next column had "Con Ed 123 Street, New York, NY 11111", I needed to replace "Con Ed" with null and just have "123 Street, New York, NY 11111" in a column.

Here's what I did and I will also let you know what you can do to accomplish your goal.

First I created a new column and set that to the Length (using the LEN() function) of the Vendor Name.

Then I created another new column and set that to the length of the Vendor Address.

A third column and was set to use the Right() function. Where the text is the Vendor Address and the length is the Length of the Address minus the length of the Vendor Name.

So consider: "Con Ed 123 Street, New York, NY 11111" 1st column: (Length of Vendor): len(Con Ed) = 6 2nd column: (Length of Vendor address): len(Con Ed 123 Street, New York, NY 11111) = 37 3rd Column: Right([Vendor Address], 31 (37-6)) = ", New York, NY 11111"

Lastly I just applied the TRIM function to the 3rd column.

In your case you would just want to use the Concatenate function to append the the original column & the new column.

So in summary you'll have the below columns:

Column 1: Names: (Smith,Jones,Taylor)

Column2: IDs: (Smith_ID,Taylor_B,Jones_C)

Column3: Len([Names])

Column4: Len([IDs])

Column5: The "replace" function = TRIM(Right([IDs], ([Column4] -[Column3] ))

Column6: String put back together =Concatenate([Column3],[Column5])

Upvotes: 1

user7899662
user7899662

Reputation: 1

Hope you are using excel and in that you want to replace value of one cell of raw from another cell of same raw and this has to be done for entire column and not form selected raw.

In this case you can copy the values which you want to kept (i.e. ID column) and then pest the same in column Last name.

If values are in same raw then there is no problem and from your query it appears that values are in same raw.

Upvotes: 0

Sai
Sai

Reputation: 11

To answer this question under general situation, suppose you have two columns as followed:

A Smith,Jones,Taylor,Kevin,Yell,Ashton...

B John Harvard, Yell USC, Kevin Cruise, Ashton Bond, Smith Stone, Taylor Shift...

The classic case here is that you have two pretty much alike columns only with slightly difference. It could be a typo or some other mistakes. What you want to do is to replace cells'value in column A by them in column B, if we assume that column B is the right thing you want.

So for each cell in A, you want to know the position of its approximate match in column B. For example, given A1(Smith), you want to know where Smith Stone is, in this case, B5.

Begin with a new column C, combined with match function and wildcard, filled C1 by the following formula:

row_index=match(A1&"",$B$1:$B$6,0)

you now extract row_index of every approximate match corresponding to column A. Then by using

=index($B$1:$B$6,row_index,0)

which returns you "Smith Stone" in cell C1. Same for other cells.

*Remark: The biggest flaw for this method is that it kind of requires the approximate match in B for each cell in A should be unique.

Upvotes: 1

Mariano
Mariano

Reputation: 1

=INDEX('All_Linguists_All_ProjectsFri J'!$DA$2:'All_Linguists_All_ProjectsFri J'!$D$71355,MATCH(A2,'All_Linguists_All_ProjectsFri J'!C:C,FALSE),1)

Dont have time to explain it, but if you know how to write formulas in excel, this should be useful. Sorry for not explaining.

Upvotes: 0

Sakir SEN
Sakir SEN

Reputation: 284

He can do it.

  • Extract column IDs with "_" character by "Text to Columns"

A column
Smith
Jones
Taylor

B column
Smith
Taylor
Jones

C column
ID
B
C

Now write to following formula

=CONCATENATE(A1;"_";VLOOKUP(A1;$B$1:$C$3;2;FALSE))

I hope you do

Upvotes: 2

PowerUser
PowerUser

Reputation: 11791

If you have a common value between the 2 lists, then you can use Vlookup() (see link for better examples) to match the 2.

Using your example, you have Sheet1 that says:
Smith
Jones
Taylor

In Sheet2, you have:
Smith 4
Jones 9
Taylor 6

Then do this:

  1. In Sheet1, add a column with the formula =vlookup(A1,Sheet2!$A$3:$B$3,2,False). This will return 4 for the 1st row, 9 for the 2nd, etc.
  2. (Optional) If you want to get rid of the original values completely, you can then copy&paste values and delete the original column OR just hide the original column.

Is this what you're looking for?

Upvotes: 1

Related Questions