Creating summary table

[agbiggs]

I'm a pretty experienced user and this may be a simple task, but I
don't know exactly how to start. I have a worksheet where columns
represent different countries and rows different years, and if the
country managed to balance its balance its budget in a given year the
worksheet shows a value of 1 and if not, a value of zero. (The
definition of balanced budget is more complex, but doesn't matter
here.)

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

 

[Dave Peterson]

Maybe you can add headers (if you don't already have them), then use
Data|filter|autofilter (xl2003 menus) to hide the 0's. Or the countries you
don't want to see.

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.

 

[Gary''s Student]

Say we have data in A1 thru G12 like:

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

 

[agbiggs]

Say we have data in A1 thru G12 like:

        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

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!

 

[agbiggs]

Maybe you can add headers (if you don't already have them), then use
Data|filter|autofilter (xl2003 menus) to hide the 0's.  Or the countries you
don't want to see.

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.

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.

 

[Dave Peterson]

If you copied just the visible cells, the new table wouldn't have those unwanted
data in it.

 

[Steve Dunn]

A possibility without VBA, (example data in A1:G12):

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.

 

[Steve Dunn]

Slight hiccup between what I did and what I described. All references to Q
should be replaced with J, as below.


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]

Aaagh, I should really start engaging brain before pressing send. The cells
that the formulae refer to are tied to the cells that they are contained in.
Please follow as below:


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.

 

[agbiggs]

Aaagh, I should really start engaging brain before pressing send.  The cells
that the formulae refer to are tied to the cells that they are contained in.
Please follow as below:

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.

Wow -- very sophisticated stuff, but it might do the trick. I'll give
it a try. Thanks!

Andrew