Finding Max Value in Duplicates

**Mr.Higgins** · 07-22-2009, 08:40 PM

Hi - I have data that has about 10 duplicate values (UTC Time) in one column and another column with number values (depth ft) that vary. I need to obtain the maximum (highest) value in the depth column and remove the other duplicates to filter out the low values. So for the data example below for UTC 15:56:28 I only want the 5.7 row, for 15:56:29 I want5.3 row and so on. I can attach the sheet if that helps, any help appreciated - this is a huuge dataset so manual filtering wont work. Data is from a sonar that gives 10 depth readings per second - I only need one depth tat is the highest value. Thanks

ID UTC Depth (FT)
1 15:56:28 4.3
2 15:56:28 4.4
3 15:56:28 5
4 15:56:28 5.4
5 15:56:28 5.7
6 15:56:28 5.5
7 15:56:28 5.6
8 15:56:28 5.6
9 15:56:28 5.5
10 15:56:28 5.4
11 15:56:28 5.3
12 15:56:28 5.3
13 15:56:28 5.3
14 15:56:29 5.2
15 15:56:29 5.3
16 15:56:29 5
17 15:56:29 4.9
18 15:56:29 5.1
19 15:56:29 5.1
20 15:56:29 5.2
21 15:56:29 5.2
22 15:56:29 5.1
23 15:56:29 5.1

**JBeaucaire** · 07-22-2009, 08:53 PM

You can create a "helper" column to spot the lowest values.

Please Login or Register  to view this content.

Drop in the formula shown (expand the range to encompass the whole column)
Copy it down the dataset, let Excel do all the calcs.
Click on row 1
Data > Filter > Autofilter
Click column D > Custom
Greater than 0
Delete all the visible data
Click Data > Filter > AutoFilter to turn off the filter
The remaining rows reappear and all are the rows you want.
Clear column D (the one you added)

**Mr.Higgins** · 07-23-2009, 06:25 PM

WOW that worked - thanks a bunch you save hours of processing time! I am not sure exactly what the formula did, not sure what a helper is but thank you.

**JBeaucaire** · 07-24-2009, 12:08 PM

A helper column is a column you add to existing data that is not useful in any way except to allow some reporting to occur...it's not part of the original "actual" data.

By adding in that "helper column" of formulas, you create a series of numbers that allows you to something hard in an easy way. In this instance, the hard thing is "find the lowest time for each time". The rows with "0" in the helper column are the lowest, so you can copy all those rows, or delete all the others.

===========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)