Match and Extract Data

  • Thread starter Thread starter K
  • Start date Start date
K

K

I have 3 Sheets. In Sheet1 I have headings in row 1 and then data
which starts from row 2 and is like (see below)
A B C D….col
xx 333 yyy record
xx 444 yyy power
xx 222 yyy number
zz 656 yyy record
zz 454 yyy power
zz 956 yyy number
yy 888 yyy record
yy 999 yyy power

Same like Sheet1, In Sheet2 I have headings in row 1 and then data
which starts from row2 and is like (see below)
A ….col
xx
yy

I want macro assigned to button in Sheet2 which should check Sheet2
column A cells value and match it with cells of column A of Sheet1.
And if value matches then it should produce result in Sheet3 from row
2 like (see below)

A B C ….col
333 yyy record
444 yyy power
222 yyy number
888 yyy record
999 yyy power

The above result is actually the values in same row of matched values
from column B to D of Sheet1. Please can any friend can help me in
this
 
I have 3 Sheets. In Sheet1 I have headings in row 1 and then data
which starts from row 2 and is like (see below)
A B C D….col
xx 333 yyy record
xx 444 yyy power
xx 222 yyy number
zz 656 yyy record
zz 454 yyy power
zz 956 yyy number
yy 888 yyy record
yy 999 yyy power

Same like Sheet1, In Sheet2 I have headings in row 1 and then data
which starts from row2 and is like (see below)
A ….col
xx
yy

I want macro assigned to button in Sheet2 which should check Sheet2
column A cells value and match it with cells of column A of Sheet1.
And if value matches then it should produce result in Sheet3 from row
2 like (see below)

A B C ….col
333 yyy record
444 yyy power
222 yyy number
888 yyy record
999 yyy power

The above result is actually the values in same row of matched values
from column B to D of Sheet1. Please can any friend can help me in
this
Click to expand...

Assuming that there is a blank cell below your data in column A in
Sheet1 and Sheet2, you may try the following macro:

Sub Button1_Click()
Dim row1, row2, row3 As Integer
row3 = 2
row2 = 2
While Worksheets("Sheet2").Cells(row2, 1) <> ""
row1 = 2
While Worksheets("Sheet1").Cells(row1, 1) <> ""
If Worksheets("Sheet1").Cells(row1, 1) =
Worksheets("Sheet2").Cells(row2, 1) Then
Worksheets("Sheet3").Cells(row3, 1) =
Worksheets("Sheet1").Cells(row1, 2)
Worksheets("Sheet3").Cells(row3, 2) =
Worksheets("Sheet1").Cells(row1, 3)
Worksheets("Sheet3").Cells(row3, 3) =
Worksheets("Sheet1").Cells(row1, 4)
row3 = row3 + 1
End If
row1 = row1 + 1
Wend
row2 = row2 + 1
Wend
End Sub

Hope this helps / Lars-Åke
 
Back
Top