How to bring negative values rows from the table

[Zubair]

Hi Experts

I have a tab "Factory" contained table, from which I want to set formula or VBA to bring negative values rows in another tab "örders", please help me in this case.


Factory

Product No.	Product Name	Quantity	Price	Value	User ID
X500095	Chips95	90.00	3.00	270.00	ZF8001
X500100	Chips90	50.00	(5.00)	(250.00)	PB2001
X500105	Chips95	49.00	5.00	245.00	YU3001
X500110	Chips90	89.00	(3.00)	(267.00)	PB2001
X500115	Chips95	90.00	4.00	360.00	ZF8001
X500120	Chips90	44.00	5.00	220.00	ZF8001
X500125	Chips95	25.00	2.00	50.00	PB2001
X500130	Chips90	42.00	(3.00)	(126.00)	YU3001
X500135	Chips95	87.00	4.00	348.00	PB2001

Orders

Product No.	Product Name	Quantity	Price	Value	User ID
X500110	Chips90	89​	-3​	-267​	PB2001
X500100	Chips90	50​	-5​	-250​	PB2001
X500130	Chips90	42​	-3​	-126​	YU3001

 

[Peter_SSs]

I note that your results are ordered by 'Value' but there was no mention of that in your description, so would this (not ordered) suffice?

Zubair.xlsm
	A	B	C	D	E	F
1	Product No.	Product Name	Quantity	Price	Value	User ID
2	X500095	Chips95	90	3	270	ZF8001
3	X500100	Chips90	50	-5	-250	PB2001
4	X500105	Chips95	49	5	245	YU3001
5	X500110	Chips90	89	-3	-267	PB2001
6	X500115	Chips95	90	4	360	ZF8001
7	X500120	Chips90	44	5	220	ZF8001
8	X500125	Chips95	25	2	50	PB2001
9	X500130	Chips90	42	-3	-126	YU3001
10	X500135	Chips95	87	4	348	PB2001
Factory

Zubair.xlsm
	A	B	C	D	E	F
1	Product No.	Product Name	Quantity	Price	Value	User ID
2	X500100	Chips90	50	-5	-250	PB2001
3	X500110	Chips90	89	-3	-267	PB2001
4	X500130	Chips90	42	-3	-126	YU3001
5
Orders

Cell Formulas
Range	Formula
A2:A5	A2	=IFERROR(INDEX(Factory!A:A,AGGREGATE(15,6,ROW(Factory!A$2:A$10)/(Factory!E$2:E$10<0),ROWS(A$2:A2))),"")
B2:F5	B2	=IF($A2="","",INDEX(Factory!B:B,MATCH($A2,Factory!$A:$A,0)))

 

[Peter_SSs]

I note that your results are ordered by 'Value' but there was no mention of that in your description, so would this (not ordered) suffice?

If the results do need to be ordered as in your sample results.

Zubair.xlsm
	A	B	C	D	E	F
1	Product No.	Product Name	Quantity	Price	Value	User ID
2	X500110	Chips90	89	-3	-267	PB2001
3	X500100	Chips90	50	-5	-250	PB2001
4	X500130	Chips90	42	-3	-126	YU3001
5
6
Orders

Cell Formulas
Range	Formula
A2:D6,F2:F6	A2	=IF($E2="","",INDEX(Factory!A:A,AGGREGATE(15,6,ROW(Factory!A$2:A$10)/(Factory!$E$2:$E$10=$E2),COUNTIF($E$2:$E2,$E2))))
E2:E6	E2	=IFERROR(AGGREGATE(15,6,Factory!E$2:E$10/(Factory!E$2:E$10<0),ROWS(E$2:E2)),"")

 

[Zubair]

If the results do need to be ordered as in your sample results.

Zubair.xlsm
	A	B	C	D	E	F
1	Product No.	Product Name	Quantity	Price	Value	User ID
2	X500110	Chips90	89	-3	-267	PB2001
3	X500100	Chips90	50	-5	-250	PB2001
4	X500130	Chips90	42	-3	-126	YU3001
5
6
Orders

