Selecting specific lines from a list depending on criteria

[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

 

[Aladin Akyurek]

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-questions/307832-multiple-vlookups.html#post1511354.

 

[Robert Mika]

Excel 2010
	A	B	C	D	E
1	Quantity	Part NUmber	Description	Status
2	1	a	dd	ordered
3	2	a	se
4	1	s	ww	ordered
5	3	d	ww
6
7
8
9
Sheet1

Excel 2010
	A	B	C	D	E	F	G	H
1	Quantity	Part NUmber	Description	Status				ordered
2	1	a	dd	ordered
3	1	s	ww	ordered
4
5
6
Sheet2

Cell Formulas
Range	Formula
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))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[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

 

[Robert Mika]

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
	A	B	C	D	E	F	G	H
1	Quantity	Part NUmber	Description	Status				ordered
2	1	a	dd	ordered				Partially Shipped
3	1	s	ww	ordered
4	3	d	ww	Partially Shipped
5
6
7
8
9
Sheet2

Cell Formulas
Range	Formula
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))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ExcelRoy]

Simply perfect, many thanks for your time and help!

 

[Robert Mika]

Simply perfect, many thanks for your time and help!

You are welcome.

 

[Robert Mika]

Simply perfect, many thanks for your time and help!

You are welcime

 

[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<B16,"Partially Shipped"))))

Would there be a way to show the outstanding balance for "Partially Shipped" lines instead of the ordered quantity

Many Thanks

 

[Robert Mika]

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<b16,"partially shipped"))))

Would there be a way to show the outstanding balance for "Partially Shipped" lines instead of the ordered quantity

Many Thanks

</b16,"partially>Could you post your data and rest of the IF formula?