Find then If then Replace

[Guest]

hi , i have 2 x files
file 1
col1 col2 col3 col4
abc 123 text1a
def 456 text2a
def 789 text3a
ghj 456 text4a

file2
col1 col2 col3 col4
abc 123 text1
def 456 text2
def 789 text3
ghj 456 text4

after i combine them i am trying to
1. if data in col1 and col2 are the same then take data in col3 (was from
file2) and replace it into col3 of file 1.
2.then delete that row (ie the row originally from file2)
end result like this
file 1
col1 col2 col3 col4
abc 123 text1 text1a
def 456 text2 text2a
def 789 text3 text3a
ghj 456 text4 text4a

hope that makes sense.

 

[Dave Peterson]

How about just retrieving the info from file2 and putting it in column 3?

=INDEX(Sheet2!$C$1:$C$100,
MATCH(1,((Sheet2!$A$1:$A$100=A1)*(Sheet2!$B$1:$B$100=B1)),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

And adjust the range to match your data--but don't use the whole column.


Convert the formulas to values by:
Select column C
edit|copy
edit|paste special|values

Replace the errors (#N/A's) with empty cells.
Edit|replace
what: #n/a
with: (leave blank)
replace all

 

[Guest]

give me a couple of days to try this out ..