combining two columns of data

[Guest]

I have a column listing 10,000 part numbers.
I have a second column of P/N's that are obsolete and I need to delete these
P/N's from the first column.
In the past I had shaded my list of obsolete numbers, pasted them at the
bottom of the column of P/N's and sorted. Anywhere I saw a shaded P/N I
deleted two lines.
I'll be there all day with this list.
Any ideas on automating this?

 

[T. Valko]

One way:

PN's in column A
obsolete PN's in column E

Enter this formula in column B and copy down to the end of data in column A:

=ISNUMBER(MATCH(A1,E:E,0))

Select both column A and column B
Sort on column B ascending
This will place all obsolete PN's at the bottom of the list
Delete all rows at the bottom of the list where column B = TRUE
Delete column B

Biff

 

[Guest]

With 10,000 # in column A and obsolete # in B try:

(Backup your w/sheet first!)

Sub Delete_Obsolete()

Dim ws1 As Worksheet
Dim irow As Long
Dim Lastrow As Long
Dim col As Integer
Dim delrng As Range

Set ws1 = Worksheets("Sheet1")


With ws1
Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set delrng = .Range("B2:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
For irow = Lastrow To 2 Step -1
If Application.CountIf(delrng, .Cells(irow, "A")) = 1 Then
.Rows(irow).Delete
End If
Next irow

End With


End Sub

 

[Tim Shnell]

I have a column listing 10,000 part numbers.
I have a second column of P/N's that are obsolete and I need to delete these
P/N's from the first column.
In the past I had shaded my list of obsolete numbers, pasted them at the
bottom of the column of P/N's and sorted. Anywhere I saw a shaded P/N I
deleted two lines.
I'll be there all day with this list.
Any ideas on automating this?

One way to do this is to use a VLOOKUP to identify which P/N's are in
the obsolete list.

For example, create a 3rd column with this formula in it =VLOOKUP(A1,$B
$1:$B$10000,1,0). This should return #N/A for non-obsolete values and
the P/N for the obsolete values. Then sort by the 3rd column to group
all of the numbers together and delete all of the entries that don't
show #N/A in the 3rd column.