What I'd like to do is create a summary table that would have two
columns, listing the country and the year in which its budget was
balanced, but omitting any countries/years in which it wasn't. So I'm
trying to build a list of countries/years that satisfy certain
criteria. From here I'd build charts, do other calculations, etc.
I don't think that standard list function is going to work in this
context, but was wondering if there's another way to do this. Thanks!
Andrew
There's an option for charts to display only visible data, so that seems to fit
your needs, too.
In xl2003 menus:
Select the chart
Tools|Options|Chart tab
Check/uncheck Plot Visible cells only.
=====
I'm not sure what calculations you're going to use, but maybe you could add a
check to see if the cell in that column is greater than 0.
=======
Or maybe you could just filter to show just the rows you want and copy those
visible cells to a new worksheet.
agb...@hotmail.com wrote:
--
Dave Peterson
Oberon Titania Ariel Umbriel Miranda Puck
2000 1 0 1 1 0 0
2001 0 1 1 1 0 0
2002 0 0 0 0 1 0
2003 0 1 0 0 0 0
2004 0 0 0 1 0 1
2005 0 0 0 0 1 0
2006 0 0 1 0 1 0
2007 0 0 1 0 0 0
2008 0 0 0 1 0 0
2009 0 0 1 0 0 0
2010 0 1 0 1 0 0
If we run this small macro:
Sub organize()
nn = 1
For i = 2 To 7
For j = 2 To 12
If Cells(j, i).Value = 1 Then
Cells(nn, "K").Value = Cells(j, 1).Value
Cells(nn, "L").Value = Cells(1, i).Value
nn = nn + 1
End If
Next
Next
End Sub
then the following summary table will be produced in cols K & L:
2000 Oberon
2001 Titania
2003 Titania
2010 Titania
2000 Ariel
2001 Ariel
2006 Ariel
2007 Ariel
2009 Ariel
2000 Umbriel
2001 Umbriel
2004 Umbriel
2008 Umbriel
2010 Umbriel
2002 Miranda
2005 Miranda
2006 Miranda
2004 Puck
Macros are very easy to install and use:
1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window
If you save the workbook, the macro will be saved with it.
To remove the macro:
1. bring up the VBE window as above
2. clear the code out
3. close the VBE window
To use the macro from Excel:
1. ALT-F8
2. Select the macro
3. Touch RUN
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
--
Gary''s Student - gsnu201002
"agb...@hotmail.com" wrote:
> .
>
The macro sounds like the way to go. I'm not very good writing these
kinds of things, but I might be able to take your language and adapt
it to what I've got. Thanks very much!
Thanks, Dave. One problem here is that if you want to do any
calculations based on the new table, I don't think Excel hides the
'hidden' data. E.g., it would include the data that didn't meet my
criteria even if I couldn't see it on the screen.
agb...@hotmail.com wrote:
--
Dave Peterson
In top left cell of summary table range (e.g. J1):
=$B$1
In J2:
=IF(SUM(OFFSET($A$2:$A$12,,MATCH($Q2,$B$1:$G$1,0)))>
COUNTIF($Q$2:$Q2,$Q2),$Q2,
INDEX($B$1:$G$1,MATCH($Q2,$B$1:$G$1,0)+1))
In K1:
=IF(SUM(OFFSET($A$2:$A$12,,MATCH($Q2,$B$1:$G$1,0))),
INDEX($A$2:$A$12,SMALL(INDEX(OFFSET($A$2:$A$12,,
MATCH($Q2,$B$1:$G$1,0))*(ROW(A$2:A$12)-
CELL("ROW",A$2:A$12)+1),),COUNTIF(OFFSET($A$2:$A$12,,
MATCH($Q2,$B$1:$G$1,0)),0)+COUNTIF($Q$2:$Q2,$Q2))),
"Never Balanced")
Copy K1 to K2, then copy J2:K2 down as far as required.
HTH
Steve D.
<agb...@hotmail.com> wrote in message
news:5efe2f99-16d3-4248...@y12g2000vbg.googlegroups.com...
In top left cell of summary table range (e.g. J1):
=$B$1
In J2:
=IF(SUM(OFFSET($A$2:$A$12,,MATCH($J2,$B$1:$G$1,0)))>
COUNTIF($J$2:$J2,$J2),$J2,
INDEX($B$1:$G$1,MATCH($J2,$B$1:$G$1,0)+1))
In K1:
=IF(SUM(OFFSET($A$2:$A$12,,MATCH($J2,$B$1:$G$1,0))),
INDEX($A$2:$A$12,SMALL(INDEX(OFFSET($A$2:$A$12,,
MATCH($J2,$B$1:$G$1,0))*(ROW(A$2:A$12)-
CELL("ROW",A$2:A$12)+1),),COUNTIF(OFFSET($A$2:$A$12,,
MATCH($J2,$B$1:$G$1,0)),0)+COUNTIF($J$2:$J2,$J2))),
"Never Balanced")
Copy K1 to K2, then copy J2:K2 down as far as required.
"Steve Dunn" <st...@sky.com> wrote in message
news:27B4EF4C-405F-4D58...@microsoft.com...
In J2:
=$B$1
In J3:
=IF(SUM(OFFSET($A$2:$A$12,,MATCH($J2,$B$1:$G$1,0)))>
COUNTIF($J$2:$J2,$J2),$J2,
INDEX($B$1:$G$1,MATCH($J2,$B$1:$G$1,0)+1))
In K2:
=IF(SUM(OFFSET($A$2:$A$12,,MATCH($J2,$B$1:$G$1,0))),
INDEX($A$2:$A$12,SMALL(INDEX(OFFSET($A$2:$A$12,,
MATCH($J2,$B$1:$G$1,0))*(ROW(A$2:A$12)-
CELL("ROW",A$2:A$12)+1),),COUNTIF(OFFSET($A$2:$A$12,,
MATCH($J2,$B$1:$G$1,0)),0)+COUNTIF($J$2:$J2,$J2))),
"Never Balanced")
Copy K2 to K3, then copy J3:K3 down as far as required.
Make sure that you change references to J2 in the formulae to reflect
wherever you place the formula.
"Steve Dunn" <st...@sky.com> wrote in message
news:BD1EF7BA-5D2A-4BA3...@microsoft.com...
Wow -- very sophisticated stuff, but it might do the trick. I'll give
it a try. Thanks!
Andrew