Pull list of unique values based on multiple criterias

[scorpio_libra]

Extract unique values with multiple criteria</SPAN>
I have approx 20,000 record in an excel sheet, wanted to pull the unique values of D column based on the three different criteria.
</SPAN>Date= B Column
Color= C Column
Values= D Column

1^st criteria = From Date = Cell F4
2^nd criteria = To Date = </SPAN>Cell G4
3^rd criteria = Color = </SPAN>Cell I3

I tried lots of possible combinations of formula (below listed for your reference), however nothing worked.</SPAN>
=INDEX(iValue,(iDate>=$F$4)*(iDate<=$G$4)*(Color=$I$3)*(MATCH(0,COUNTIF($E$2:E2,iValue),0)))</SPAN>
Any possible helps would be highly appreciated. Due to security reasons there is strict no no to macro.</SPAN>
Hence I want the answers in formula.</SPAN>


[TABLE="width: 279"]
<TBODY>[TR]
[TD]Date</SPAN>
[/TD]
[TD]Color</SPAN>
[/TD]
[TD]values</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15-Mar-12</SPAN>
[/TD]
[TD]Blue</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14-Feb-12</SPAN>
[/TD]
[TD]Green</SPAN>
[/TD]
[TD]ABC</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20-Apr-12</SPAN>
[/TD]
[TD]Black</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]28-Jan-12</SPAN>
[/TD]
[TD]White</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]24-Jan-12</SPAN>
[/TD]
[TD]Blue</SPAN>
[/TD]
[TD]ABC</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1-Mar-12</SPAN>
[/TD]
[TD]Yellow</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]20-Jan-12</SPAN>
[/TD]
[TD]Black</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]4-May-12</SPAN>
[/TD]
[TD]Orange</SPAN>
[/TD]
[TD]ABC</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]6-Mar-12</SPAN>
[/TD]
[TD]Red</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]29-Jan-12</SPAN>
[/TD]
[TD]Yellow</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5-Apr-12</SPAN>
[/TD]
[TD]Blue</SPAN>
[/TD]
[TD]ABC</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]16-Feb-12</SPAN>
[/TD]
[TD]White</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15-May-12</SPAN>
[/TD]
[TD]Yellow</SPAN>
[/TD]
[TD]ABC</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10-Feb-12</SPAN>
[/TD]
[TD]Blue</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]11-Feb-12</SPAN>
[/TD]
[TD]Orange</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]13-Jun-12</SPAN>
[/TD]
[TD]Black</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]28-Apr-12</SPAN>
[/TD]
[TD]Black</SPAN>
[/TD]
[TD]ABC</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]24-May-12</SPAN>
[/TD]
[TD]Red</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10-Jun-12</SPAN>
[/TD]
[TD]Orange</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]9-Mar-12</SPAN>
[/TD]
[TD]White</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]7-Jan-12</SPAN>
[/TD]
[TD]Red</SPAN>
[/TD]
[TD]XYZ</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]26-Feb-12</SPAN>
[/TD]
[TD]Red</SPAN>
[/TD]
[TD]PQR</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]9-May-12</SPAN>
[/TD]
[TD]Green</SPAN>
[/TD]
[TD]ABC</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[T. Valko]

ms 2007

Here's a small sample file that demonstrates this.

ExtractUniques.xlsx

http://cjoint.com/?BFAqDr2nbRT

 

[scorpio_libra]

/QUOTE]

Thanks for the help .... formula with this sample data is working accurately, even working correctly on the same sample file that was created by me.
However don't know still it wasn't working on the actual file, the only difference with the actual file is, the data is on the another sheet and formula is on the another summary sheet within the same workbook. I dn't think that will create any issues, unless am not using the proper reference. I am getting a results for few of the values only and for remaining getting a #NUM error. To be more precise am getting a correct unique count of 19 but getting a unique value for only 7-8 and for remaining values getting a #NUM error till it reaches the 19th cell and then the blank. This is something am still unable to figure out....

 

[T. Valko]

/QUOTE]

Thanks for the help .... formula with this sample data is working accurately, even working correctly on the same sample file that was created by me.
However don't know still it wasn't working on the actual file, the only difference with the actual file is, the data is on the another sheet and formula is on the another summary sheet within the same workbook. I dn't think that will create any issues, unless am not using the proper reference. I am getting a results for few of the values only and for remaining getting a #NUM error. To be more precise am getting a correct unique count of 19 but getting a unique value for only 7-8 and for remaining values getting a #NUM error till it reaches the 19th cell and then the blank. This is something am still unable to figure out....

Does any of the data you're wanting to extract start with any "unusual" characters?

Characters like: =, <, >, ?, *, <>, ~