Need help creating a function or macro

  • Thread starter Thread starter Tami
  • Start date Start date
T

Tami

Hello,
I have 2 spreadsheets. I receive 1 spreadsheet daily of
values that have to be updated to our company database.
My 2nd spreadsheet is a list of things that need to be
deleted from spreadsheet 1 before being imported into our
company database. Spreadsheet 1 will change on a daily
basis but spreadsheet 2 is consistent.
I'm trying to find a way to delete from spreadsheet 1 the
values in spreadsheet 2 without having to do a filter or a
find the values and then deleting them.
Is there a formula or preferably, a macro that I can build
to do this?
There are probably about 100 values in spreadsheet 2 that
are not unique and its very time consuming to use either
the filter or find tools.

Thanks,
Tami
 
...
...
I'm trying to find a way to delete from spreadsheet 1 the
values in spreadsheet 2 without having to do a filter or a
find the values and then deleting them.
Is there a formula or preferably, a macro that I can build
to do this?
Click to expand...
...

You're willing to use a macro but not a filter? Maybe by 'filter' you don't mean
Data > Filter.

If there's one field in SS2 that determines which records to delete in SS1, you
could add an extra column of formulas to the table in SS1. If the common field
between SS1 and SS2 were in column C in SS1 and row 2 contained the first record
in SS1, and ExcludeList refers to the range of values to exclude in SS2, then
the formula for the topmost record in SS1 would be

=COUNTIF(ExcludeList,C2)>0

and fill down as needed so there's one of these formulas for each record in SS1.
Then select the entire table including this additional column, and run Data >
Filter > AutoFilter, then in the column containing these formulas, select TRUE
in the top row to filter out the records in which this field evaluates to TRUE.
These are the records to be deleted. Press [Shift]+[Spacebar] to select entire
rows, and run Edit > Delete. Remove the AutoFilter, and delete the added column
of formulas. Export what's left to your database.

If you turn on Excel's macro recorder while your following these steps, then
reply here with the macro that Excel recorded, others could help you generalize
it for variable numbers of records in SS1.
 
Back
Top