Comparing and pastingClosed

Question

Hello,

I have 4 columns.

Col1....|....Col2.....|....Col3..|....Col4.....|
----------|--------------|----------|---------------|
A............SSHH.............B...................... 
B............XXYY..............C......................

Now the macro should compare Col1 and Col3. If any value is found in Col3, den paste the text present at Col2 in Col4.

For e.g. The macro compares Col1 and Col3. It finds B in Col3 and simultaneously it should paste the value XXYY in Col4 (i.e. beside B). It should look like shown below after macro runs.

Col1....|....Col2.....|....Col3..|....Col4.....|
----------|--------------|----------|---------------|
A............SSHH.............B...........XXYY.......
B............XXYY..............C......................

Excelguru · Answer

Hello

Try Recording
Put the formula =IF(ISERROR(LOOKUP(C2,A:A,B:B)),"",LOOKUP(C2,A:A,B:B)) in D2
Drag it down
select column D
copy and paste special values
stop recording

Himanshu · Answer

Hi ExcelGuru,

Thanks very much for the formulae. But when i try to give strings in Column 1, only the 1st alphabet (i.e. 'S' in Sorry ) is picked up for comparison. Hence the strings in Column 3 with words Signature, Sight all are getting the same value as Sorry. This should not happen. The whole string should be compared.

Below take a look at the below example.


Present Case

Col1....|....Col2.....|....Col3..|....Col4.....| 
----------|--------------|----------|---------------| 
A............SSHH.............Signature.....XXYY................. 
Sorry............XXYY..............C...................... 


Expected Result 

Col1....|....Col2.....|....Col3..|....Col4.....| 
----------|--------------|----------|---------------| 
A............SSHH.............Signature.....False................. 
Sorry............XXYY..............C......................
Same.............PPQQ..............Sorry.....XXYY.................

I hope it is clear. Please let me know if you need any further information.

Excelguru · Answer

Hello

Use Vlookup and FALSE as the last argument

Comparing and pasting

3 responses

Similar discussions

Subscribe To Our Newsletter!