concatenate only non zero values

[NapervilleMike]

So here is what I have:

Substance	quantity
Water	98
Sodium	.5
Potassium	1
Calcium	.5
Magnesium	0

<tbody>
</tbody>

What I need is a formula that will concatenate only the non-zero's and the substance associated with them. For example this out put should say: Water, 98; Sodium .5; Potassium, 1; Calcium .5

If this seems pointless, the reason I need a methodology because I actually have about 50 substances and there are about 30 "quantity" columns and I would like a nice way to automate this and apply it to other similar applications.

Thanks.

 

[NapervilleMike]

Its just spitting out #VALUE! now

 

[Rick Rothstein]

Its just spitting out #VALUE! now

Did you remember to use two arguments instead of one? Either this...

=JoinSubs(A2:A6,B2:B6)

or this (if you plan to drag it across)...

=JoinSubs($A2:$A6,B2:B6)

 

[NapervilleMike]

Sorry, I'm an idiot and you are the best.

I know I say this every time I come here for help, but you all are wonderful and make me look like some genius boy wonder at work,

 

[shashidharga]

Hi, I'm a new member and excel user. I'm also searching for the same solution, but I want the result line by line.

The sample data

A B

2 Apple

4 Orange

7 Banana

8 Grapes

0 Berry

I want the final result in a cell like ; (combined data with carriage return after each data)

2 Apple
4 Orange
7 Banana
8 Grapes

By concatenating only the non zero cells

Thanx in advance.
Shashi

 

[Rick Rothstein]

A B

2 Apple

4 Orange

7 Banana

8 Grapes

0 Berry

I want the final result in a cell like ; (combined data with carriage return after each data)

2 Apple
4 Orange
7 Banana
8 Grapes

By concatenating only the non zero cells

Give this modification to the code I posted earlier in this thread a try...

Function JoinSubs(SubstanceRange As Range, QualityRange As Range) As String
  Dim X As Long, LastRow As Long, Txt As String
  LastRow = Cells(Rows.Count, SubstanceRange.Column).End(xlUp).Row
  JoinSubs = Join(Application.Transpose(Evaluate("IF(" & QualityRange.Address & "=0,""""," & _
             SubstanceRange.Address & "&"", ""&" & QualityRange.Address & ")")), vbLf)
  Do While InStr(JoinSubs, vbLf & vbLf)
    JoinSubs = Replace(JoinSubs, vbLf & vbLf, vbLf)
  Loop
  JoinSubs = Trim(JoinSubs)
  If Right(JoinSubs, 1) = vbLf Then JoinSubs = Left(JoinSubs, Len(JoinSubs) - 1)
End Function

This is the formula you would put in the cell you wanted the concatenation to appear in...

=JoinSubs(B1:B100,A1:A100)

where you would change both 100's to the row number containing your last row of data (a row number bigger than that will also work). Note that you will have to manually turn on word wrapping (Cell Formatting/Alignment Tab) for the line-by-line display to show up correctly.

 

[shashidharga]

Thank you very much Sir. I'l try this code & get back to you.
Shashi

 

[shashidharga]

Sir,
I'm attaching the file and in this file i've explained how i want the result.
Example.xlsm - Speedy Share - upload your files here
shashi

 

[Rick Rothstein]

Sir,
I'm attaching the file and in this file i've explained how i want the result.
Example.xlsm - Speedy Share - upload your files here
shashi

Sorry, but that website wants to install something called "Tiny DM Manager" on my computer in order to download your file and I will not do that. If you want to email the file to me directly, my email address is....

rick DOT news AT verizon DOT net

Please put the title of this thread in the body of your email so I can relate the file back to this thread more easily.

 

[shashidharga]

Sorry, but that website wants to install something called "Tiny DM Manager" on my computer in order to download your file and I will not do that. If you want to email the file to me directly, my email address is....

rick DOT news AT verizon DOT net

Please put the title of this thread in the body of your email so I can relate the file back to this thread more easily.

Sir,
I've emailed the file
Shashi

 

[Rick Rothstein]

Sir,
I've emailed the file

For those who did not want to download the file for the same reason I stated in Message #18, here is roughly what it looked like (the column widths did not end up correct in it, Columns E:H should all be the same width and that width should be wide enough to show cell E15 with 4 rows having a number in front of each row)...

	A	B	C	D	E	F	G	H	I
1			Sl. No.	Work Details	Finance for April 2014	Finance for May 2014	Finance for June 2014	Finance for July 2014
2			1	Work 1	20	40	50	10
3			2	Work 2	34	62	20	13
4			3	Work 3	12	40	10	13
5			4	Work 4	10	3	15	40
6			5	Work 5	50	20	20	25
7			6	Work 6	10	15	10	25
8			7	Work 7	3	20	20	60
9			Color code	Status	April	May	June	August
10				Works Tender Process	2	3	0	2
11				Works Started	2	0	0	2
12				Works Under Progress	2	2	4	3
13				Works Completed	1	2	3	0
14					This following is the Result I want
15					2 Works Tender Process 2 Works Started 2 Works Under Progress 1 Works Completed	3 Works Tender Process 2 Works Under Progress 2 Works Completed	4 Works Under Progress 3 Works Completed	2 Works Tender Process 2 Works Started 3 Works Under Progress
16
17				Note : I will get numbers for the months by counting cells having particular color fill
18

<tbody>
</tbody>

Here is the revised UDF that I came up with for it...

Function ColorCounts(ColorCountRange As Range, StatusRange As Range) As String
  Dim X As Long, LastRow As Long, Txt As String
  LastRow = Cells(Rows.Count, ColorCountRange.Column).End(xlUp).Row
  ColorCounts = Join(Application.Transpose(Evaluate("IF(" & ColorCountRange.Address & "=0,""""," & _
             ColorCountRange.Address & "&"" ""&" & StatusRange.Address & ")")), vbLf)
  Do While InStr(ColorCounts, vbLf & vbLf)
    ColorCounts = Replace(ColorCounts, vbLf & vbLf, vbLf)
  Loop
  If Left(ColorCounts, 1) = vbLf Then ColorCounts = Mid(ColorCounts, 2)
  If Right(ColorCounts, 1) = vbLf Then ColorCounts = Left(ColorCounts, Len(ColorCounts) - 1)
End Function

Once this code is installed in accordance with the directions I gave in Message #5, then put this formula in cell E15 and copy it across to cell H15...

=ColorCounts(E10:E13,$D10:$D13)