Range

[amitshah]

I have a list (Finished Goods)
with five columns
(C1 - FItemNumber,
C2 - FItemDescription,
C3 - Diameter,
C4 - Length,
C5 - rawGoodsItemNo).

I have another list (Raw Goods)
with 6 columns
(C1 - RItemNumber,
C2 - RItemDescription,
C3 - LowerDiameter,
C4 - UpperDiameter,
C5 - LowerLength,
C6 - UpperLength)

For finished goods list I need to find an item from raw goods list. To do this, the finished goods item's diameter is checked
with the LowerDiameter and UpperDiameter AND the length checked with the raw goods LowerLength and UpperLength, and if WITHIN
the range of BOTH then only is that raw goods item is selected and inserted into the fifth column (rawGoodsItemNo) of
Finished Goods list.

Finished Goods
--------------
C1 C2 C3 C4 C5
A ItemA 5 10
B ItemB 6.5 27
etc etc

Raw Goods
---------
C1 C2 C3 C4 C5 C6
r1 Itemr1 2 7 5 15
r2 Itemr2 15 22 35 40
etc etc

So in the above case the finished good item A will have raw goods item r1 in its 5th Column.
I would imagine this will need
a formula in column 5 of Finished Goods to check through the raw goods list. Which formula?

How is this possible? I have been trying to use VLookup but know its inappropriate for the task.

If this involves using access I am fine with that as well. Any ideas welcome. Thanks.

_________________
Amit Shah
This message was edited by amitshah on 2002-04-14 21:45
This message was edited by amitshah on 2002-04-14 21:52
This message was edited by amitshah on 2002-04-14 21:53

 

[Cosmos75]

Aladin,

How about this as the array formula

IF(Diameter > LowerDiameterRange, IF(Diameter < UpperDiamerRange,IF(Length > LowerLengthRange, IF(Length < UpperLengthRange,RIDNumberRage,"No Match"))))

 

[Aladin Akyurek]

On 2002-04-15 15:21, amitshah wrote:
Hi guys thanks for the help .......

I have one query though ...... Aladdin the formula works but it also gives false value .... what I mean is that in the second case of the finished goods, the diameter is 6.5 (which is 2<6.5<7, this is fine) but its length 27 is not in the range of fgoods item r1's lengths (5 and 15).

Is there something that can be done about that?

Sure. Define LowerL and UpperL following the same procedure that I described for LowerD and UpperD. I leave that to you.

Then extend the formula in F2 as follows:

=SUMPRODUCT((C2>=LowerD)*(C2<=UpperD)*(D2>=LowerL)*(D2<=UpperL)*(ROW(LowerD)))

That's all.

Aladin

 

[Cosmos75]

Anyone know if my last array formula would work and if not, why not?

 

[amitshah]

Thanks for the help guys ..... sorry for the delay in reply was caught up in another project

Aladin your fomula works great ...... does exactly what I need.

Cosmos I tried your array formula it doesnt work. It doesnt get the reference to the item number of rgoods.

once again thanks for the help

 

[amitshah]

Hi this query is a continuation of the initial query I had posted. I hope someone can help me with it in particular aladin answered my question so I am hoping you do remember this question and can answer.

The formula you gave works great although I have two questions:

1) it slows the computer when it tries to compute values for a long list can I improve the performance?
2) when I add a new range (which is a new row in rgoods sheet) it somehow does not include it in the list of the named range. Is there a way to update a named range so that it accpets the new row in the array?

Please anyone who can answer this it would be greatly appreciated. Thank you in advance.