cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Register for our Discovery Summit 2024 conference, Oct. 21-24, where you’ll learn, connect, and be inspired.
Choose Language Hide Translation Bar
sgschauer
Level I

How to drop duplicates and only retain maximum value

I'm trying to consolidate this data table.  I previously used Excel for it but it is >18,000,000 lines of data which is too big for Excel.  I have multiple different values for the same subject and I only want to retain the maximum value and delete the rest.  The table-join-table method does not have a maximum value only option.  How do I go about consolidating this?

1 REPLY 1
jthi
Super User

Re: How to drop duplicates and only retain maximum value

Below are few options

  1. Create summary table where you use columns you wish to keep as group columns and then calculate max if the value you want the max from
  2. Sort the data based on the value you want to max value for and use Select Duplicate Rows when you have grouping columns selected and delete the selected rows
  3. Write a formula formula which will add new column to tell if column is max or not
    1. :VALUE == Col Max(:VALUE, :GROUP1, :GROUP2) or something similar
-Jarmo