Need VBA code or formula ( Next Largest Value )

F

furqan_q

New Member
Joined
Dec 21, 2017
Messages
19
Hi,
I need help finding some way out in VBA or just formula .My conditions are :
Main column is " D ", if Column J is "Open" then i need next great value from column "C" to column "E" and vise versa .(attached snapshot)
 

Attachments

  • 1Capture.JPG
    1Capture.JPG
    70.1 KB · Views: 32

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
furqan_q said:
Hi,
I need help finding some way out in VBA or just formula .My conditions are :
Main column is " D ", if Column J is "Open" then i need next great value from column "C" to column "E" and vise versa .(attached snapshot)
Click to expand...
 

Attachments

  • Capture2.JPG
    Capture2.JPG
    48.3 KB · Views: 17
Upvote 0

u can see the arrows ... it should take the next higher time from the all list ... andin end it should give zero as there is no more high value for 200255
 
Upvote 0
furqan_q said:
u can see the arrows ... it should take the next higher time from the all list ... andin end it should give zero as there is no more high value for 200255
Click to expand...
and it copy high time when it status is OPEN, other wise should look for next open status
 
Upvote 0
Book1
ABCDEFGHI
1
2
31/14/202010:10:1020025512:10:10 PMOpen
41/14/202012:10:10200255 Close
51/14/202014:10:10200255 Close
61/14/202016:10:10200255 Close
71/14/202019:10:10200255 Close
81/14/202020:10:10200255 Close
91/14/202023:10:10200255 Close
101/15/202019:10:10200245 Close
111/15/202023:10:10200245 Close
121/15/202003:10:10200245 Close
131/15/202007:10:10200245 Close
141/15/202011:10:10200245 Close
151/15/202015:10:102002457:10:10 PMOpen
161/15/202019:10:10200245 Close
171/15/202023:10:10200245 Close
181/15/202003:10:10200245 Close
191/15/202007:10:10200245 Close
201/15/202011:10:10200245 Close
211/15/202015:10:10200245 Close
Sheet1
Cell Formulas
RangeFormula
E3:E21E3=IFERROR(INDEX($C$3:$C$21,(ROW($E$3:$E$21)-ROW($E$3)+1)/($I$3:$I$21="Open")*ROWS($C$3:C4)),"")
 
Upvote 0
Thanks you sooo much for ur help ... there is one thing more it should see only 200255 and 200245 in list after status is open .... 200255 can be any were in this list with out sorting ... can you help me with this ...
 
Upvote 0
Greetings
one condition may some one help you or summarize below Formula
Sorry
Book1
ABCDEFGHI
1
2
31/14/202010:10:1020025512:10:10Open
41/14/202012:10:1020025514:10:10Close
51/14/202014:10:1020025516:10:10Close
61/14/202016:10:1020025519:10:10Close
71/14/202019:10:1020025520:10:10Close
81/14/202020:10:1020025523:10:10Close
91/15/202019:10:10200245 Close
101/15/202023:10:10200245 Close
111/14/202023:10:10200255 Close
121/15/202003:10:10200245 Close
131/15/202007:10:10200245 Close
141/15/202011:10:10200245 Close
151/15/202015:10:1020024519:10:10Open
161/15/202019:10:1020024523:10:10Close
171/15/202023:10:1020024503:10:10Close
181/15/202003:10:1020024507:10:10Close
191/15/202007:10:1020024511:10:10Close
201/15/202011:10:1020024515:10:10Close
211/15/202015:10:10200245 Close
22
Sheet1
Cell Formulas
RangeFormula
E3:E21E3=IFERROR(INDEX($C$3:$C$21,AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+1)/((ROW($D$3:$D$21)-ROW($D$3)+1)>AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+0)/($I$3:$I$21="Open"),COUNTIF($I$3:I3,"Open")))/($D$3:$D$21=INDEX($D$3:$D$21,AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+1)/($I$3:$I$21="Open"),COUNTIF($I$3:I3,"Open")))),(ROWS($D$3:D3)-AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+1)/($I$3:$I$21="Open"),COUNTIF($I$3:I3,"Open"))+1)+1)),"")
 
Upvote 0
Hi
what about below if ?ok feedback (y)


x1.xlsx
ABCDEFGHI
1
2
31/14/202010:10:1020025512:10:10Open
41/14/202012:10:1020025514:10:10Close
51/14/202014:10:1020025516:10:10Close
61/14/202016:10:1020025519:10:10Close
71/14/202019:10:1020025520:10:10Close
81/14/202020:10:1020025523:10:10Close
91/15/202019:10:10200245 Close
101/15/202023:10:10200245 Close
111/14/202023:10:102002550Close
121/15/202003:10:10200245 Close
131/15/202007:10:10200245 Close
141/15/202011:10:10200245 Close
151/15/202015:10:1020024519:10:10Open
161/15/202019:10:1020024523:10:10Close
171/15/202023:10:1020024503:10:10Close
181/15/202003:10:1020024507:10:10Close
191/15/202007:10:1020024511:10:10Close
201/15/202011:10:1020024515:10:10Close
211/15/202015:10:102002450Close
Sheet1
Cell Formulas
RangeFormula
E3:E4E3=IFERROR(INDEX($C$3:$C$21,AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+1)/((ROW($D$3:$D$21)-ROW($D$3)+1)>AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+0)/($I$3:$I$21="Open"),COUNTIF($I$3:I3,"Open")))/($D$3:$D$21=INDEX($D$3:$D$21,AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+1)/($I$3:$I$21="Open"),COUNTIF($I$3:I3,"Open")))),(ROWS($D$3:D3)-AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+1)/($I$3:$I$21="Open"),COUNTIF($I$3:I3,"Open"))+1)+1)),IF(COUNTIF($E2:E$3,C3)<>0,TEXT(0,"@"),""))
E5:E21E5=IFERROR(INDEX($C$3:$C$21,AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+1)/((ROW($D$3:$D$21)-ROW($D$3)+1)>AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+0)/($I$3:$I$21="Open"),COUNTIF($I$3:I5,"Open")))/($D$3:$D$21=INDEX($D$3:$D$21,AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+1)/($I$3:$I$21="Open"),COUNTIF($I$3:I5,"Open")))),(ROWS($D$3:D5)-AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+1)/($I$3:$I$21="Open"),COUNTIF($I$3:I5,"Open"))+1)+1)),IF(COUNTIF($E$3:E4,C5)<>0,TEXT(0,"@"),""))
 
Last edited:
Upvote 0
Thank you dear so much for ur help ... i will check it and will let you know, but its seems fine to me till now .
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Help with formula please, if two values match calculate the difference between 2 dates
2
Replies
11
Views
402
Newbie73
N
D
  • Question Question
VBA Code Copy/Replace Values base on Date
Replies
1
Views
106
Anthony47
A
P
  • Solved
Need help with VBA code prompting Compile error: Next without For
2
Replies
15
Views
442
Pi_Lover
P
L
  • Question Question
Creating macro for nested calculations within calculations
Replies
2
Views
105
LizN
L
I
  • Solved
FILTER formula criteria based of a variable with VBA
Replies
2
Views
245
ItalianPlatinum
I

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

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