Dynamic filtered lists

[Ray]

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

 

[Debra Dalgleish]

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.

 

[T. Valko]

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

Biff

 

[Debra Dalgleish]

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

 

[Ray]

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

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( _
"P4647"), 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

 

[Debra Dalgleish]

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

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

 

[Ray]

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

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

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

 

[Ray]

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

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

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

 

[Debra Dalgleish]

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.