FAST Complex Pricing Macro Routine Help

[JerBearG]

Hello world. I'd appreciate some help as I'm really not sure how to solve this complex problem in an efficient manner.

I need to find a fast way to price potentially hundreds of items. I know that loops will be the slowest thing in the world because of the sheer number of things that EACH ITEM must be parsed through. You'll know what I mean when I break down how complex this is. Thanks for your help in advance; I'm quite stumped.

Let's begin.

I'm am trying to write a macro that prices items in a quote.
Each item is priced based upon its weight (lbs) and thickness (inches). (certain ranges of thicknesses are more expensive than others)

A sample item is: (made up values)

[TABLE="width: 582"]
<tbody>[TR]
[TD]Mark #[/TD]
[TD]Grade[/TD]
[TD]Pieces
[/TD]
[TD]Thickness
[/TD]
[TD]W
[/TD]
[TD]L
[/TD]
[TD]Weight
[/TD]
[/TR]
[TR]
[TD]43431[/TD]
[TD]44w
[/TD]
[TD]10[/TD]
[TD]0.5
[/TD]
[TD]22[/TD]
[TD]100
[/TD]
[TD]250
[/TD]
[/TR]
</tbody>[/TABLE]

Here is a sample pricing structure for just one grade (44w) (prices made up)
Note: I know this is an inefficient structure for a table (it's more for visual means), but I'm not sure how to go about making it more efficient though.

44w:
[TABLE="width: 513"]
<colgroup><col span="6"><col><col></colgroup><tbody>[TR]
[TD]If Other similar Grade, Add
[/TD]
[TD] ->
[/TD]
[TD] $ 5
[/TD]
[TD][/TD]
[TD="align: right"]0.375[/TD]
[TD="align: right"].625
[/TD]
[TD="align: right"]1.25
[/TD]
[TD="align: right"]2.125
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]THRU[/TD]
[TD]THRU[/TD]
[TD]THRU[/TD]
[TD]THRU
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.1875
[/TD]
[TD="align: right"].25
[/TD]
[TD="align: right"]0.3125
[/TD]
[TD="align: right"].5
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD="align: right"]Critera 1
[/TD]
[TD]$10
[/TD]
[TD]$12
[/TD]
[TD]$16
[/TD]
[TD]$13
[/TD]
[TD]$11
[/TD]
[TD]$8
[/TD]
[TD]$4
[/TD]
[/TR]
[TR]
[TD="align: right"]Criteria 2
[/TD]
[TD]$12
[/TD]
[TD]$14
[/TD]
[TD]$20
[/TD]
[TD]$15
[/TD]
[TD]$13
[/TD]
[TD]$10
[/TD]
[TD]$7
[/TD]
[/TR]
[TR]
[TD]Low bracket
[/TD]
[TD]$14
[/TD]
[TD]$17
[/TD]
[TD]$24
[/TD]
[TD]$17
[/TD]
[TD]$16
[/TD]
[TD]$12
[/TD]
[TD]$10
[/TD]
[/TR]
[TR]
[TD]High Bracket
[/TD]
[TD]$16
[/TD]
[TD]$21
[/TD]
[TD]$28
[/TD]
[TD]$20
[/TD]
[TD]$19
[/TD]
[TD]$15
[/TD]
[TD]$12
[/TD]
[/TR]
</tbody>[/TABLE]

How we price things is, first, you find which grade the item is (There are many more 'grade' tables like this, each with their own prices AND different "THRU" ranges). Then, you find which thickness column it lines up with, and then you match it to the criteria it meets (which price bracket or other criteria), and then where they intersect is the price we use.

I can't make all prices in one column based off one another either (% up and down).

I know this is a lot of work, even if someone could point me in the right direction that would be great. It's just such a monster problem that I feel stumped and I don't know how to make this actually usable (fast).

Thanks for your help.

 

[JerBearG]

Sorry, missed the edit post timeout. In the intro I made an error:
Correction: Items are priced based upon Weight (lbs), thickness (inches) AND their Grade.

 

[Smitty]

Welcome to the Board!

I think the big thing will be to put the data into a single cohesive pricing table. If you can do that then you might want to look into VLOOKUP & INDEX/MATCH.

Having your data in different places will just make it harder to put together.

 

[JerBearG]

Mmmm. Good idea! Thanks for the tips.

Also, thanks for the time you spend helping people on this board. Your expertise is saving countless amateurs like me hundreds of hours and headaches with your simple tips. Thank you!