Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Selecting specific lines from a list depending on criteria

This is a discussion on Selecting specific lines from a list depending on criteria within the Excel Questions forums, part of the Question Forums category; Hi all, I am looking for a formula to only copy "Ordered" status parts from a list So on sheet ...

  1. #1
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,413

    Default Selecting specific lines from a list depending on criteria

    Hi all,

    I am looking for a formula to only copy "Ordered" status parts from a list

    So on sheet 1 i have

    Col A
    Quantity
    Col B
    Part NUmber
    Col C
    Description
    Col D
    Status

    On SHeet 2 i would like all "Ordered" lines to show

    What sort of formula could i use in order to achieve my "Ordered" list on sheet 2

    Many Thanks

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,413

    Default Re: Selecting specific lines from a list depending on criteria

    Quote Originally Posted by ExcelRoy View Post
    Hi all,

    I am looking for a formula to only copy "Ordered" status parts from a list

    So on sheet 1 i have

    Col A
    Quantity
    Col B
    Part NUmber
    Col C
    Description
    Col D
    Status

    On SHeet 2 i would like all "Ordered" lines to show

    What sort of formula could i use in order to achieve my "Ordered" list on sheet 2

    Many Thanks
    I suspect you can adapt the set up Post #3 in: Multiple vlookup's?.
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,217

    Default Re: Selecting specific lines from a list depending on criteria

    Excel 2010
    ABCDE
    1QuantityPart NUmberDescriptionStatus
    21addordered
    32ase
    41swwordered
    53dww
    6
    7
    8
    9

    Sheet1




    Excel 2010
    ABCDEFGH
    1QuantityPart NUmberDescriptionStatusordered
    21addordered
    31swwordered
    4
    5
    6

    Sheet2



    Array Formulas
    CellFormula
    A2{=IFERROR(INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$D$2:$D$5=$H$1,ROW(Sheet1!$D$2:$D$5)-ROW(Sheet1!$D$2)+1),ROWS(Sheet1!$D$2:D2))),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  4. #4
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,413

    Default Re: Selecting specific lines from a list depending on criteria

    Hi Robert Mika,

    That is just fantastic work!

    I have modified to suit my ranges and criteria and works perfect.

    I have one more question though. Can this be set to display not only "Ordered" but "Partially Shipped" aswell

    Many thanks for you time

  5. #5
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,217

    Default Re: Selecting specific lines from a list depending on criteria

    Quote Originally Posted by ExcelRoy View Post
    Hi Robert Mika,

    That is just fantastic work!

    I have modified to suit my ranges and criteria and works perfect.

    I have one more question though. Can this be set to display not only "Ordered" but "Partially Shipped" aswell

    Many thanks for you time
    There few ways to achive that
    For two categories only :
    Excel 2010
    ABCDEFGH
    1QuantityPart NUmberDescriptionStatusordered
    21addorderedPartially Shipped
    31swwordered
    43dwwPartially Shipped
    5
    6
    7
    8
    9

    Sheet2



    Array Formulas
    CellFormula
    A2{=IFERROR(INDEX(Sheet1!A$2:A$5,SMALL(IF((Sheet1!$D$2:$D$5=$H$1)+(Sheet1!$D$2:$D$5=$H$2),ROW(Sheet1!$D$2:$D$5)-ROW(Sheet1!$D$2)+1),ROWS(Sheet1!$D$2:D2))),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  6. #6
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,413

    Default Re: Selecting specific lines from a list depending on criteria

    Simply perfect, many thanks for your time and help!

  7. #7
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,217

    Default Re: Selecting specific lines from a list depending on criteria

    Quote Originally Posted by ExcelRoy View Post
    Simply perfect, many thanks for your time and help!
    You are welcome.
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  8. #8
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,217

    Default Re: Selecting specific lines from a list depending on criteria

    Quote Originally Posted by ExcelRoy View Post
    Simply perfect, many thanks for your time and help!
    You are welcime
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  9. #9
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,413

    Default Re: Selecting specific lines from a list depending on criteria

    Hi Robert,

    I have encountered a slight problem with the "Partially Shipped" Lines

    Sheet 2 has a simple formula that generates the "Ordered" or "Partially Shipped" lines

    The formulas you have created show the originally ordered on the backorders under partially shipped and not the "Outstanding Balance"

    Sheet 2 houses the simple formulas in Column R which generate the "Ordered" and "Partially Shipped" with column Y showing what has been recieved

    The formula is

    =IF(B16="","",IF(B16=Y16,"Shipped",IF(OR(Y16="",Y16=0),"Ordered",IF(Y16
    Would there be a way to show the outstanding balance for "Partially Shipped" lines instead of the ordered quantity

    Many Thanks

  10. #10
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,217

    Default Re: Selecting specific lines from a list depending on criteria

    Quote Originally Posted by ExcelRoy View Post
    Hi Robert,

    I have encountered a slight problem with the "Partially Shipped" Lines

    Sheet 2 has a simple formula that generates the "Ordered" or "Partially Shipped" lines

    The formulas you have created show the originally ordered on the backorders under partially shipped and not the "Outstanding Balance"

    Sheet 2 houses the simple formulas in Column R which generate the "Ordered" and "Partially Shipped" with column Y showing what has been recieved

    The formula is

    =IF(B16="","",IF(B16=Y16,"Shipped",IF(OR(Y16="",Y16=0),"Ordered",IF(Y16
    Would there be a way to show the outstanding balance for "Partially Shipped" lines instead of the ordered quantity

    Many Thanks

    Could you post your data and rest of the IF formula?
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

Page 1 of 2 12 LastLast

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
  •  


DMCA.com