Help with lookups and arrays

[jg]

I have two worksheets, "Pricesheets" and "Orders".

Columns in Pricesheets (2500 rows):
- Customer (text)
- Plant (text)
- SKU (text)
- Standard lengths (text, like this: 48' 50' 60')

Colums in Orders (10,000 rows):
- Customer (Text)
- Plant (text)
- SKU (text)
- length ordered (integer)

For each order (a row in Orders), I want to find the matching row in
Pricesheets (based on Customer, Plant and SKU), then return a 1 if the
length ordered was listed in "standard lengths".

I've tried combinations of array-entered SUM(IF()) and FIND(text, within)
functions, but I can't get it to work. On top of which, it takes a heckuva
long time to process.

Is there a more elegant solution?

Thanks.

[Harlan Grove]

jg wrote...
>I have two worksheets, "Pricesheets" and "Orders".
>
>Columns in Pricesheets (2500 rows):
>- Customer (text)
>- Plant (text)
>- SKU (text)
>- Standard lengths (text, like this: 48' 50' 60')
>
>Colums in Orders (10,000 rows):
>- Customer (Text)
>- Plant (text)
>- SKU (text)
>- length ordered (integer)
>
>For each order (a row in Orders), I want to find the matching row in
>Pricesheets (based on Customer, Plant and SKU), then return a 1 if the
>length ordered was listed in "standard lengths".

And return a 0 if the length ordered isn't a standard length?

>I've tried combinations of array-entered SUM(IF()) and FIND(text, within)
>functions, but I can't get it to work. On top of which, it takes a heckuva
>long time to process.
>
>Is there a more elegant solution?

Other than using a database instead of a spreadsheet? Realize that what
you're trying to do is IDEALLY suited to databases. While it can be
done using spreadsheets, it can't be done particularly efficiently.
Kinda like using a wheelbarrow to haul a few tons of coal several
hundred miles - that it can be done doesn't mean it should be.

Anyway, the best you're likely to manage in spreadsheets is to create
concatenated key fields in each table. If your pricesheet tables
originally spanned A1:D2501 with field names in row 1, enter Key in E1
and the following formula in E2.

E2:
=A2&CHAR(8)&B2&CHAR(8)&C2&CHAR(8)&D2

Then fill E2 down into E3:E2501. Then sort A2:E2501 on column E in
ascending order. If your Orders table were in a different worksheet
spanning A1:D10001 with field names in the top row, you could check for
standard size orders using formulas like

E2:
=count(1/(VLOOKUP(A2&CHAR(8)&B2&CHAR(8)&C2&CHAR(8)&D2,
Pricesheet!$E$2:$E$2501,1)=A2&CHAR(8)&B2&CHAR(8)&C2&CHAR(8)&D2))

which will return 1 if the Order record has an exact match in the
Pricesheet table or 0 if not. Note that this uses VLOOKUP for
*approximate* matching, which is why col E in the Pricelist table needs
to be sorted in ascending order. Approximate matching makes use of
binary search, which is MUCH FASTER than linear searching which VLOOKUP
uses for exact matching. Since the VLOOKUP result may not match the
lookup key exactly, that's why its result is compared to the lookup key.

[jg]

Harlan

Yes, I know this would be better as a database. For now, I'm just doing
some analysis on a customer's data, and have neither the budget nor the tech
chops to get the data into Access. (I did, in fact, try to import it, but
couldn't work out how to preserve the relationships so as to get the
benefits of relational db).

Thanks for the other suggestions - especially the one about the performance
difference between approximate and linear search. That will turn out to be
a great time save.




"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> jg wrote...
>>I have two worksheets, "Pricesheets" and "Orders".
>>
>>Columns in Pricesheets (2500 rows):
>>- Customer (text)
>>- Plant (text)
>>- SKU (text)
>>- Standard lengths (text, like this: 48' 50' 60')
>>
>>Colums in Orders (10,000 rows):
>>- Customer (Text)
>>- Plant (text)
>>- SKU (text)
>>- length ordered (integer)
>>
>>For each order (a row in Orders), I want to find the matching row in
>>Pricesheets (based on Customer, Plant and SKU), then return a 1 if the
>>length ordered was listed in "standard lengths".
>
> And return a 0 if the length ordered isn't a standard length?
>
>>I've tried combinations of array-entered SUM(IF()) and FIND(text, within)
>>functions, but I can't get it to work. On top of which, it takes a
>>heckuva
>>long time to process.
>>
>>Is there a more elegant solution?
>
> Other than using a database instead of a spreadsheet? Realize that what
> you're trying to do is IDEALLY suited to databases. While it can be
> done using spreadsheets, it can't be done particularly efficiently.
> Kinda like using a wheelbarrow to haul a few tons of coal several
> hundred miles - that it can be done doesn't mean it should be.
>
> Anyway, the best you're likely to manage in spreadsheets is to create
> concatenated key fields in each table. If your pricesheet tables
> originally spanned A1:D2501 with field names in row 1, enter Key in E1
> and the following formula in E2.
>
> E2:
> =A2&CHAR(8)&B2&CHAR(8)&C2&CHAR(8)&D2
>
> Then fill E2 down into E3:E2501. Then sort A2:E2501 on column E in
> ascending order. If your Orders table were in a different worksheet
> spanning A1:D10001 with field names in the top row, you could check for
> standard size orders using formulas like
>
> E2:
> =count(1/(VLOOKUP(A2&CHAR(8)&B2&CHAR(8)&C2&CHAR(8)&D2,
> Pricesheet!$E$2:$E$2501,1)=A2&CHAR(8)&B2&CHAR(8)&C2&CHAR(8)&D2))
>
> which will return 1 if the Order record has an exact match in the
> Pricesheet table or 0 if not. Note that this uses VLOOKUP for
> *approximate* matching, which is why col E in the Pricelist table needs
> to be sorted in ascending order. Approximate matching makes use of
> binary search, which is MUCH FASTER than linear searching which VLOOKUP
> uses for exact matching. Since the VLOOKUP result may not match the
> lookup key exactly, that's why its result is compared to the lookup key.
>