Excel - Replace cell value based on value in another column

Question

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'm trying to solve:

I have last names in the column:

Smith
Jones
Taylor
and etc.

I have another column with identifiers, for example.

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.

Answer 1

He can do it.

• Extract column identifiers with the character "_" using "Text to Columns"

Column
Smith
Jones
Taylor

column

B
Smith
Taylor
Jones

column

C id
B
C

Now write the following formula

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

Hope you do

Answer 2

If you have a common meaning between the two lists, you can use Vlookup () (see link for best examples) to match 2.

Using your example, you have Sheet1, which reads:
Smith
Jones
Taylor

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

Then do the following:

• 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.
• (Optional) If you want to completely get rid of the original values, you can copy and paste the values ​​and delete the original column OR just hide the original column.

Is this what you are looking for?

Answer 3

To answer this question in a general situation, suppose you have two columns:

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

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

The classic case is that you have two pretty similar columns with only a slight difference. This may be a typo or some other errors. What you want to do is replace the value of the cells in column A with it in column B, if we assume that column B is what you need.

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

Start with the new C column, combined with the match function and wildcard, filling in C1 with the following formula:

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

you now retrieve the row_index of each approximate match corresponding to column A. Then, using

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

which returns you Smith Stone in cell C1. The same goes for other cells.

* Note. The biggest drawback of this method is that it requires an approximate match in B for each cell in A.

Answer 4

I had 2 columns, one was the name of the supplier, the second was the address of the supplier, but it also had the name of the supplier. I needed to remove the name of the supplier, which would leave me with the address of the supplier without a name.

For example, if there was “Con Ed” in one column, the next column had “Con Ed 123 Street, New York, NY 11111”, I needed to replace “Con Ed” with zero and just have “123 Street, New York, NY 11111” in a collumn.

This is what I have done, and I will also let you know what you can do to achieve your goal.

First, I created a new column and set it to Length (using the LEN () function) of the provider name.

Then I created a new column and set it to length at the provider address.

The third column was also configured to use the Right () function. If the text is the supplier’s address and the length is the length of the address minus the length of the supplier’s name.

So think: "Con Ed 123 Street, New York, NY 11111" 1st column: (vendor length): len (Con Ed) = 6 2nd column: (vendor address length): len (Con Ed 123 Street , New York, NY 11111) = 37 3rd column: Right ([Supplier Address], 31 (37-6)) = ", New York, New York 11111"

Finally, I just applied the TRIM function to the third column.

In your case, you just want to use the Concatenate function to add the source column and the new column.

So, in the end you will have the following columns:

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

Column 2: identifiers: (Smith_ID, Taylor_B, Jones_C)

Column 3: Flax ([Names])

Column 4: Flax ([ID])

Column5: Function "replace" = TRIM (on the right ([IDs], ([Column4] - [Column3]))

Column 6: Row inserted again = Merge ([Column3], [Column5])

Answer 5

=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)

I don't have time to explain this, but if you know how to write formulas in excel, this should be helpful. Sorry for not explaining.

Answer 6

We hope that you use excel and want to replace the value of one raw cell from another cell of the same raw, and this must be done for the entire column and not form the selected raw.

In this case, you can copy the values ​​that you want to save (for example, the identifier column), and then display them in the Last name column.

If the values ​​are in the same raw, then there is no problem, and from your query it seems that the values ​​are in the same raw.

Answer 7

I use this formula when searching for the letter “s” in cell B1, if it is found, I combine the word “add” with the value of cell A1, otherwise I just accept the value of cell A1.

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