Cell Formulas
Range	Formula
A2:D6,F2:F6	A2	=IF($E2="","",INDEX(Factory!A:A,AGGREGATE(15,6,ROW(Factory!A$2:A$10)/(Factory!$E$2:$E$10=$E2),COUNTIF($E$2:$E2,$E2))))
E2:E6	E2	=IFERROR(AGGREGATE(15,6,Factory!E$2:E$10/(Factory!E$2:E$10<0),ROWS(E$2:E2)),"")

Thanks that's what I needed

 

[Peter_SSs]

Cheers. Thanks for the follow-up.

 

[Zubair]

If the results do need to be ordered as in your sample results.

Zubair.xlsm
	A	B	C	D	E	F
1	Product No.	Product Name	Quantity	Price	Value	User ID
2	X500110	Chips90	89	-3	-267	PB2001
3	X500100	Chips90	50	-5	-250	PB2001
4	X500130	Chips90	42	-3	-126	YU3001
5
6
Orders

Cell Formulas
Range	Formula
A2:D6,F2:F6	A2	=IF($E2="","",INDEX(Factory!A:A,AGGREGATE(15,6,ROW(Factory!A$2:A$10)/(Factory!$E$2:$E$10=$E2),COUNTIF($E$2:$E2,$E2))))
E2:E6	E2	=IFERROR(AGGREGATE(15,6,Factory!E$2:E$10/(Factory!E$2:E$10<0),ROWS(E$2:E2)),"")

Can I request an addition in the same formula to bring the rows if price is <= less than equal to -3?

Product No.	Product Name	Quantity	Price	Value	User ID
X500110	Chips90	89​	-3​	-267​	PB2001
X500130	Chips90	42​	-3​	-126​	YU3001

 

[Peter_SSs]

Can I request an addition in the same formula to bring the rows if price is <= less than equal to -3?

From your sample it appears you mean greater than or equal to -3. You have omitted the -5 row and -5 is less than -3
Try this

Zubair.xlsm
	A	B	C	D	E	F
1	Product No.	Product Name	Quantity	Price	Value	User ID
2	X500110	Chips90	89	-3	-267	PB2001
3	X500130	Chips90	42	-3	-126	YU3001
4
5
Orders

Cell Formulas
Range	Formula
A2:D5,F2:F5	A2	=IF($E2="","",INDEX(Factory!A:A,AGGREGATE(15,6,ROW(Factory!A$2:A$10)/((Factory!$E$2:$E$10=$E2)*(Factory!$D$2:$D$10>=-3)),COUNTIF($E$2:$E2,$E2))))
E2:E5	E2	=IFERROR(AGGREGATE(15,6,Factory!E$2:E$10/((Factory!E$2:E$10<0)*(Factory!D$2:D$10>=-3)),ROWS(E$2:E2)),"")

 

[Zubair]

From your sample it appears you mean greater than or equal to -3. You have omitted the -5 row and -5 is less than -3
Try this

Zubair.xlsm
	A	B	C	D	E	F
1	Product No.	Product Name	Quantity	Price	Value	User ID
2	X500110	Chips90	89	-3	-267	PB2001
3	X500130	Chips90	42	-3	-126	YU3001
4
5
Orders

Cell Formulas
Range	Formula
A2:D5,F2:F5	A2	=IF($E2="","",INDEX(Factory!A:A,AGGREGATE(15,6,ROW(Factory!A$2:A$10)/((Factory!$E$2:$E$10=$E2)*(Factory!$D$2:$D$10>=-3)),COUNTIF($E$2:$E2,$E2))))
E2:E5	E2	=IFERROR(AGGREGATE(15,6,Factory!E$2:E$10/((Factory!E$2:E$10<0)*(Factory!D$2:D$10>=-3)),ROWS(E$2:E2)),"")

Many thanks, you are reading the mind and understand what the Excel dumb wanted to say!

 

[Peter_SSs]

No problem. Glad to help.