Delete rows based in three criteria in three separate columns

[Rabbitoh]

Example: I have a list of 100 names on sheet1 along with other data in other columns (A - date, B - number, C - address. D - first name, E - middle name, F- last name). and a smaller list of 10 names (A - first name, B - middle name, C - last name) on sheet2. All the names on sheet2 are considered "exceptions" and as such I use the following code to remove every row where the instance of each of them appear on sheet1.

However, the code only works based on one criteria in one column i.e. the name in column D. What I need it to do is to work based on three criteria over three columns e.g. instead of just deleting every row in Column D of sheet1 where "John" appears, I need it to identify and delete every row where "John" appears in Column D, AND "Peter" in Column E, AND "Smith" in Column F. So three columns and three criteria to be met before the row is deleted. If "all three names" "in the same order" as they appear on sheet2 two are not present then the row is not deleted.

The code below searches the full row to find "John" rather than specifying a specific row. I hope a codemaster can help.

Please Login or Register  to view this content.

[sintek]

And DrumRoll....

A sample file with exact setup as Main File is...

[oeldere]

you did not add an excel file, so I can't test it for you.

Column D1 = A1&B1&C1

After that make compare D1 with the list in sheet 2.

[Rabbitoh]

Attachment attached, I think

[alansidman]

I would use Power Query to do this.
Load your data to PQ (if you don't have this already, you can download the add in from MS. It is native to Excel versions 2016 and later and found on the Data Tab.)
Merge the three columns and place a colon as the delimiter.
Filter out the necessary item
Split the field on the delimiter
Close and Load to Excel

Here is the Mcode

Please Login or Register  to view this content.

[sintek]

I have a list of 100 names on sheet1 along with other data in other columns (A - date, B - number, C - address. D - first name, E - middle name, F- last name). and a smaller list of 10 names (A - first name, B - middle name, C - last name) on sheet2

This tells me that your sample file is not a accurate representation of your actual file????
We can supply code and 99% of the time, a member will not be able to amend it for actual file...Meaning...valuable time is wasted on unnecessary code writing...

So do a favour and upload a sample file with accurate representation...

[Rabbitoh]

Correct attachment attached (my bad, I had a simplified copy for playing around with)

[Rabbitoh]

Done. Corrected as requested

[sintek]

Thank you...This should always be the case for future threads...

Please Login or Register  to view this content.

[Rabbitoh]

I agree and will do so. This code works perfectly. Thanks for doing what you do.

[sintek]

.........................
THANKS.gif

[Rabbitoh]

My version of Excel does not support this add-in. If I upgrade, will the code generated by Power Query allow the worksheet to function correctly in any version of Excel, or will it always require 2016 or greater?
This post of course relates back to the post from alansidman in respect to the POWER QUERY add-in, which sounds like something I should have going forward anyway.

[alansidman]

An alternative is to again use Power Query and join the two tables with a Right Anti Join.

Here is the Mcode.

Please Login or Register  to view this content.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
1	Table1.Date	Table1.Number	Table1.Address	Table1.First Name	Table1.Middle Name	Table1.Last Name
2	1/1/2020	15	123 Street	John	Arthur	Smith
3	1/20/2020	4	123 Street	Michael	James	Jones
4	2/15/2020	9	123 Street	Enda	Mary	Smith

Sheet: Sheet4

1. In Version 2010 and 2013, you need to download the add in from MS. Power Query will work in any machine either having the add in or 2016 and later versions. Click on the link in my signature block to learn more about PQ.

2. If this is something you wish to explore further, the book "M is for (Data) Monkey" by Ken Puls and Miguel Escobar is a great primer. This is a very powerful piece of Excel and will allow you to easily manipulate data without learning code.