It worked. thank you very much Jacob.

"Jacob Skaria" wrote:

Hi Meeru

If you want your data to be in the same column and not to spread across
columns; use the below formula

In D2 enter the array formula

=IF(COUNTIF($B$1:$B$100,C2)=COUNTIF(C$1:C2,C2),IN DEX($A$1:$A$100,SMALL(IF($B$1:$B$100=$C2,ROW($B$1: $B$100)),COUNTIF(C$1:C2,C2))),"")

Result as below

Col A Col B Col C Col D
Items Date Date Item
ABC 2/4/2009 2/4/2009 ABC
DEF 4/5/2009 4/5/2009 DEF
AEG 2/4/2009 2/4/2009 AEG
AEH 2/4/2009


If this post helps click Yes
---------------
Jacob Skaria


"Meeru" wrote:

jacob, i tried and it gave the below result, i entered the formula as you
have mentioned, i enter dates in c2 and get the result from d2:d5, is there
something wrong in the formula.

Date Items
2/4/2009 ABC
4/5/2009 ABC
ABC
ABC


"Jacob Skaria" wrote:

Try the below test

Col A Col B Col C Col D
Items Date QDate Items
ABC 2/4/2009 2/4/2009 =
DEF 4/5/2009 2/4/2009 =
AEG 2/4/2009
AEH 2/4/2009

--QDate represents Query Date.
--In cell D2; enter the below formula; Please note that this is an array
formula. An array formula can perform multiple calculations and then return
either a single result or multiple results. Array formulas act on two or more
sets of values known as array arguments. Each array argument must have the
same number of rows and columns. You create array formulas in the same way
that you create other formulas, except you press CTRL+SHIFT+ENTER to enter
the formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

=INDEX($A$1:$A$100,SMALL(IF($B$1:$B$100=C2,ROW($B$ 1:$B$100)),ROW(A1)))

If this post helps click Yes
---------------
Jacob Skaria


"Meeru" wrote:

i have the following data:-

Items # Date
ABC 2/4/09
DEF 4/5/09
AEG 2/4/09

I want to auto extract items# when you enter a date, but i do have a
duplicate date how do i auto extract when there is a duplicate date:-

Date Items
2/4/09 ABC
2/4/09 AEG
thanks for your help
meeru