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