If statement is false skip to next row

[jmmaldonadobruin]

Hi everyone,

Here's my problem. I have a permanent list of part numbers that are hazardous or too large to be shipped (column A on Parts spreadsheet). In column B of the same sheet, I have a column where I will be copying and pasting lists of part numbers along with their make code and on hand quantity. In column E, I am looking to see if there are matches between the two lists. If there is no match, it returns TRUE (which means they are not hazardous or too large to be shipped).

In the 'Upload List' sheet, I want to take all the part numbers that have a TRUE value (OK to be shipped because they are not too large and are not hazardous). I have a formula that works, but it takes forever to calculate as I have 4,000+ rows this time around and there will be other times where I have upwards of 20,000 part numbers. Also, it is probably not the most efficient way to go about what I am trying to do. I read somewhere that VBA is the way to go, but I'm not really sure how to get started.

Please help!

P.S. I only included the first 10 rows (not including the header) for the 'Upload List' sheet of the workbook because it was taking too long for the formulas to calculate and save.

Upload Parts (version 1).xlsx

[Richard Buttrey]

Hi, and welcome to the forum.

VBA code will never be quicker than using standard Excel functions or functionality.

The correct approach to your requirement is to use the Data Filter Functionality.

First question is, is it vital to extract those records that have evaluated as True to another sheet. Why not just filter the Parts sheet so that you only see the True Values.

Then, and only then, if it's vital to have them on another sheet just copy and paste them. You could do all the above with a simple macro which would take seconds.

[jmmaldonadobruin]

No, it is not vital to extract those records that have evaluated as True to another sheet. They can all stay on the first spreadsheet. Now that I'm thinking about it, I realize how easy it would be just to filter the values that are True. Not sure why I am purposely making it more complicated for myself.

Would you mind helping me with the simple macro for all of this?

[Jonmo1]

The big problem is the references to ENTIRE columns (or almost entire 2:1048576)

If 20000 records is a realistic limit, then use that, maybe add 10%, 22,000
Change your named ranges to cover rows 2:22000

Also, you only need the array formula in column A to get the part number.
Since the values are unique, you can then do a simple vlookup or Index/Match type of function to get the other 2 columns of data.

I also removed the INDIRECT reference, and directly named the PartNumber named range in the formula, instead of INDIRECT(A1)

Here's an example workup.
EFjmmaldon.xlsx

[jmmaldonadobruin]

Jonmo1,

Wow! Thank you. This is amazing. Thank you so much.

[Jonmo1]

You're welcome..

But in hindsite, using plain old Autofilter is probably a much better solution.