Previous Topic Next Topic

Compare & Delete - EXCEL Macro Help

Closed
MickyFinn Posts 1 Registration date Thursday March 4, 2010 Status Member Last seen March 4, 2010 - Mar 4, 2010 at 01:01 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 4, 2010 at 01:17 PM
Hello,
I am new to Macro's and VB. I have been trying to create a macro to automate a task in Excel.
I have an Excel workbook with data on two different sheets, sheet 1 is labeled "Pending Order Data" and sheet 2 is labeled "Already Shipped". Sheet 1 has data in columns A-I, and sheet 2 has data in column A. I need to compare the data (sales order numbers) in sheet 2 column A to the data (sales order numbers) in sheet 1 column C and delete the entire 9-column row (A thru I) in sheet 1 if the numbers match. There are somtimes multiple rows in sheet 1 that will match the data on sheet 2. None of the data on sheet 2 is repeated. The number of rows varies on both sheets, usually between 100 and 300. The column headers in sheet 1 are:
Sales Order Date|Promise Date|Sales Order#|Cust.Number|BillToName|Cust.PO#|ItemNumber|ItemDesc.|Qty Ordered

Any help you can give me would be greatly appreciated.
Mike

1 reply

Answer 1 / 1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Mar 4, 2010 at 01:17 PM
Well for this you do not really need macro. You can do a couple of step1

Lets say your data in sheet 1 (A:I) starts from row 2, then in J2 you can write

=IF(ISERROR(MATCH(C2, Sheet2!A:A,0)),0, 1)

Drag this formula to the last row of your data


Now apply filter to sheet 1 and filter on this new column for value 1. 1 represents the situation where there is a match between the number in C on sheet 1 and a number in A on sheet 2


Delete all the visible rows
0