Comparing and pasting

Closed
Himanshu - Oct 30, 2009 at 02:40 AM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Oct 30, 2009 at 04:50 AM
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......................

3 replies

Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Oct 30, 2009 at 03:22 AM
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
1
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.
0
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Oct 30, 2009 at 04:50 AM
Hello

Use Vlookup and FALSE as the last argument
0