Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and delete rows macro | Excel Discussion (Misc queries) | |||
Find and delete all other rows | Excel Discussion (Misc queries) | |||
Find & delete rows with ID's from another sheet. | Excel Discussion (Misc queries) | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
Find item and delete rows | Excel Programming |