Find the Highest Value After First Occurrence Another Value in Column

JamesA11

New Member
Joined
Oct 2, 2020
Messages
5
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
544
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,272
Messages
5,571,243
Members
412,373
Latest member
HelpPls21NZ
Top