Hello,
I have a scenario in which there is one excel 2007 workbook with three
sheets in it.
Sheet 1 is a master sheet with around 1900 rows and 15 columns of data.
Sheet 2 contains the qualifying data.
Sheet 3 is a result (or an output) sheet.

Sheet 2 has a column A with unique 5 digit integers.
The goal is to compare every cell of column A of Sheet 2 with Column B in
Sheet 1.
If there is a match, then copy that whole row of Sheet 1 into Sheet 3.
Repeat until all the cells in Column A of Sheet 2 have been compared with
Column B of Sheet 1.

What is the best way to achieve this?

Thanks much!

Hi,

I had this type of process regularly and what I like to do is like this:

option explicit
sub Process()
'Creating the variables and asign them the sheets you use

dim mySheet1 as worksheet, mySheet2 as worksheet
dim mySheet3 as worksheet, i as long, j as long, k as integer

set mysheet1 = worksheets("Sheet 1")
set mysheet2 = worksheets("Sheet 2")
set mysheet3 = worksheets("Sheet 3")

i = 2
j = 2
'Going through your data and comparing cells
while mysheet2.cells(i,1) < ""

'Compare the cells in the 2 worksheets
if mysheet2.cells(i,1) = mysheet1.cells(i,2) then

'Put the value of every column in sheet1 into sheet3
for k = 1 to 15
mysheet3 .cells(j,k) = mysheet1.cells(i,k)
next
j = j + 1

end if
i = i + 1

wend

If you have any questions, please let me know.
I hope this helps

"nshring" wrote:

Hello,
I have a scenario in which there is one excel 2007 workbook with three
sheets in it.
Sheet 1 is a master sheet with around 1900 rows and 15 columns of data.
Sheet 2 contains the qualifying data.
Sheet 3 is a result (or an output) sheet.

Sheet 2 has a column A with unique 5 digit integers.
The goal is to compare every cell of column A of Sheet 2 with Column B in
Sheet 1.
If there is a match, then copy that whole row of Sheet 1 into Sheet 3.
Repeat until all the cells in Column A of Sheet 2 have been compared with
Column B of Sheet 1.

What is the best way to achieve this?

Thanks much!

Thanks for your quick reply. I ran the code with little changes to column
numbers to represent the correct columns in sheets 1 and 2.

If mySheet2.Cells(i, 7) = mySheet1.Cells(i, 10)

Upon execution, I get a code 9 " Subscript out of range" error.
I wonder why it could be happening.


Thanks again for your input.

nshring
"japfvg" wrote:

Hi,

I had this type of process regularly and what I like to do is like this:

option explicit
sub Process()
'Creating the variables and asign them the sheets you use

dim mySheet1 as worksheet, mySheet2 as worksheet
dim mySheet3 as worksheet, i as long, j as long, k as integer

set mysheet1 = worksheets("Sheet 1")
set mysheet2 = worksheets("Sheet 2")
set mysheet3 = worksheets("Sheet 3")

i = 2
j = 2
'Going through your data and comparing cells
while mysheet2.cells(i,1) < ""

'Compare the cells in the 2 worksheets
if mysheet2.cells(i,1) = mysheet1.cells(i,2) then

'Put the value of every column in sheet1 into sheet3
for k = 1 to 15
mysheet3 .cells(j,k) = mysheet1.cells(i,k)
next
j = j + 1

end if
i = i + 1

wend

If you have any questions, please let me know.
I hope this helps

"nshring" wrote:

Hello,
I have a scenario in which there is one excel 2007 workbook with three
sheets in it.
Sheet 1 is a master sheet with around 1900 rows and 15 columns of data.
Sheet 2 contains the qualifying data.
Sheet 3 is a result (or an output) sheet.

Sheet 2 has a column A with unique 5 digit integers.
The goal is to compare every cell of column A of Sheet 2 with Column B in
Sheet 1.
If there is a match, then copy that whole row of Sheet 1 into Sheet 3.
Repeat until all the cells in Column A of Sheet 2 have been compared with
Column B of Sheet 1.

What is the best way to achieve this?

Thanks much!

this is really simple if you use a MATCH function in sheet2 column B
=IF(ISERROR(MATCH(A1,Sheet1!A:A,FALSE)),0,MATCH(A1 ,Sheet1!A:A,FALSE))

now your code just reads down the results

set target to range("B1")
rowindex = 0
do while target<""
if target,Value0 then
rowindex = rowindex +1
worksheets("Sheet1").rows(target.value).copy
worksheets("Sheet3").rows(worksheets("Sheet1")).Pa stepecial xlall
end if
set target = target.offset(1)
loop

"nshring" wrote:

Hello,
I have a scenario in which there is one excel 2007 workbook with three
sheets in it.
Sheet 1 is a master sheet with around 1900 rows and 15 columns of data.
Sheet 2 contains the qualifying data.
Sheet 3 is a result (or an output) sheet.

Sheet 2 has a column A with unique 5 digit integers.
The goal is to compare every cell of column A of Sheet 2 with Column B in
Sheet 1.
If there is a match, then copy that whole row of Sheet 1 into Sheet 3.
Repeat until all the cells in Column A of Sheet 2 have been compared with
Column B of Sheet 1.

What is the best way to achieve this?

Thanks much!

Patrick. Thank you for your reply. I am assuming that the Match function
changes the data in the cells of column B?
If that is the case, then I really want to keep the data in Sheet1 and
Sheet2 unchanged. I just want to copy over relevant data from Sheet1 into
Sheet3.

Please correct me if I am mistaken.

Thanks!

nshring

"Patrick Molloy" wrote:

this is really simple if you use a MATCH function in sheet2 column B
=IF(ISERROR(MATCH(A1,Sheet1!A:A,FALSE)),0,MATCH(A1 ,Sheet1!A:A,FALSE))

now your code just reads down the results

set target to range("B1")
rowindex = 0
do while target<""
if target,Value0 then
rowindex = rowindex +1
worksheets("Sheet1").rows(target.value).copy
worksheets("Sheet3").rows(worksheets("Sheet1")).Pa stepecial xlall
end if
set target = target.offset(1)
loop

"nshring" wrote:

Hello,
I have a scenario in which there is one excel 2007 workbook with three
sheets in it.
Sheet 1 is a master sheet with around 1900 rows and 15 columns of data.
Sheet 2 contains the qualifying data.
Sheet 3 is a result (or an output) sheet.

Sheet 2 has a column A with unique 5 digit integers.
The goal is to compare every cell of column A of Sheet 2 with Column B in
Sheet 1.
If there is a match, then copy that whole row of Sheet 1 into Sheet 3.
Repeat until all the cells in Column A of Sheet 2 have been compared with
Column B of Sheet 1.

What is the best way to achieve this?

Thanks much!