Find the Highest Value After First Occurrence Another Value in Column

J

JamesA11

New Member
Joined
Oct 2, 2020
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
I have a set of test data comprising a TestID, Time, ExtractID and Value in a sheet called Data. The data set is large so I have just included an extract below.

For each TestID there are two ExtractIDs and ExtractIDs may appear against multiple TestIDs. I want to be able to find the highest Value for each ExtractID after the first occurrence of another value. This value will be input by the user in cell A1 of the Dashboard tab.

So, for example, if the user inputs 2 into A1 I would like a list of highest values obtained after the first time 2 has been reached for each ExtractID in each TestID.

Book1
ABCD
1TestIDDate/TimeExtractIDVal
217276625206/09/2020 13:40:02103011.98
317276625206/09/2020 13:40:02166062
417276625206/09/2020 13:40:03103011.98
517276625206/09/2020 13:40:03166062
617276625206/09/2020 13:40:04103011.98
717276625206/09/2020 13:40:04166062
817276625206/09/2020 13:40:05103011.98
917276625206/09/2020 13:40:05166062
1017276625206/09/2020 13:40:06103011.98
1117276625206/09/2020 13:40:06166062
1217276625206/09/2020 13:40:07103011.98
1317276625206/09/2020 13:40:07166062
1417276625206/09/2020 13:40:08103011.98
1517276625206/09/2020 13:40:08166062.02
1617276625206/09/2020 13:40:09103011.98
1717276625206/09/2020 13:40:09166062.02
1817276625206/09/2020 13:40:10103011.98
1917276625206/09/2020 13:40:10166062.02
2017276625206/09/2020 13:40:11103011.98
2117276625206/09/2020 13:40:11166062.02
2217276625206/09/2020 13:40:12103011.98
2317276625206/09/2020 13:40:12166062.02
2417276625206/09/2020 13:40:13103011.98
2517276625206/09/2020 13:40:13166062.02
2617276625206/09/2020 13:40:14103011.98
2717276625206/09/2020 13:40:14166062.02
2817276625206/09/2020 13:40:15103011.98
2917276625206/09/2020 13:40:15166062.02
3017276625206/09/2020 13:40:16103011.98
3117276625206/09/2020 13:40:16166062.02
3217276625206/09/2020 13:40:17103011.98
3317276625206/09/2020 13:40:17166062.02
3417276625206/09/2020 13:40:18103011.98
3517276625206/09/2020 13:40:18166062.02
3617276625206/09/2020 13:40:19103011.97
3717276625206/09/2020 13:40:19166062.02
3817276625206/09/2020 13:40:20103011.97
3917276625206/09/2020 13:40:20166062.02
4017276625206/09/2020 13:40:21103011.97
4117276625206/09/2020 13:40:21166062.02
4217276625206/09/2020 13:40:22103011.97
4317276625206/09/2020 13:40:22166062.02
4417276625206/09/2020 13:40:23103011.97
4517276625206/09/2020 13:40:23166062.02
4617276625206/09/2020 13:40:24103011.97
4717276625206/09/2020 13:40:24166062.02
4817276625206/09/2020 13:40:25103011.97
4917276625206/09/2020 13:40:25166062.02
5017276625206/09/2020 13:40:26103011.97
5117276625206/09/2020 13:40:26166062.02
5217276625206/09/2020 13:40:27103011.97
5317276625206/09/2020 13:40:27166062.02
5417276625206/09/2020 13:40:28103011.97
5517276625206/09/2020 13:40:28166062.02
5617276625206/09/2020 13:40:29103011.98
5717276625206/09/2020 13:40:29166062.02
5817276625206/09/2020 13:40:30103011.98
5917276625206/09/2020 13:40:30166062.02
6017276625206/09/2020 13:40:31103011.98
6117276625206/09/2020 13:40:31166062.02
6217276625206/09/2020 13:40:32103011.98
6317276625206/09/2020 13:40:32166062.02
6417276625206/09/2020 13:40:33103011.98
6517276625206/09/2020 13:40:33166062.02
6617276625206/09/2020 13:40:34103011.98
6717276625206/09/2020 13:40:34166062.02
6817276625206/09/2020 13:40:35103011.98
6917276625206/09/2020 13:40:35166062.02
7017276625206/09/2020 13:40:36103011.98
7117276625206/09/2020 13:40:36166062.02
7217276625206/09/2020 13:40:37103011.98
7317276625206/09/2020 13:40:37166062.02
Data


What is the best way of achieving this?

Thanks
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
It sounds like you could use table formatting and a few steps to write a macro. After you format as table, start recording a macro:
1. Filter by value for less than some number (pick a value the first time, set it up to refer to a cell value later)
2. Sort by oldest to newest, then on value by largest to smallest (gives highest value and earliest time that value was reached)
3. Copy whole table, paste on another sheet
4. Remove duplicates based on TestID and ExtractID (leaves first time highest value was reached)

Stop recording. Clean up code as much as you want. Add code to pull value to filter by.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Find column by header AND filter by another column?
Replies
3
Views
174
Fluff
Fluff
H
  • Solved
Delete all rows including cells in column "A" that are identical to the values in cells "D1", "E1","F1" and "A6".
2
Replies
10
Views
266
Peter_SSs
Peter_SSs
P
  • Question
INDEX-MATCH with Duplicate Values
Replies
4
Views
290
SanjayGMusafir
SanjayGMusafir
LRATOZ
  • Question
In Excel 2021 what is the formula to return a date when the max value happened (Compare the same month over many years)
Replies
2
Views
750
LRATOZ
LRATOZ
I
  • Question
Formula to match criteria
2
Replies
18
Views
513
ItalianPlatinum
I

Forum statistics

Threads
1,215,036
Messages
6,122,794
Members
449,095
Latest member
m_smith_solihull

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