Dynamic filtered lists

R

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
 
D

Debra Dalgleish

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

Ray

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

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
 
D

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

'===========================
 
R

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

'===========================
Click to expand...

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
 
R

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

'===========================
Click to expand...

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
 
D

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.
 
Top