How to pull a unique list of dates based on 2 criteria?

[gizmo]

Hey guys. Does anyone know the formula to pull a unique list of dates based on 2 criteria? I dont want to use a Pivot Table for this either. Data would be on one tab and the results would be on a separate tab.


EX:

Week Phase Person

Thu 05/02/13 Pre-Cert Angela

Thu 04/25/13 Cert Angela

Thu 04/18/13 Pre-Cert Angela

Thu 04/11/13 Pre-Cert Angela

Thu 04/04/13 Cert Angela

Thu 04/04/13 Pre-Cert Jana

Thu 04/25/13 Cert Jana

Thu 04/18/13 Pre-Cert Jana

Thu 04/11/13 Pre-Cert Jana


If the Criteria is: Person = Angela & Phase = Pre-Cert


Week Dates pulled back:

Week Phase Person

Thu 05/02/13 Pre-Cert Angela

Thu 04/18/13 Pre-Cert Angela

Thu 04/11/13 Pre-Cert Angela


How do you do that?


Thank you in advance!!


JB

 

[jason]

couldnt you use the advanced filter?

 

[jason]

you'll need to setup your 'criteria'. all this would involve would be:


go to the sheet where you want the results and in A1 type "criteria"

In A2 type Phase and in B2 type Person

In A3 type Pre-Cert and in B3 type Angela

HINT: Copy and paste may be better/more accuate than typing


then go to Advanced Filter:

1. select Copy results to another location

2. Specify where your data is

3. specify where your criteria is (Sheet2!A2:B3) ((dont include A1, this is only intended as a label))

4. specify where you want your results (i'd go a couple rows below A3, but up to you)

5. Hit OK

 

[Kevin@Radstock]

Hi gizmo


For a formula approach. Assuming your data is in A1:C10 (Inc. column headers).Cell F1 has the phase criteria: Pre-Cert. Cell H1 has the Person criteria: Angela. The formula is assuming that you are using Excel 2007 >. For earlier versions replace the IFERROR with IF & ISERROR.

Then in cell say E3 copy across to G3 and down:

=IFERROR(INDEX(A$2:A$10,SMALL(IF(($B$2:$B$10=$F$1)*($C$2:$C$10=$H$1),ROW($2:$10)-ROW($1:$1)),ROW($A1))),"") Array formula, to commit. CTRL + SHIFT + ENTER


Kevin