Join two tables with conditions

[papi84]

Hello everybody
i have been a long time a fan of this forum and now i am part of it
so, i have the following problem that i just caaaaaaant' solve
i have two tables respectively in two sheets: Sheet1 and Sheet2. The first table looks something like that:
Product Support
Computer HP
Printer IBM
Telephone Motorola
Computer IBM
Computer Dell

and the second one like that:

Support OpSystem Version IntNumber
HP Win2003 ix86 1.02
HP Win2000 ix86 0.76
IBM Win2003 ix86 2.2
Dell Win2003 ix86 1.2
Motorolla Win2003 ix86 2.1
Dell Win2000 ix86 1.1.1

So, for example, i have to extract all the Supporters for the Product "Computer"....that means HP, IBM, Dell
then i have to use this data (HP, IBM, Dell) in the second table and to eliminate the rows that do not have HP, IBM, Dell as Support. That means that in the second table Motorola should be eliminated.
I 'google'-d for some quite time but i couldn't find something that could help me.
Please heeelp

[Bob Phillips]

You use the words 'extract' and 'eliminate', but what exactly is your objective? Is it a new sheet with the the remaining data on it, or something else?

[papi84]

Yes, my final goal is to make a new sheet from the "already cleaned" table2. i hope that helps

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long
Dim vecItems As Variant
Dim sh As Worksheet

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With Worksheets("Sheet1")

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
ReDim vecItems(1 To 1)
NextRow = 0
For i = 1 To LastRow

If .Cells(i, "A").Value = "Computer" Then

If IsError(Application.Match(.Cells(i, "B").Value, vecItems, 0)) Then

NextRow = NextRow + 1
ReDim Preserve vecItems(1 To NextRow)
vecItems(NextRow) = .Cells(i, "B").Value
End If
End If
Next i
End With

Set sh = Worksheets("Sheet3")
With Worksheets("Sheet2")

NextRow = 1
.Rows(1).Copy sh.Range("A1")
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

If Not IsError(Application.Match(.Cells(i, "A").Value, vecItems, 0)) Then

NextRow = NextRow + 1
Rows(i).Copy sh.Cells(NextRow, "A")
End If
Next i
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]

[papi84]

yeeei thank you very much.....you really helped me