Compare two excel files to change elements in cells

All we need is an easy explanation of the problem, so here it is.

I have two excel files, A and B.

A is a dataframe with a header.

B has two columns, the first one has the original name and the second one has new name

A:

    sam1 sam2 sam3 sam4 ....
p1  0    1    2    1
p2  2    3    3    4
p3  5    6    1    2 
....

B:

sam1 newname1
sam2 newname2
sam3 newname3
sam4 newname4
....

I want to change the header of A for "newnames" in B.

So the output will be

A’:

     newname1 newname2 newname3 newname4 ....
p1  0         1        2        1
p2  2         3        3        4
p3  5         6        1        2 
....

Note that B can have more "original name – newname" rows than the number of headers in A.
So the comparison is only done with names in the headers in A.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Try these formulas:

=VLOOKUP(B2,$A$8:$B$11,2,0)

Compare two excel files to change elements in cells

=SUMPRODUCT(($A15=$A$3:$A$5)*(B$2=$B$2:$E$2),$B$3:$E$5)

Compare two excel files to change elements in cells

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply