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