Summarising lists by removing empty entries

[andi]

I have a table in Excel of contracts of yearly revenues:

2002 2003

A 10 10
B 0 7
C 0 0
D 5 0

and I want to be able to create a summarised version, where the zeros
are stripped out:

2002
A 10
D 5

2003
A 10
B 7

Sounds like there should be a vlookup-esque function for this... any
ideas?

 

[Dave Peterson]

Just eliminate the 0's?

If your ranges don't have any errors in them:

Copy to a new worksheet (if you want to keep the original data)

Select your range (A:B?)
Edit|replace
replace what: 0
replace with: #n/a
(make sure you select "match entire cell contents" or you'll be mad!)
replace all

(your range should still be selected, but if it isn't, select it again)

F5 (or edit|goto)
click special
click Constants
uncheck everything but errors
click ok.

Right click on a cell in the selection (now just errors)
select delete
and shift cells up.