dynamic filtering of an array
Posted by Chris on April 14, 2001 4:16 PM
is there any way to filter out a list into several columns even if the list is dynamically updated?
I am trying to seperate values based on date they correspond to in an array... ie. Values in Column A >= 1/1/01 AND <= 1/7/01 should appear in Fiscal Week 1 Column...
Posted by Aladin Akyurek on April 15, 2001 9:42 AM
Chris
Care to post 15 lines or so of your data? If you're inclined to do so, you can activate an empty cell, enter = and select a range of 15 rows X 3 columns (not all columns if you have many), and hit control+shift+enter at the same time. Go to the formula bar, select the formula, and hit F9. You'll see there an array containing a part of your data. Copy and paste it in your follow-up posting.
Aladin
Posted by Chris on April 15, 2001 12:06 PM
Aladin -
sure, no prob. the third column in the array is in date format and is the one I want to filter on and return corresponding values into a table elswhere.
{2,23505,36920,-7;2,15605,36967,-5;2,53605,36972,-1;2,94605,36976,0;2,64505,36976,0;2,14605,36977,1;2,8605,36978,5;2,96605,36978,7;2,37605,36979,8;2,47605,36979,8;2,75605,36979,9;2,68605,36980,10;2,48605,36980,10;2,38605,36980,12;2,86605,36980,13;2,66203,36980,13;2,96505,36980,13;2,50570,36980,25}
Chris :
Posted by Aladin Akyurek on April 15, 2001 4:26 PM
Chris
First some alternatives.
1) I believe you can do all the computations that you need directly on your original data.
2) Advanced Filter might be a much cheaper solution.
3) A system of formulas to do the job. Be warned it's rather heavy. I'll take up this one in what follows.
First create the following named dynamic ranges:
VALSETA: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
VALSETB: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),1)
SDATES: =OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$C:$C),1)
VALSETD: =OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D:$D),1)
I just assumed your data to be on Sheet1 in the range A2:Dn.
By the way, let me know if you don't know how to define such named dynamic ranges.
Activate Sheet2, to which the data associated Fiscal Week 1 will be showed.
In A1 enter: the date for lower limit
In A2 enter: the date for upper limit
In A3 enter: =IF(((SDATES>=$A$1)*(SDATES<=$A$2)),VALSETA,""),
then while in A3 select m [=COUNTA(SDATES)] rows down column A, go to the formula where you see this formula and array-enter it (that is, hit CONTROL+SHIFT+ENTER at the same time).
Activate A3, copy the formula to B3:C3. Replace VALSETA by VALSETB in B3, then select from B3 on m rows and array-enter the formula. Replace VALSETA by VALSETD in C3, then select from C3 on m rows and array enter the formula.
In E3 array-enter: =IF(ROW()-ROW(E$3:E$17)+1>ROWS(A$3:A$17)-COUNTIF(A$3:A$17,""),"",INDIRECT(ADDRESS(SMALL((IF(A$3:A$17<>"",ROW(A$3:A$17),ROW()+ROWS(A$3:A$17))),ROW()-ROW(E$3:E$17)+1),COLUMN(A$3:A$17))))
Copy this formula across to F3:G3 and then down to m rows.
You'll get the data of interest in a range from F to G neatly in a consecutive series of non-blank rows.
You can repeat this process for other fiscal weeks on other sheets.
Aladin
----------------------
- sure, no prob. the third column in the array is in date format and is the one I want to filter on and return corresponding values into a table elswhere.
Posted by Chris on April 16, 2001 10:15 AM
Aladin -
Thanks!
Yes, I do need you to help w/assigning the dynamic ranges too..!
--Chris
2) Advanced Filter might be a much cheaper solution. 3) A system of formulas to do the job. Be warned it's rather heavy. I'll take up this one in what follows. VALSETB: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),1) SDATES: =OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$C:$C),1) VALSETD: =OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D:$D),1) Aladin ----------------------
Posted by Aladin Akyurek on April 16, 2001 10:33 AM
Hi Chris
I'll take up one named dynamic range that we talked about: VALSETA.
Activate Sheet1 then the option Insert|Name|Define.
Type VALSETA as name for "Names in Worbook:"
Then modify or retype the following formula for "Refers to:"
=OFFSET(Sheet1!$A$2,0,0,COUNTA($A:$A),1)
The values we are interested in are assumed here to be in A from A2 on.
Aladin
Posted by Chris on April 19, 2001 9:20 PM
Aladin -
almost works GREAT but...
the array doesnt seem to update when I add or delete rows in the original table...
is there any way to control the reference to the filtered list so that when it grows or shrinks so does the finished list reference?
something instead of:
=IF(ROW()-ROW(E$3:E$17)+1>ROWS(A$3:A$17)-COUNTIF(A$3:A$17,""),"",....
i tried copying the formula down in more rows then necessary as "place holders" but that led to the '#NUM' error in all the output cells.
-- Chris Hi Chris I'll take up one named dynamic range that we talked about: VALSETA. Activate Sheet1 then the option Insert|Name|Define. Type VALSETA as name for "Names in Worbook:" Then modify or retype the following formula for "Refers to:" =OFFSET(Sheet1!$A$2,0,0,COUNTA($A:$A),1) The values we are interested in are assumed here to be in A from A2 on.
