Another Scheduling formula issue part 2

M

mdean32

New Member
Joined
Jan 15, 2019
Messages
22
My current formula for "RODS AVAILABLE" is =IFERROR(G2-SUMIF(E$2:E2,E2,D$2:D2),"").

I have the table sorted exactly how I want to print it with the "Line" sorted first, followed by the "Run Date" in order. However, my formula for "RODS AVAILABLE" isn't correct by the Run Date needed. The "RODS AVAILABLE" function calculates by the line order instead of by the Run Date. How do I calculate "RODS AVAILABLE" by the Run Date, while still keeping the "Line" in the below order?

I am aware I can sort my table by Run Date and get the RODS AVAILABLE formula to calculate correctly, but I need my lines grouped together like the example below. Thank you for any help you can offer


A B C D E F G
[TABLE="width: 906"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Line[/TD]
[TD="align: center"]Run Date[/TD]
[TD="align: center"]SEQ[/TD]
[TD="align: center"]ORDER AMT[/TD]
[TD="align: center"]ROD[/TD]
[TD="align: center"]RODS AVAILABLE[/TD]
[TD="align: center"]ROD INVENTORY[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]1/21[/TD]
[TD="align: center"] 30[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A07250[/TD]
[TD="align: center"]2700[/TD]
[TD="align: center"]3200[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]1/22[/TD]
[TD="align: center"] 50[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A08000[/TD]
[TD="align: center"]1300[/TD]
[TD="align: center"]1800[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]1/23[/TD]
[TD="align: center"] 55[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A10000[/TD]
[TD="align: center"]700[/TD]
[TD="align: center"]1200[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]1/24[/TD]
[TD="align: center"] 60[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A10000[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]1200[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]1/21[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A10000[/TD]
[TD="align: center"]-300[/TD]
[TD="align: center"]1200[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]1/22[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A07250[/TD]
[TD="align: center"]2200[/TD]
[TD="align: center"]3200[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]1/23[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A07250[/TD]
[TD="align: center"]1700[/TD]
[TD="align: center"]3200[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]1/24[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A08000[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]1800[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]1/21[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A08000[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]1800[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]1/22[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A10000[/TD]
[TD="align: center"]-800[/TD]
[TD="align: center"]1200[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]1/23[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A08000[/TD]
[TD="align: center"]-200[/TD]
[TD="align: center"]1800[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]1/24[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1166H62A07250[/TD]
[TD="align: center"]1200[/TD]
[TD="align: center"]3200[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Can any Excel experts tell me if this is even doable? I've searched all over the internet looking for a solution. Thanks
 
Upvote 0
Not sure if I am getting you right.. But if you are asking for a solution like below mentioned table, then it is possible:

[TABLE="width: 200"]
<tbody>[TR]
[TD]Run Date
[/TD]
[TD="align: center"]Line
[/TD]
[TD="align: center"]SEQ
[/TD]
[TD="align: center"]Order Amount
[/TD]
[/TR]
[TR]
[TD]1/21
[/TD]
[TD="align: center"]13
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]500
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]14
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]500
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]15
[/TD]
[TD="align: center"]40
[/TD]
[TD="align: center"]500
[/TD]
[/TR]
[TR]
[TD]1/22
[/TD]
[TD="align: center"]13
[/TD]
[TD="align: center"]50
[/TD]
[TD="align: center"]500
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]14
[/TD]
[TD="align: center"]25
[/TD]
[TD="align: center"]500
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]15
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]500
[/TD]
[/TR]
</tbody>[/TABLE]

Please let me know we are on same platform..
In other case, if you are trying to sort the table with respect to 2 columns at the same time, I am afraid that's not doable as far as I know.
 
Upvote 0
Right now the formula is subtracting straight down the spreadsheet in the Rods Available column. For example, the spreadsheet is subtracting the 1166h62a10000 rods on dates 1/23 and 1/24 on line 13 before it's subtracting the 1166h62a10000 rods on 1/21 on line 14. I need all the lines to be grouped together like I have it laid out, but I need the Rods Available formula to subtract like values by date. Am I making sense? I can sort by date and everything is right (like your example), but the lines are not grouped together like my example.
 
Upvote 0
Hi,

Got your Tag, if I understand correctly, adjust the formula as follows:


Book1
ABCDEFG
1LineRun DateSEQORDER AMTRODRODS AVAILABLEROD INVENTORY
2131/21/2019305001166H62A0725027003200
3131/22/2019505001166H62A080008001800
4131/23/2019555001166H62A10000-3001200
5131/24/2019605001166H62A10000-8001200
6141/21/2019105001166H62A100007001200
7141/22/2019255001166H62A0725022003200
8141/23/2019155001166H62A0725017003200
9141/24/2019755001166H62A08000-2001800
10151/21/2019405001166H62A0800013001800
11151/22/2019305001166H62A100002001200
12151/23/201955001166H62A080003001800
13151/24/2019455001166H62A0725012003200
Sheet478
Cell Formulas
RangeFormula
F2=IFERROR(G2-SUMIFS(D$2:D$13,E$2:E$13,E2,B$2:B$13,"<="&B2),"")


Formula copied down.
 
Upvote 0
You're welcome, thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
536
mumps
mumps
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
83
Peter_SSs
Peter_SSs
G
  • Locked
  • Question Question
VBA Userform - Change from 1900 Date Format to Long Date
Replies
1
Views
58
Peter_SSs
Peter_SSs
G
  • Question Question
VBA Userform Date Format
Replies
7
Views
184
gilly01625
G
Z
  • Question Question
Breaking down some dates using a formula
2
Replies
13
Views
539
zelarra
Z

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

Share this page

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