Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel Problem
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Problem with Excel 2003 restricting functionality | Excel Discussion (Misc queries) | |||
Problem Editing Macro in Shared Excel File | Excel Discussion (Misc queries) | |||
Excel problem with web based spreadsheets | Excel Discussion (Misc queries) | |||
Staring Excel Problem | Excel Discussion (Misc queries) |