PDA

View Full Version : Compare and Copy



mchilapur
05-17-2014, 10:28 AM
Hello All,
Can you plz help me out with below situation.

Below i have excel columns with headers 10 to 100 as shown.


10
20
30
40
50
60
70
80
90
100



And i have a input data as below (Top Row is header and 2nd row is values),


10
20
50
70
90
100


A
B
C
D
E
F



Now i need as VBA code to Compare Input data headers with standard headers (i,e 10 to 100) and paste the respective values below. If the Input header is not having any of the number, then it must return 'Nil' in the main header.
I simple words, i need output as below,



10
20
30
40
50
60
70
80
90
100


A
B
Nil
Nil
C
Nil
D
Nil
E
F



Plz provide me the VBA code.

Thanks in advance
Madvesh

Trebor76
05-18-2014, 02:58 AM
Hi mchilapur,

Welcome to the forum!!

You don't need a macro for this. If your headers are in cells A1:J1 and the matching data is in A4:F5, you can put the following formula into cell A2...

=IFERROR(HLOOKUP(A1,$A$4:$F$5,2,FALSE),"Nil")

...and copy it across to cell J2 (just change the ranges to suit).

Regards,

Robert

mchilapur
05-18-2014, 09:49 AM
Hello Robert..
I wonder how could I forgot HLOOKUP..:(
Thanks for your guidance...:)

Best regards.
Madvesh C