How to bring negative values rows from the table

Zubair

Active Member
Joined
Jul 4, 2009
Messages
304
Office Version
  1. 2016
Platform
  1. Windows
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 NameQuantityPriceValueUser ID
X500095Chips9590.003.00270.00ZF8001
X500100Chips9050.00(5.00)(250.00)PB2001
X500105Chips9549.005.00245.00YU3001
X500110Chips9089.00(3.00)(267.00)PB2001
X500115Chips9590.004.00360.00ZF8001
X500120Chips9044.005.00220.00ZF8001
X500125Chips9525.002.0050.00PB2001
X500130Chips9042.00(3.00)(126.00)YU3001
X500135Chips9587.004.00348.00PB2001

Orders

Product No.Product NameQuantityPriceValueUser ID
X500110Chips90
89​
-3​
-267​
PB2001
X500100Chips90
50​
-5​
-250​
PB2001
X500130Chips90
42​
-3​
-126​
YU3001
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
ABCDEF
1Product No.Product NameQuantityPriceValueUser ID
2X500095Chips95903270ZF8001
3X500100Chips9050-5-250PB2001
4X500105Chips95495245YU3001
5X500110Chips9089-3-267PB2001
6X500115Chips95904360ZF8001
7X500120Chips90445220ZF8001
8X500125Chips9525250PB2001
9X500130Chips9042-3-126YU3001
10X500135Chips95874348PB2001
Factory


Zubair.xlsm
ABCDEF
1Product No.Product NameQuantityPriceValueUser ID
2X500100Chips9050-5-250PB2001
3X500110Chips9089-3-267PB2001
4X500130Chips9042-3-126YU3001
5      
Orders
Cell Formulas
RangeFormula
A2:A5A2=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:F5B2=IF($A2="","",INDEX(Factory!B:B,MATCH($A2,Factory!$A:$A,0)))
 
Upvote 0
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
ABCDEF
1Product No.Product NameQuantityPriceValueUser ID
2X500110Chips9089-3-267PB2001
3X500100Chips9050-5-250PB2001
4X500130Chips9042-3-126YU3001
5      
6      
Orders
Cell Formulas
RangeFormula
A2:D6,F2:F6A2=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:E6E2=IFERROR(AGGREGATE(15,6,Factory!E$2:E$10/(Factory!E$2:E$10<0),ROWS(E$2:E2)),"")
 
Upvote 1
If the results do need to be ordered as in your sample results.

Zubair.xlsm
ABCDEF
1Product No.Product NameQuantityPriceValueUser ID
2X500110Chips9089-3-267PB2001
3X500100Chips9050-5-250PB2001
4X500130Chips9042-3-126YU3001
5      
6      
Orders
Cell Formulas
RangeFormula
A2:D6,F2:F6A2=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:E6E2=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
 
Upvote 0
If the results do need to be ordered as in your sample results.

Zubair.xlsm
ABCDEF
1Product No.Product NameQuantityPriceValueUser ID
2X500110Chips9089-3-267PB2001
3X500100Chips9050-5-250PB2001
4X500130Chips9042-3-126YU3001
5      
6      
Orders
Cell Formulas
RangeFormula
A2:D6,F2:F6A2=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:E6E2=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 NameQuantityPriceValueUser ID
X500110Chips90
89​
-3​
-267​
PB2001
X500130Chips90
42​
-3​
-126​
YU3001
 
Last edited:
Upvote 0
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
ABCDEF
1Product No.Product NameQuantityPriceValueUser ID
2X500110Chips9089-3-267PB2001
3X500130Chips9042-3-126YU3001
4      
5      
Orders
Cell Formulas
RangeFormula
A2:D5,F2:F5A2=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:E5E2=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)),"")
 
Upvote 0
Solution
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
ABCDEF
1Product No.Product NameQuantityPriceValueUser ID
2X500110Chips9089-3-267PB2001
3X500130Chips9042-3-126YU3001
4      
5      
Orders
Cell Formulas
RangeFormula
A2:D5,F2:F5A2=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:E5E2=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!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top