Excel Problem

[dan038]

Column A Column B Column C Column F Column G Column H
DATE PRODUCT QTY DATE PRODUCT QTY
Oct 05 Apples 2
Oct 03 Orange 1
Oct 10 Candy 1
Oct 03 Cookies 5
Oct 03 Grapes 3
Oct 07 Apples 6
Oct 03 Orange 1

QUESTION? Extract all "OCT 03" dates put on "Column F",
"PRODUCT" put on "Column G",
"QTY" put on "Column H" by using formula only.

"VLOOKUP"??? the problem with "VLOOKUP" only looks for first row with the Oct 03 date. I need the formula to get the next Oct 03 row going down the Column A and transfer data continuously down Column F. This formula needs to continue pulling every occurance of the Oct 03 date. Any help with solving this problem is appreciated.

Dan

[Max]

One way via non-array formulas ..

Demo file to play around with at:
http://www.savefile.com/files/3599672
FilteringviaFormulas_onSameSheet_dan038_misc.xls

Assume the date of interest will be input in say, E1

Put in I2: =IF($E$1="","",IF(A2=$E$1,ROW(),""))
(Leave I1 empty)

Put in F2:
=IF(ISERROR(SMALL($I:$I,ROWS($A$1:A1))),"",
INDEX(A:A,MATCH(SMALL($I:$I,ROWS($A$1:A1)),$I:$I,0)))
Copy F2 across to G2
Format F2 as date

Select F2:I2, fill down to say I20,
to cover the max expected data range in the source table

Cols F to G will return the desired results from the source table
depending on the date input in E1
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"dan038" <[email protected]> wrote in
message news:[email protected]...
>
> Column A Column B Column C Column F Column G
> Column H
> DATE PRODUCT QTY DATE
> PRODUCT QTY
> Oct 05 Apples 2
> Oct 03 Orange 1
> Oct 10 Candy 1
> Oct 03 Cookies 5
> Oct 03 Grapes 3
> Oct 07 Apples 6
> Oct 03 Orange 1
>
> QUESTION? Extract all "OCT 03" dates put on "Column F",
> "PRODUCT" put on "Column
> G",
> "QTY" put on "Column H" by
> using formula only.
>
> "VLOOKUP"??? the problem with "VLOOKUP" only looks for first row with
> the Oct 03 date. I need the formula to get the next Oct 03 row going
> down the Column A and transfer data continuously down Column F. This
> formula needs to continue pulling every occurance of the Oct 03 date.
> Any help with solving this problem is appreciated.
>
> Dan
>
>
> --
> dan038
> ------------------------------------------------------------------------
> dan038's Profile:
http://www.excelforum.com/member.php...o&userid=27987
> View this thread: http://www.excelforum.com/showthread...hreadid=474942
>