Concatenate

M

Morrigan

Let’s say I have 2 columns, A and B:

ColA ColB
1101 a
1101
1101 b
1101 12
1103 c
1103 d
1103 e
1111 f
1106 g
1106
1106 h
1106
1106 i


I want to concatenate ColB so it will look like this:

ColA ColB
1101 a,b,12
1103 c,d,e
1111 f
1106 g,h,i


Is there a formula that can do this? Approach involving manua
procedure (ie. copy and paste, etc) is not an option.

Thank yo
 
M

Miguel Zapico

You can use a custom formula like this:
Function ConcatenateByCode(strInput As String)
Dim rngData, rngCode As Range
Dim strResult As String

Application.Volatile
Set rngData = ActiveSheet.Range("B1:B13")
Set rngCode = ActiveSheet.Range("A1:A13")

For Each cell In rngCode.Cells
If cell.Value = strInput Then
If rngData.Cells(cell.Row, 1).Value <> "" Then
strResult = strResult & rngData.Cells(cell.Row, 1).Value & ","
End If
End If
Next

strResult = Left(strResult, Len(strResult) - 1)
ConcatenateByCode = strResult
End Function

Change the ranges A1:A13 and B1:B13 to match your case. Also, if you want
to use it on a different sheet you can hardcode the name of the sheet on
those ranges, or accept it as an input.
To use it, just enter in column B of your desired result:
=ConcatenateByCode(A1)

Hope this helps,
Miguel.
 
M

Morrigan

I am getting "#NAME?", seems like Excel is not recognizing the function
after I pasted the code in VBA. Any idea? (More explanation the
better, I would not even consider myself a beginner in VBA)
Furthermore, I would like to avoid using fixed range; for instance, if
I add a row I would like the function to change the range
automatically.

Btw in "rngData.Cells(cell.Row, 1).Value", what does "Cells(cell.Row,
1)" mean?

Thank you for the input
 
M

Miguel Zapico

The function should be located in a module, you can create one easily from
the Visual Basic editor (ALT+F11 on Excel) and going to the menu
Insert->Module. Paste the code there and it should work.
About the range, the range that is hardcoded is the lookup table. You can
fix it to the greatest value you expect, but that may affect the performance.
There are ways of making the dependant of the region size, but I wanted to
keep the function simple.
The Cells(cell.Row,1) is the cell of the values range where the iteration
is, the value is taken from the B column if the cell in column A has the same
value as the input string.

Miguel.
 
M

Morrigan

I just realized I cannot use fixed range since I have 5 different
tables. Thus, I have 5 different range of cells for rngCode and
rngData. Using fixed range will lead to 5 custom functions, which can
be confusing as time goes by. I was trying to modify the code so that
rngCode and rngData are inputs, but no luck with my lack of VBA
experience. The following is what I tried:

Function ConcatenateByCode(strInput As String, rngCode As Range,
rngData As Range)
Dim strResult As String

Application.Volatile
For Each cell In rngCode.Cells
If cell.Value = strInput Then
If rngData.Cells(cell.Row, 1).Value <> "" Then
strResult = strResult & rngData.Cells(cell.Row, 1).Value & ","
End If
End If
Next

strResult = Left(strResult, Len(strResult) - 1)
ConcatenateByCode = strResult
End Function


Any help is appreciated, thank you.
 
Top