Combining data greater than 0

Hi,

I have an order sheet where along the top I have the items and down the side
I have the clients. Currently I enter quantities below the items at the top
of the page to line up with each client rows. What I would like to do is
have another column at the end of the sheet, which would summarize the order
for me. E.g. If along the top I have apples, oranges, bananas, and down the
side I have client A,B,C; I would like to have a summary column that would
say 3-apples, 2-bananas. Any column with a 0 would not show up.

I appreciate any help offered,

Dave

If all the headers don't have any spaces, you could use something like:

=SUBSTITUTE(TRIM(IF(B2=0,"",B2&"-"&$B$1)&" "&IF(C2=0,"",C2&"-"&$C$1)
&" "&IF(D2=0,"",D2&"-"&$D$1)&" "&IF(E2=0,"",E2&"-"&$E$1))," ",", ")

I stopped at column E, but you could extend the pattern pretty far. (There is a
limit of 1024 characters in a formula (when measured in R1C1 reference style.)

(if you do have spaces in your headers, you can cheat and use a non-breaking
space in that label.

For example:
Red Grapes
Instead of typing a spacebar after the d in Red, use alt-0160 (hold the alt key
while typing 0160 on the numeric keypad).



coddave wrote:
>
> Hi,
>
> I have an order sheet where along the top I have the items and down the side
> I have the clients. Currently I enter quantities below the items at the top
> of the page to line up with each client rows. What I would like to do is
> have another column at the end of the sheet, which would summarize the order
> for me. E.g. If along the top I have apples, oranges, bananas, and down the
> side I have client A,B,C; I would like to have a summary column that would
> say 3-apples, 2-bananas. Any column with a 0 would not show up.
>
> I appreciate any help offered,
>
> Dave

--

Dave Peterson

Thank You Dave, it worked perfectly.

Sincerely,

Dave

"Dave Peterson" wrote:

> If all the headers don't have any spaces, you could use something like:
>
> =SUBSTITUTE(TRIM(IF(B2=0,"",B2&"-"&$B$1)&" "&IF(C2=0,"",C2&"-"&$C$1)
> &" "&IF(D2=0,"",D2&"-"&$D$1)&" "&IF(E2=0,"",E2&"-"&$E$1))," ",", ")
>
> I stopped at column E, but you could extend the pattern pretty far. (There is a
> limit of 1024 characters in a formula (when measured in R1C1 reference style.)
>
> (if you do have spaces in your headers, you can cheat and use a non-breaking
> space in that label.
>
> For example:
> Red Grapes
> Instead of typing a spacebar after the d in Red, use alt-0160 (hold the alt key
> while typing 0160 on the numeric keypad).
>
>
>
> coddave wrote:
> >
> > Hi,
> >
> > I have an order sheet where along the top I have the items and down the side
> > I have the clients. Currently I enter quantities below the items at the top
> > of the page to line up with each client rows. What I would like to do is
> > have another column at the end of the sheet, which would summarize the order
> > for me. E.g. If along the top I have apples, oranges, bananas, and down the
> > side I have client A,B,C; I would like to have a summary column that would
> > say 3-apples, 2-bananas. Any column with a 0 would not show up.
> >
> > I appreciate any help offered,
> >
> > Dave
>
> --
>
> Dave Peterson
>