# Microsoft Excel - Filtering

**URL:** <https://boards.straightdope.com/t/microsoft-excel-filtering/520903>\
**Category:** Factual Questions\
**Created:** [December 11, 2009, 7:49pm UTC](https://boards.straightdope.com/t/microsoft-excel-filtering/520903 "2009-12-11T19:49:02Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kid\_A\_1](https://avatars.discourse-cdn.com/v4/letter/k/ed8c4c/32.png) [@Kid\_A\_1](https://boards.straightdope.com/u/Kid_A_1)\
**Post date:** [December 11, 2009, 7:49pm UTC](https://boards.straightdope.com/t/microsoft-excel-filtering/520903/1 "2009-12-11T19:49:02Z")

</div>

I have a spreadsheet like so (actual spreadsheet has over 46,000 records and ~400 unique names in column A):

```auto

A 1/1/2001
A 2/1/2001
A 3/1/2001
B 2/2/2002
B 3/2/2002
B 4/2/2002
C 3/3/2003
C 4/3/2003
C 5/3/2003

```

For each unique name in column A, I need to extract the earliest date that belongs to that name.

What’s the easiest way to do this?

Thanks.

---

<div class="post-metadata">

**Author:** ![Ruminator](https://avatars.discourse-cdn.com/v4/letter/r/b9bd4f/32.png) [@Ruminator](https://boards.straightdope.com/u/Ruminator)\
**Post date:** [December 11, 2009, 8:04pm UTC](https://boards.straightdope.com/t/microsoft-excel-filtering/520903/2 "2009-12-11T20:04:02Z")

</div>

> [@Kid\_A](#):
>
> For each unique name in column A, I need to extract the earliest date that belongs to that name.
> 
> What’s the easiest way to do this?

I’d create a Pivot Table using the MIN() function on the date.

Btw, I recently found out that youtube has lots of videos showing how to create Pivot Tables and Pivot Reports which will probably be easier than trying to read a step-by-step text description of what to do.

---

<div class="post-metadata">

**Author:** ![cmosdes](https://avatars.discourse-cdn.com/v4/letter/c/a587f6/32.png) [@cmosdes](https://boards.straightdope.com/u/cmosdes)\
**Post date:** [December 11, 2009, 8:08pm UTC](https://boards.straightdope.com/t/microsoft-excel-filtering/520903/3 "2009-12-11T20:08:21Z")

</div>

> [@Kid\_A](#):
>
> I have a spreadsheet like so (actual spreadsheet has over 46,000 records and ~400 unique names in column A):
> 
> ```auto
> 
> A 1/1/2001
> A 2/1/2001
> A 3/1/2001
> B 2/2/2002
> B 3/2/2002
> B 4/2/2002
> C 3/3/2003
> C 4/3/2003
> C 5/3/2003
> 
> ```
> 
> For each unique name in column A, I need to extract the earliest date that belongs to that name.
> 
> What’s the easiest way to do this?
> 
> Thanks.

You could probably use a pivot table, or you could do to following:

Sort your data by column A then column B. Add a column C with a formula starting in C2: (If (A1==A2, C1+1, 1). Set C1 to 1.

Then put an autofilter on your data and select just the “1s” from column C.

---

<div class="post-metadata">

**Author:** ![Chessic\_Sense](https://avatars.discourse-cdn.com/v4/letter/c/7c8e57/32.png) [@Chessic\_Sense](https://boards.straightdope.com/u/Chessic_Sense)\
**Post date:** [December 11, 2009, 8:15pm UTC](https://boards.straightdope.com/t/microsoft-excel-filtering/520903/4 "2009-12-11T20:15:34Z")

</div>

> [@cmosdes](#):
>
> You could probably use a pivot table, or you could do to following:
> 
> Sort your data by column A then column B. Add a column C with a formula starting in C2: (If (A1==A2, C1+1, 1). Set C1 to 1.
> 
> Then put an autofilter on your data and select just the “1s” from column C.

That’s almost exactly what I’d do. Can you sort by column A? If so, say “Does this cell match the one above it? If so, “duplicate”. If not, “First occurrence””. One way is:  
=EXACT(A1, A2) \<----Says “FALSE” if it’s the first occurance, “TRUE” if it’s not.

Make sure you sort the list first. And put it in a new column. Then filter it by that.

---

<div class="post-metadata">

**Author:** ![Boyo\_Jim](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@Boyo\_Jim](https://boards.straightdope.com/u/Boyo_Jim)\
**Post date:** [December 11, 2009, 8:25pm UTC](https://boards.straightdope.com/t/microsoft-excel-filtering/520903/5 "2009-12-11T20:25:27Z")

</div>

I will just add that in order to filter unique values within a column, you do have to have that column sorted.

---

<div class="post-metadata">

**Author:** ![Kid\_A\_1](https://avatars.discourse-cdn.com/v4/letter/k/ed8c4c/32.png) [@Kid\_A\_1](https://boards.straightdope.com/u/Kid_A_1)\
**Post date:** [December 11, 2009, 9:38pm UTC](https://boards.straightdope.com/t/microsoft-excel-filtering/520903/6 "2009-12-11T21:38:21Z")

</div>

Thanks for the help. This thread is getting bookmarked since I’m going to be doing this on a regular basis.

---

<div class="post-metadata">

**Author:** ![cmosdes](https://avatars.discourse-cdn.com/v4/letter/c/a587f6/32.png) [@cmosdes](https://boards.straightdope.com/u/cmosdes)\
**Post date:** [December 11, 2009, 11:09pm UTC](https://boards.straightdope.com/t/microsoft-excel-filtering/520903/7 "2009-12-11T23:09:43Z")

</div>

> [@Boyo\_Jim](#):
>
> I will just add that in order to filter unique values within a column, you do have to have that column sorted.

I’m not sure how you mean this. I just built a really simple table:

A…1…X  
B…2…T  
C…1…U

I then turned on autofilter and was able to pick just the 1s in column 2.