Selecting specific lines from a list depending on criteria

Thanks:  0
Likes:  0

# Thread: Selecting specific lines from a list depending on criteria

1. ## 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. ## Re: Selecting specific lines from a list depending on criteria

Originally Posted by ExcelRoy
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: http://www.mrexcel.com/forum/excel-q...ml#post1511354.

3. ## 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

4. ## 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. ## Re: Selecting specific lines from a list depending on criteria

Originally Posted by ExcelRoy
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

6. ## Re: Selecting specific lines from a list depending on criteria

Simply perfect, many thanks for your time and help!

7. ## Re: Selecting specific lines from a list depending on criteria

Originally Posted by ExcelRoy
Simply perfect, many thanks for your time and help!
You are welcome.

8. ## Re: Selecting specific lines from a list depending on criteria

Originally Posted by ExcelRoy
Simply perfect, many thanks for your time and help!
You are welcime

9. ## 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. ## Re: Selecting specific lines from a list depending on criteria

Originally Posted by ExcelRoy
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?

## User Tag List

#### Posting Permissions

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