Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Range

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    Finally got a chance to try out the array formula.

    It doesn't work. SORRY, AmitShah.

    My question is, why not?

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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?

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anybody know what's wrong with the array formula?

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

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

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •