Concatenate in a cell the content of a column if a certain condition

[nedy_03]

Hello,

Please help me on this:
- in column A I have a list of suppliers
- in column B the sum I have to pay to them (witch can be "0" too)
- in cell C1 I would need to concatenate (separated by ",") the names of the suppliers with the sum (from B) different from "0"

If it can't be done without a VB code I would attach the file, because the thing happens in different sheets and I don't know much about VB, and I think I won't be able to modify it in order to work :banghead:

Thx,
Nedy

[Bob Phillips]

=A1&IF(B1<>0,","&B1,"")

[moa]

=A1&IF(B1<>0,", $"&FIXED(B1,2),"")

If you want to keep decimal places.

[nedy_03]

I seems I didn't make myself understood :-S ... I've attached a file with some indications ... can u look pls ...

[Bob Phillips]

You certainly didn't.



Sub ConcatenateData()
Dim iLastRow As Long
Dim i As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To iLastRow
If .Cells(i, "B").Value <> 0 Then
.Cells(2, "C").Value = .Cells(2, "C").Value & _
Cells(i, "A").Value & ","
End If
Next i
If Right(.Cells(2, "C").Value, 1) = "," Then
.Cells(2, "C").Value = Left(.Cells(2, "C").Value, Len(.Cells(2, "C").Value) - 1)
End If
End With
End Sub

[nedy_03]

Thx .. is it possible to make it a function so I can put the name in the cell and the pull it doun ??

[nedy_03]

I would need to concatenate all the cell'content from a column if the value is different from null ("") in just one cell separated by ";" ... By ex :
In column A I have :
A1-""
A2-X
A3-""
A4-y
In cell B1 I should get "X;Y"

Is it possible ?? .. Can U help me on that ? ...

[mdmackillop]

OK I misread your data, but I'll leave this here anyway!
For a function try
e.g =ConcData(A2:B12)

Function ConcData(Data As Range)
Dim i As Long, txt As String
Dim Arr()
ReDim Arr(Data.Rows.Count, 2)
Arr = Data.Value
For i = 1 To UBound(Arr)
If Arr(i, 2) <> 0 Then
txt = txt & Arr(i, 1) & ", "
End If
Next
If Right(txt, 2) = ", " Then
txt = Left(txt, Len(txt) - 2)
End If
ConcData = txt
End Function

[mdmackillop]

Enter =ConcData1(A2:A12)
Function ConcData1(Data As Range)
Dim i As Long, txt As String, tmp As String
Dim Arr(), a
Arr = Data.Value
For Each a In Arr
tmp = a
If a <> 0 Then
txt = txt & a & "; "
End If
Next
If Right(txt, 2) = "; " Then
txt = Left(txt, Len(txt) - 2)
End If
ConcData1 = txt
End Function

[Shazam]

Another approach....


Download and install the free add-in Morefunc.xll (http://xcell05.free.fr/), then try...


=SUBSTITUTE(MCONCAT(IF(B2:B5<>0,","&A2:A5,"")),",","",1)

[nedy_03]

It works .. thx

[Bob Phillips]

So you would rather have a heavy XLL loaded rather than a small bit of VBA? I just don't understand that logic.