remove columns which show "0" as sum
 

My data has following structu

A09 (code) and B09(time) are headings of a table, its data is filled from
A10 upto B100 or so. A column contains codes which take a value from 1 to 20.
Column B contains data corresponding to the code contained in A. A and B are
filled periodically, and manually.

A01=CODE, A02=TOTAL

Now B02 to U02 contain codes 1 to 20.
B03 to U03 should contain total corresponding to each code, for example
B03 = sumif(A10:A100,"B2",B10:B100)

It is possible that for some codes data does not exist, in that case the
total would be "0".

Is there a function of method which would remove the columns which have zero
total, and leave only those columns which have some data in it, when these
two rows are copied and pasted at a different worksheet?

It is something like, if H03 has total "0", G02 should take place of H02,
and G03 should take place of H03.

remove columns which show "0" as sum
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"hsg" wrote in message
...
My data has following structu

A09 (code) and B09(time) are headings of a table, its data is filled from
A10 upto B100 or so. A column contains codes which take a value from 1 to
20.
Column B contains data corresponding to the code contained in A. A and B
are
filled periodically, and manually.

A01=CODE, A02=TOTAL

Now B02 to U02 contain codes 1 to 20.
B03 to U03 should contain total corresponding to each code, for example
B03 = sumif(A10:A100,"B2",B10:B100)

It is possible that for some codes data does not exist, in that case the
total would be "0".

Is there a function of method which would remove the columns which have
zero
total, and leave only those columns which have some data in it, when these
two rows are copied and pasted at a different worksheet?

It is something like, if H03 has total "0", G02 should take place of H02,
and G03 should take place of H03.

remove columns which show "0" as sum
 

Now B02 to U02 contain codes 1 to 20.
B03 to U03 should contain total corresponding to each code, ...
It is possible that for some codes data does not exist, in that case the
total would be "0".

Is there a function of method which would remove the columns which have zero
total, and leave only those columns which have some data in it...?

This approach uses B1:U1, B4:U5.

In B1 put
=IF(B3=0,0,1)

In C1 put
=IF(C3=0,0,MAX($B1:B1)+1)
and extend it to U1.

In B4 put
=IF(COLUMN()-1MAX($B$1:$U$1),"",
HLOOKUP(COLUMN()-1,$B$1:$U$3,2,FALSE))
and extend it to U4.

In B5 put
=IF(B4="","",
HLOOKUP(COLUMN()-1,$B$1:$U$3,3,FALSE))
and extend it to U5.

The desired result should be in B4:U5.

Modify to suit.

remove columns which show "0" as sum
 

It works !!! Thanks a lot !

"zvkmpw" wrote:

Now B02 to U02 contain codes 1 to 20.
B03 to U03 should contain total corresponding to each code, ...
It is possible that for some codes data does not exist, in that case the
total would be "0".

Is there a function of method which would remove the columns which have zero
total, and leave only those columns which have some data in it...?

This approach uses B1:U1, B4:U5.

In B1 put
=IF(B3=0,0,1)

In C1 put
=IF(C3=0,0,MAX($B1:B1)+1)
and extend it to U1.

In B4 put
=IF(COLUMN()-1MAX($B$1:$U$1),"",
HLOOKUP(COLUMN()-1,$B$1:$U$3,2,FALSE))
and extend it to U4.

In B5 put
=IF(B4="","",
HLOOKUP(COLUMN()-1,$B$1:$U$3,3,FALSE))
and extend it to U5.

The desired result should be in B4:U5.

Modify to suit.
.