Comparing two columns

S

seven

Joined
Apr 8, 2011
Messages
4
Reaction score
0
Hello experts,

i have two sheets in sheet 1 is data like

Column A Column B
SEC020 DIM01
SEC020 DIM02
SEC021 DIM01

In sheet 2 i have data like
Column A Column B Column C
SEC020 DIM01 100,2
SEC050 DIM03 234,5
SEC021 DIM04 56,7

I need macro, which will compare columns A and B from two sheets and if the data from both columns in the same line are euqual, macro will write value from column C sheet 2 to sheet1 in the column which user specifies.

Result from previous example would then be:
(lets say user selected column H in Sheet1):

Column A Column B Column H
SEC020 DIM01 100,2
SEC020 DIM02
SEC021 DIM01

Any help please..
 
in sheet 1, row 1, column H, you set this function:
=vlookup(a1;Sheet2:a$1$:c$4$;3;false)

and if you copy this formula down, a1 will change to a2 etc but Sheet2:a$1$:c$4$ will not change.

Or type =vlookup(
and press fx button for help.

We could say
=vlookup(a;b;c;d)
Where a stand for ref. cell
b stand for the range (your table in sheet 2)
c stand for where your value is. 3 is third column in your table. In your case column C
d stand for exact match or not. Exact match = false.

If your refresh data in sheet 2, sheet 1 will automatically change.

If you want user to specify colum. You record these step above

How do you record macro
develop - macro - record macro (RELATIVE).
And you choose name for the macro.

When you have finished, you press stop recording.
When you want to run your macro. Select -> developer - macro- run.

If you choose relative macro. The macro will run in activate cell.
 
Yes, I tried withf vlookup, but I need to compare 2 columns in sheet1 (A and B) with two columns in sheet 2. :cry:
 
OK, back to basic.
I will make it easier so stand for vlookup()

in sheet 1, cell H1
=If(condition 1 = "A1" And condiition 2 ="B1";X;"")
where X is vlookup()

Again I am at home, so I cannot test it in excel.
you can also
=if(condition1 ="A1";=if(condition2="B1";x;"")"")

If this is correct you just record these steps in macro.
If you want to choose your cell, you record relative macro.

(before you record your macro, write down the steps you will make. That will make it faster when you run the macro)
 
Hello Seven,
Sorry this is more tricky than I thought at the beginning.

Best solution is to use concatenate to combine columnA and columnB.
I use ColumnA to concatenate, maybe you prefer to hide columnA.

Sheet 1
Will give this result:

Concatenate ColumnA ColumnB ColumnC
SEC020DIM01 SEC020 DIM01 100,2
SEC020DIM05 SEC020 DIM05
SEC021DIM01 SEC021 DIM01 56,7

Sheet 2

Concatenate ColumnA ColumnB ColumnC
SEC020DIM01 SEC020 DIM01 100,2
SEC020DIM02 SEC020 DIM02 234,5
SEC021DIM01 SEC021 DIM01 56,7


Sub ConcatenateAndVlookup()
'
' Concatenate ColumnA and ColumnB to use Vlookup Macro
' This macro will create new column in sheet 1, name Concatenate.
' This macro will create new column in sheet 2, name Concatenate.
' This macro will lookup for value in sheet 2 and give it in column C in sheet 1.
'

'
Sheets("Sheet2").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Concatenate"
Range("A2").Select
' Concatenate = combine two cells
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],RC[2])"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A4")
Range("A2:A4").Select
Sheets("Sheet1").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Concatenate"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],RC[2])"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A4")
Range("A2:A4").Select
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-3],Sheet2!C[-3]:C,4,FALSE),"""")"
' IfError => If no result then " "
' Vlookup, lookup concatenate cell
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D5")
Range("D2:D5").Select
End Sub
 
Back
Top