# Matching rows of data with another

#### everscern

##### Board Regular
Hi all,
this is the crux of my project. but i really am at wits end. pls help. I would very much appreciate.

i have 3 rows. one row is the variable.

column 1 = 1 2 3
column 2 = 1 2 3
column 3 = 4 5 6
with parameters 1,2 3 , column 2 will have a stock of 10.
with parameter 4,5,6 , column 3 will have a stock of 20.

now, comparing column 1, 2,3. column 1 and 2 is similar. Thus, column 1 must return a stock value of 10 too.

I need a macro for this. Thanks in advance.. :wink: [/list]

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### jindon

##### MrExcel MVP
How do you know 1 2 3 has 10 and 4 5 6 has 20?

When you say similar, 1 2 3 and 1 2 4 are similar?

#### everscern

##### Board Regular
sorry for my unclear explaination.
1=material A 2=material B 3=material C 4=material D 5=material E 6=material F

so in column two, we have 1,2,3. That means the toy is made of material A,B,C. and in the stock, we have ten of such toys. That's how i got the number 10.

in column 3, i have 4,5,6. meaning another toy is made of material D,E,F.
in the stock, there are twenty such toys.

So in column one, a buyer specifies the exact parameters of the first toy. the return should be 10 right?

so i need to get this in a macro? thanks

#### jindon

##### MrExcel MVP
so in column two, we have 1,2,3. That means the toy is made of material A,B,C. and in the stock, we have ten of such toys. That's how i got the number 10.

I understand the part before above.

It doesn't say anything about how 1,2,3 is connected to 10.

and is it 1 2 3 or 1,2,3 or 1, 2, 3 ?

You need to be very careful for this kind of question.

#### everscern

##### Board Regular

it will look something like this.

i want the question mark to be 10.

I meant there are 10 toys made up of material A,B,C

#### jindon

##### MrExcel MVP
Assuming you are entering A1:A3 and list start from C1...
Paste the code onto sheet module
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim txt As String, r As Range
With Target
If Intersect(.Cells,Range("a1:a3")) Is Nothing Then Exit Sub
If WorksheetFunction.CountA(Range("a1:a3"))<>3 Then Exit Sub
Application.EnableEvents = False
Range("a4") = Empty
txt = Range("a1").Text & "_" & Range("a2").Text & "_" & Range("a3").Text
For Each r In Range("c1",Cells(1,Columns.Count).End(xlToLeft))
If txt = r.Text & "_" & r.offset(1).Text & "_" & r.Offset(2).Text Then
Range("a4").Value = r.Offset(3).Value
Exit For
End If
Next
End With
Application.EnableEvents = True
End Sub``````

#### everscern

##### Board Regular

sorry to disturb u once more. But i did what you told me but i've got this dialog box. how do i link the macro to a button. my button is 'button2'

#### jindon

##### MrExcel MVP
Previous code runs automatically when you filled A1:A3

If you want to attach the code to CommandButton2_Click event then
change to
Code:
``````Private Sub CommandButton2_Click()
Dim txt As String, r As Range

If WorksheetFunction.CountA(Range("a1:a3"))<>3 Then Exit Sub
Range("a4") = Empty
txt = Range("a1").Text & "_" & Range("a2").Text & "_" & Range("a3").Text
For Each r In Range("c1",Cells(1,Columns.Count).End(xlToLeft))
If txt = r.Text & "_" & r.offset(1).Text & "_" & r.Offset(2).Text Then
Range("a4").Value = r.Offset(3).Value
Exit For
End If
Next
End With
End Sub``````

#### everscern

##### Board Regular
thanks so much. it really worked!!

#### everscern

##### Board Regular
Hi..I tried to tweak the code to suit my situation. But somehow everything turned out worse.

I used to following code:

Private Sub Button24_Click()
Dim txt As String, r As Range

If WorksheetFunction.CountA(Range("b3:b9")) <> 7 Then Exit Sub
Range("b11") = Empty
txt = Range("b3").Text & "_" & Range("b4").Text & "_" & Range("b5").Text& "_"& Range("b6").Text& "_"& Range("b7").Text& "_"& Range("b8").Text& "_"& Range("b9").Text
For Each r In Range("s3", Cells(1, Columns.Count).End(xlToLeft))
If txt = r.Text & "_" & r.Offset(1).Text & "_" & r.Offset(2).Text Then
Range("b11").Value = r.Offset(3).Value
Exit For
End If
Next
End With
End Sub

for

Replies
1
Views
164
Replies
4
Views
46
Replies
12
Views
334
Replies
3
Views
64
Replies
1
Views
65