Dynamic filtered lists

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ray - 08 Feb 2007 19:11 GMT

Hello -

Let's say I have 2 sheets ... sheet1 has 5 stores (A2:A11), with a
number (0-5) in B2:B11. The value of B2:B11 is based on a formula and
is (of course) dynamic. On sheet2, I'd like a list of ONLY those
stores with a value greater than 0 and the value assigned to that
store. So, an example:

Store 1 = 0
Store 2 = 3
Store 3 = 1
Store 4 = 0
Store 5 = 5

Result on sheet2 would be:
Store 2 3
Store 3 1
Store 5 5

How would I do something like this? I'm comfortable with VBA, so
that's an option...

TIA,
Ray

Reply to this Message

Debra Dalgleish - 08 Feb 2007 23:41 GMT

You could record the steps as you manually apply an advanced filter,
then run the macro as required. There are instructions in Excel's Help,
and here:

http://www.contextures.com/xladvfilter01.html

In the criteria area, test for values greater than zero.

> Hello -
>
[quoted text clipped - 20 lines]
> TIA,
> Ray

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

T. Valko - 09 Feb 2007 05:23 GMT

You still have to run the macro so it isn't dynamic! <g>

Biff

> You could record the steps as you manually apply an advanced filter, then
> run the macro as required. There are instructions in Excel's Help, and
[quoted text clipped - 28 lines]
>> TIA,
>> Ray

Reply to this Message

Debra Dalgleish - 09 Feb 2007 05:34 GMT

Obviously, it runs automatically when the workbook opens, or the data
changes. <g>

> You still have to run the macro so it isn't dynamic! <g>
>
[quoted text clipped - 32 lines]
>>>TIA,
>>>Ray

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Ray - 07 Mar 2007 19:41 GMT

> Obviously, it runs automatically when the workbook opens, or the data
> changes. <g>
[quoted text clipped - 44 lines]
> Debra Dalgleish
> Contextureshttp://www.contextures.com/tiptech.html

A follow-up question .... but first, here's my code so far:

Sub CallOut()
' this macro creates a list of Stores with "data issues"

Range("M46:N81").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"P46:P47"), CopyToRange:=Range("S46:T46"), Unique:=False

Range("S47:T81").Select
Selection.Sort Key1:=Range("T47"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

This code works great, giving me exactly the list I want, sorted in
descending order of #_of_Issues. HOWEVER, in practice, the list is
too long! So, what I want to do is show the 'worst offenders' and
then a summary of the rest. For instance:
Store A - 8
Store D - 7
Store X - 5
6stores with 2
7stores with 1

Is there a way to do this?

TIA,
Ray

Reply to this Message

Debra Dalgleish - 07 Mar 2007 20:35 GMT

Insert a column to the right of your store data
In the new column, in cell O46, add the heading: Stores
In O47, enter the formula:

=IF(N47=0,"",IF(N47>=LARGE($N$47:$N$81,3),M47,
COUNTIF($N$47:$N$81,N47)&" Stores with "))
and copy down to row 81

In the criteria heading (Q46), use the heading from the Issues column
IN the criteria cell below (Q47), enter: >0

In the extract range use the headings Stores and Issues

Change your code slightly, as shown below, to use the revised ranges,
and to filter for unique values:

'============================
Sub CallOut()
' this macro creates a list of Stores with "data issues"

Range("M46:O81").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Q46:Q47"), _
CopyToRange:=Range("T46:U46"), Unique:=True

Range("T47:U81").Select
Selection.Sort Key1:=Range("U47"), _
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

'===========================

>>Obviously, it runs automatically when the workbook opens, or the data
>>changes. <g>
[quoted text clipped - 76 lines]
> TIA,
> Ray

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Ray - 07 Mar 2007 21:05 GMT

> Insert a column to the right of your store data
> In the new column, in cell O46, add the heading: Stores
[quoted text clipped - 113 lines]
> Debra Dalgleish
> Contextureshttp://www.contextures.com/tiptech.html

Excellent, Debra! Thanks very much .... I'm constantly amazed at how
fast MVPs are able to solve such problems...

I did make one adjustment to the FORMULA -- I added 'N47' at the very
end of the equation, making it:
=IF(N47=0,"",IF(N47>=LARGE($N$47:$N$81,3),M47,
COUNTIF($N$47:$N$81,N47)&" Stores with "& N47))

Without that reference, the result is something like " 8 Store with"
and nothing else ;)

Thanks again...
Rgds, ray

Reply to this Message

Debra Dalgleish - 07 Mar 2007 21:15 GMT

You're welcome, and thanks for letting me know that it worked.

I left off the issue count because it should appear in the adjacent
column, and didn't know if you'd want it duplicated. Glad you were able
to adjust it to your preference.

>>Insert a column to the right of your store data
>>In the new column, in cell O46, add the heading: Stores
[quoted text clipped - 127 lines]
> Thanks again...
> Rgds, ray

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Ray - 07 Mar 2007 21:10 GMT

Excellent, Debra ... THANKS very much! I'm constantly amazed at how
fast MVPs are able to create solutions for such vaguely described
problems...

Rgds, Ray

Reply to this Message