Find according to sheet 2, Delete rows in COL A
 

Hello all

This is probably easy for most of you, so I'll try and be brief. I a
totally stumped.

I have a spreadsheet. In sheet 2 COL A, I have a list of 20000
invoice numbers and only invoice numbers (one, 8 character invoic
number per cell). In column A of sheet 1, I have invoice numbers
spaces, and a little other data (raw text conversion) in rows.

I need to view in the invoice numbers from COL A sheet 1, that aren'
in COL A sheet 2. I tried doing an extended find and delete accordin
to COL A sheet 2 from a macro I wrote awhile back (to bold row
according to value) but it is far from working out.

In a nutshell: "Check sheet 2 COL A with sheet 1 COL A. If there ar
any matches, DELETE that row from sheet 1 COL A." Sheet 2 COL A i
just a checklist.

Thank you for reading.
Pa

--
Message posted from http://www.ExcelForum.com

Find according to sheet 2, Delete rows in COL A
 

You don't need a macro, a bunch of "vlookup" functions will do:

In you sheet 1 insert a column, say, column B. In Cell [B2] (assuming your first row is your heading), type in:

=vlookup($A2,Sheet2!$A$1:$A$20000,1,false)

copy this formula to the bottom of your sheet 1 list in column B. The get rid of the formulae by doing copy pastespecial values. The ones that are not on your sheet 2 list are "#N/A", so just use a filter or a sort to locate and delete them.


"Rubix³ " wrote:

Hello all

This is probably easy for most of you, so I'll try and be brief. I am
totally stumped.

I have a spreadsheet. In sheet 2 COL A, I have a list of 20000+
invoice numbers and only invoice numbers (one, 8 character invoice
number per cell). In column A of sheet 1, I have invoice numbers,
spaces, and a little other data (raw text conversion) in rows.

I need to view in the invoice numbers from COL A sheet 1, that aren't
in COL A sheet 2. I tried doing an extended find and delete according
to COL A sheet 2 from a macro I wrote awhile back (to bold rows
according to value) but it is far from working out.

In a nutshell: "Check sheet 2 COL A with sheet 1 COL A. If there are
any matches, DELETE that row from sheet 1 COL A." Sheet 2 COL A is
just a checklist.

Thank you for reading.
Pat


---
Message posted from http://www.ExcelForum.com/

Find according to sheet 2, Delete rows in COL A
 

Hey!

The reason I didn't do a VLOOKUP to begin with was because my initia
spreadsheet is incredibly messy. It's over 40K rows long, converte
from a raw text file - and in sheet A, there's more than invoic
numbers in the cells - so we'll see.

I'm going to try this once I get home. Thank you for your reply. I'l
post back either way.

Take care
Pa

--
Message posted from http://www.ExcelForum.com

Find according to sheet 2, Delete rows in COL A
 

Alright ~× excellent. You got it. I used the formula

=IF(ISNA(VLOOKUP(VALUE(LEFT(A1,8)),Sheet2!$A$1:$A$ 20000,1,FALSE)),"Delete",""


Sorted col B descending - deleted the "delete" rows, and I was i
business.

Thanks a lot.

Pat
:

--
Message posted from http://www.ExcelForum.com