Better way than Filter to search a large data set

[showboat]

Hey everyone.

Please find my attached workbook.

On the "Pipe Takeoff" tab, I enter in the Pipe Size, Ins Thick, Ins Type, and Manufacturer.

Then I have a Filter(Filter()) function to search for those 4 variables on the "Pipe List Pricing" tab and return the cost listed.

Everything works fine until I get to about 1,000 or so rows on the "Pipe Takeoff" tab. Then it gets very slow and laggy due to all of the searching happening on such a large table. The "Pipe List Pricing" tab has thousands of rows with data to look through.

Any ideas how to change or modify the way I'm searching so it doesn't get bogged down on large data sets?

[sintek]

So I'm confused...

On the "Pipe Takeoff" tab, I enter in the Pipe Size, Ins Thick, Ins Type, and Manufacturer.

Enter where?

Filter(Filter()) function to search for those 4 variables on the "Pipe List Pricing" tab and return the cost listed.

Where?

Explain in which cells you enter these 4 values and where you want the cost|pricing to placed...
Are those entries in row 17 & 18 2 examples and the costs for each placed in Col U of each row...
Do you enter 1 or more than 1 entry at a time...
Seems this file setup is a bit strange...

[showboat]

I enter in the values on the Pipe Takeoff tab - Columns E, F, O, Q.

Example: Pipe Takeoff Tab, Row 17. I manually enter in E17, F17, O17, Q17. The filter function is in cell U17 which I did not paste that filter formula in the cell since I was looking for an alternative.

On the Pipe List Pricing tab, it searchs for the Ins Type first (column B). Then it searches for the Manufacturer (column C). Then it searches through the Pipe Size (column E). Then it searches through the Ins Thickness (row 2 - Columns F through Q).

So row 17 goes - search for "Poly-ASJ", search for "DUNA", search for 2, search for 1. The returned value of 4.22 (cell U17) on the Pipe Takeoff tab is found on the Pipe List Pricing tab (cell G8).

[sintek]

okay...so this is for row 17 entry only as you did not specify if there is more than 1 entry...

Please Login or Register  to view this content.

If more...You will need a loop...

Please Login or Register  to view this content.

[showboat]

Thanks! Yeah there are thousands of rows doing the exact same searching as row 17. I showed 2 entries in the workbook and used row 17 as an example.

Will the returned value repopulate automatically if I were to change the any of the values in row 17? For instance your loop would populate the cost upon initial data entry but if I needed to change the manufacturer or pipe size, would it automatically repopulate again?

Example: Row 17 - if I change the thickness from a 1 to a 2, the return value of 4.22 would need to change to 8.59

[sintek]

there are thousands of rows doing the exact same searching as row 17

Evaluate may not be best option then...Perhaps storing all in dictionary might be better

if I needed to change the manufacturer or pipe size, would it automatically repopulate again?

Will need a Sheet_Change event for this but it depends on which actual cell entry triggers the code to fire...

Is this the actual setup of your Pipe Takeoff sheet...All those blank columns & rows
Why don't you explain this in a practical sense...perhaps someone could offer a different method

[showboat]

My actual spreadsheet is alot bigger and more complex. Changing any of the 4 variables would need to trigger the code to fire.

I will try to explain it in a practical sense:

I deal with pipe insulation. There are different types and there are different manufacturers for each type. Then there are suppliers that sell the insulation. So think of it as Pickup is the type. GM is the manufacturer. Dealership is the seller.

Then there are differnet sizes and thicknesses to take into consideration.

Each of my rows is essentially a pipe run. Think of it as a line of pipe going from your water heater to your sink.

The bigger the job, the more pipe runs you have and the more rows you have.

Each row (pipe run) has all kinds of variables but only 4 of these variables affect the cost of the insulation (Pipe Size, Ins Thickness, Ins Type, Manufacturer).

I currently store all of my pricing in the Pipe List Pricing tab (which has thousands of rows).

I manually enter in my pipe runs into each row.

I need an efficient way to grab the cost from the Pipe List Pricing data for each row (pipe run) in the Pipe Takeoff tab that doesn't bog down the workbook on large jobs with thousands of rows (pipe runs).

[sintek]

I need an efficient way to grab the cost from the Pipe List Pricing data for each row (pipe run) in the Pipe Takeoff tab that doesn't bog down the workbook on large jobs with thousands of rows (pipe runs).

Okay...Lets ignore the "Trigger on change" for now...Will come back to it...
I want to focus on speed and efficiency...As I said the Evaluate method for thousands of rows will be slower than the dict save method...
So...storing all the data into a Dictionary first, perhaps even at the opening of the file,will be much more effecient...
So for now...Take a sample file with as many possible entries you see fit and make use of this code to populate the costs in Col U...



This is for your sample file setup...

Please Login or Register  to view this content.

Will change the code to a sheet event after...

My actual spreadsheet is alot bigger and more complex

It is always best to provide a sample file that represents actual file setup...99% of the time users are not able to edit the code and time is wasted...

[Akuini]

I agree with Sintek's suggestion to use the worksheet change event, but it should be triggered only when all four columns (E, F, O, Q) on the row you're working on are filled with data.
If you're interested in this approach I can try to write the code.
Question:
1. Are data in the columns INS CODE and MANUFACTURER all in capital letters?
2. Are data sorted by INS CODE and then by MANUFACTURER?

[showboat]

I'll give it a try and report back!