• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter unique records, based on value in another column

V

vandam

New Member
In a table I have a column with ID numbers (values)and a similar column having names (text) linked to these ID numbers. There are many records that have the same ID.


In another column there are values (ages of siblings). In case of multiple IDs, I need only the records of IDs that have the lowest value in that second column.


Example of table:


ID Name Age siblings Other1 Other2 Other3

1 Mary 2 … … …

2 Stephany 4 … … …

3 Carol 8 … … …

3 Carol 5 … … …

4 Louise 1 … … …

5 Jennifer 4 … … …

5 Jennifer 3 … … …

5 Jennifer 7 … … …

6 Kim 5 … … …


What I need after after filtering the multiple IDs, based on the lowest age of siblings for each particular ID.


Example of desired result:


ID Name Age siblings Other1 Other2 Other3

1 Mary 2 … … …

2 Stephany 4 … … …

3 Carol 5 … … …

4 Louise 1 … … …

5 Jennifer 3 … … …

6 Kim 5 … … …


In the output, all records of all IDs appear, and all appear only once, with the record containing the age of the younghest sibling.


This example I did manually, but I have to deal with a table of approximately 80.000 records (rows). Any suggestion, no mather how complicated, is more than welcome. Thanks in advance.
 
This is easy!


Make a pivot table. Put Id's in the row field and Age in the Data field. Then double Click the age in the data field, and change the summarize by from 'sum' to 'Min'


Walla! all done
 
Hello Montrey,


Your suggestion indeed seems very simple, thanks. Question. Can I incorporate all other columns as well somewhere in the pivot table, so that after the move, unique records are made containing all other information as well?
 
pivot table is the easy out for your problem, if u want a formula u can try this.


1)select all the names & paste it in a different sheet & use the remove duplicate function from data tab. Now u have the unique list of name.


2)=MIN(IF(select the original names column = select the cell from the unique list of name,the age column))This is an array formula shift+ctrl+enter
 
You must log in or register to reply here.
Back
Top