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]

Maybe using a macro with this logic

Loop through each FInished Goods row

If(AND(LowerDiameter < Diameter < UpperDiamer,LowerLength < Length < UpperLength),RItemnumber,"No Matches")

Or using an array formula with the same if structure (but with the Lower and Upper parts as a range).

e.g. If(AND(LowerDiameterRange < Diameter < UpperDiamerRange,LowerLengthRange < Length < UpperLengthRange),RItemnumber,"No Matches")

So if your Finished goods are in columns A to E and your Raw Items are in Columns F to L

it'ls look like (in E2)

=IF(AND(I2:I10 < C2 < J2:J10, K2:K10 < D2 < L2:L10) , G2 , "No Matches")

Then use Ctrl-Shift-Enter to enter Forumla (Should have { at beginning of formula and } at the end of it.

Not sure if that'll work, have to try it and see. I always have to test an array formula to see if it'll work.

Although that could become cumbersome if you had a lot of Finished Items to check.

Sorry that I haven't given any code. No Excel at home and I'm not good at writing code in my head.


[ This Message was edited by: Cosmos75 on 2002-04-14 22:15 ]

[Aladin Akyurek]

On 2002-04-14 21:39, amitshah wrote:
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 ]

I'll assume that A1:F4 in a sheet called FGoods houses the following sample data:

{"FItemNumber","FItemDescription","Diameter","Length","rawGoodsItemNo","Hidden";
"f1","ItemA",5,10,"r1",2;
"f2","ItemB",6.5,27,"r1",2;
"f3","ItemC",8,22,"Not Found",0}

Note 1. Columns E and F show results, computed by formulas which will be reported below.

I'll also assume that A1:F3 in a sheet called RGoods houses the following sample data:

{"RItemNumber","RItemDescription","LowerDiameter","UpperDiameter","LowerLength","UpperLength";
"r1","Itemr1",2,7,5,15;
"r2","Itemr2",15,22,35,40}

The Problem Statement: Retrieve for FItemNumber N an RItemNumber M whose LowerDiameter is less than or equal to the Diameter of N & whose UpperDiameter greater than or equal to the Diameter of N.

Activate the option Insert|Name|Define.
Enter Rrecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=MATCH(9.99999999999999E+307,RGoods!$C:$C)

Activate Add [Don't quite the Define Name Window yet].

Enter RawItemNums as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(RGoods!$A$2,0,0,Rrecs-1,1)

Activate Add [Don't quite the Define Name Window yet].

Enter LowerD as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(RGoods!$C$2,0,0,Rrecs-1,1)

Activate Add [Don't quite the Define Name Window yet].

Enter UpperD as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(RGoods!$D$2,0,0,Rrecs-1,1)

Activate OK.

Activate E2 in FGoods and enter:

=IF(F2,INDEX(RawItemNums,F2-1),"Not Found")

In F2 in FGoods enter:

=SUMPRODUCT((C2>=LowerD)*(C2<=UpperD)*(ROW(LowerD)))

Select E2:F2 and drag down as far as needed.

Hide column F.

Note 2. Column F is created for efficiency reasons.

I've already shown the results in E and F along with the sample data in FGoods at the beginning.

[Cosmos75]

Aladin,

Finally got a chance to try out the array formula.

It doesn't work. SORRY, AmitShah.

My question is, why not?

[Aladin Akyurek]

On 2002-04-15 09:13, Cosmos75 wrote:
Aladin,

Finally got a chance to try out the array formula.

It doesn't work. SORRY, AmitShah.

My question is, why not?

Which array-formula?

[Cosmos75]

Aladin,

This array formula. Was hoping you could tell me what's wrong with it.

IF(AND(LowerDiameterRange < Diameter < UpperDiamerRange,LowerLengthRange < Length < UpperLengthRange),RItemnumber,"No Matches")

At least this way I'll know what will and what won't work in an array formula.

[ This Message was edited by: Cosmos75 on 2002-04-15 09:51 ]

[Cosmos75]

Anybody know what's wrong with the array formula?

[Aladin Akyurek]

On 2002-04-15 09:46, Cosmos75 wrote:
Aladin,

This array formula. Was hoping you could tell me what's wrong with it.

IF(AND(LowerDiameterRange < Diameter < UpperDiamerRange,LowerLengthRange < Length < UpperLengthRange),RItemnumber,"No Matches")

At least this way I'll know what will and what won't work in an array formula.

[ This Message was edited by: Cosmos75 on 2002-04-15 09:51 ]

AND is a function that returns a scalar value, that is, a single result, while an array formula expects a constant array of logical values like {TRUE,FALSE,FALSE}.

Another observation about your formula:

the AND bit is not set up right. This issue has nothing to do with the one above -- that is, even if AND set up as required, the above formula wouldn't work for the reason I mentioned.

AND(LowerDiameterRange < Diameter < UpperDiamerRange,LowerLengthRange < Length < UpperLengthRange)

must be:

AND(Diameter > LowerDiameterRange,Diameter < UpperDiamerRange,Length > LowerLengthRange, Length < UpperLengthRange)

where the names must refer to single cell ranges.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-04-15 12:18 ]

[Cosmos75]

Aladin,

THANK YOU!! Am ashamed to have posted that up without knowing if it would work. Oh, the shame of it all!!

[amitshah]

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?