Concatenate

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 manual procedure (ie. copy and paste, etc) is not an option.

Thank you

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.

"Morrigan" wrote:

>
> 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 manual
> procedure (ie. copy and paste, etc) is not an option.
>
> Thank you
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=544864
>
>

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

Originally Posted by 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.

"Morrigan" wrote:

>
> 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 manual
> procedure (ie. copy and paste, etc) is not an option.
>
> Thank you
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=544864
>
>

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.

"Morrigan" wrote:

>
> 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
>
>
> Miguel Zapico Wrote:
> > 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.
> >
> > "Morrigan" wrote:
> >
> > >
> > > 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 manual
> > > procedure (ie. copy and paste, etc) is not an option.
> > >
> > > Thank you
> > >
> > >
> > > --
> > > Morrigan
> > >
> > ------------------------------------------------------------------------
> > > Morrigan's Profile:
> > http://www.excelforum.com/member.php...fo&userid=7094
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=544864
> > >
> > >
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=544864
>
>

Ahh~inserting a module is what I didn't do. Super, thanks again!

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.

Originally Posted by 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.

"Morrigan" wrote:

>
> 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
>
>
> Miguel Zapico Wrote:
> > 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.
> >
> > "Morrigan" wrote:
> >
> > >
> > > 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 manual
> > > procedure (ie. copy and paste, etc) is not an option.
> > >
> > > Thank you
> > >
> > >
> > > --
> > > Morrigan
> > >
> > ------------------------------------------------------------------------
> > > Morrigan's Profile:
> > http://www.excelforum.com/member.php...fo&userid=7094
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=544864
> > >
> > >
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=544864
>
>