Auto-filter or mark cells based on criteria from two columns in different sheets?

[SerafimF1]

I’m not sure exactly how to describe this, but the problem is as follows.

My client has a large file containing company IDs, product codes, and state codes. Each row tells you that the company sells that product in that state.

I have two other sheets that tell us which information needs to be deleted, but it isn’t formatted in the same way. One sheet only has state information (i.e. delete state TX for Company A, which means to delete every single product code for that state) and one has only product information (i.e. delete product AUTO from Company A, which means that product code should be deleted for every state that Company A sells in).

I don’t need to actually delete the rows from the sheet, just mark them, i.e. with an x in a new column. If I can filter the sheet based on the criteria from the other sheets that will work, but if it can be done via VLOOKUP or another function that works too.

Here are a few screenshots showing example scenarios

The database file itself

Products that need to be deleted for company 2143

In this case, I would need to mark each row that has 2143 in column A and HOME in column B, regardless of what is in column C, then each row that has 2143 in A, CNDO in B, and so forth.

The database info for Company 41344

States that need deletion for Company 41344

Here, I would need to mark each row with 41344 in column A and IA in column C, regardless of what is in column B.

There in a further complication that seems easier to deal with. Many states have already been deleted. If a company ID and state appear in the delete-state sheet but aren’t in the actual database, they can be ignored.

The actual database file is over 30,000 rows, so obviously this has to be automated. I am going to continue working on it but my skills are limited so I figured I would post asking for help since I may be missing a simple solution that is beyond my abilities.

updatesheetsample.ods

[schiavinatto]

@SerafimF1,

Is the markup to be inserted into the database file?

It is possible to post the file, it is easier to recreate it to test the formula.

Edit your question and with clip icon attach your file template.

[SerafimF1]

The markup should be inserted into the database file, yes. Ideally, it will be another column that tells you what to delete. I’ve just uploaded a sample version of the database and update data.

[schiavinatto]

@SerafimF1, here is a suggestion

Columns D and E are work columns, use the formula in column F.

---

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball to the left of the answer, to finish the question.

[SerafimF1]

This is absolutely amazing, thank you so much! If it is possible, can you provide me with a brief explanation of how these formulas work? I am investigating them myself but would like to have a better understanding of them before using them in the full scale data. If you do not have time I understand, and I think I can figure it out on my own.

Thank you again so much. This is very appreciated.

[schiavinatto]

Analyze the formulas individually, column F is the sum D + E and if> O (zero) = Delete

[SerafimF1]

Just came to understand it right before reading your comment. This is a simple and perfect solution. Thank you so much, you have saved me hours of work!