Find Value in Excel Range 1

[Krell48]

Through a another process I assemble an Excel worksheet(1) with data in three columns:

PartNo. DwgRev PLRev

In another worksheet(2) I have the same three columns as well as other data.

What I need to do is start with the "PartNo." in Sheet(1), find if it exists in the other sheet(2) and if so, do the other two fields match.

If all match go to next. If not write to a third sheet(3).

I am struggling with this. Find vs vlookup or what will work. Any help would be appreciated.

 

[Loomah]

Hi
I think a little more info is required here.

1) What do you want to copy to sheet3?
2) Will part nums occur more than once in either sheet1 or sheet2?

Assuming that you want to copy sheet2 to sheet3 if the relative conditions are met and that there is only one possible part num on either sheet then the following could be your starting point.

Note that this is very rough and is unlikely to be your final solution.

Sub stance()
Dim c1 As Range, c2 As Range
Dim lRow As Long

lRow = 1
For Each c1 In Worksheets("Sheet1").UsedRange.Columns(1).Cells
    
    Set c2 = Worksheets("Sheet2").UsedRange.Columns(1).Cells _
        .Find(c1, LookIn:=xlValues)
    If Not c2 Is Nothing Then
        If c1.Offset(0, 1).Value <> c2.Offset(0, 1).Value Or _
            c1.Offset(0, 2).Value <> c2.Offset(0, 2).Value Then
            c2.EntireRow.Copy Worksheets(&quot;Sheet3&quot;).Rows(lRow)
            lRow = lRow + 1
        End If
    End If
Next
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Krell48]

Thanks for the quick response.

In answer to your questions:
1. I will be coping the three columns mentioned plus other data. If all three match I will ignore, if not I will use the PartNo. to query a database to place data in sheet 3. So in reality I am looking for a true/false in the compare.
2. There will be no duplicate PartNo.'s.

I will try your code. I was thinking I would be using offset as you suggested.

 

[TonyJollans]

Hi Krell48,

If you have column headings in sheets 1 and 2 which match you should be able to use Advanced Filtering in a slightly roundabaout way ..

Sheet2.Cells.Copy Sheet3.Cells

Sheet3.Range("A1:F33").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Sheets("Sheet1").Range("A1:C5"), Unique:=False
Sheet3.Cells.SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
Sheet3.ShowAllData

Obviously substituting your own ranges in the filter.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.