ExcelBanter - View Single Post

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.

Miguel Zapico Wrote:
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

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=544864