Number of instance

I want following result in excel in column B

Sr    AA    BBB
1      a      1,2,3,6,7   <-------------Wherever "a" is reflected
2      a      1,2,3,6,7
3      a      1,2,3,6,7
4      b      4,5
5      b      4,5
6      a      1,2,3,6,7
7      a      1,2,3,6,7
8      c      8,9,10
9      c      8,9,10
10      c      8,9,10
TEJAS NIMBALKARAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
You may use the following User Defined Function to get the desired output.
Function CustomConcatenate(ByVal Rng As Range, ByVal LookupStr As String) As String
Dim x
Dim Str As String
Dim i As Long
x = Rng.Value
For i = 1 To UBound(x, 1)
    If LCase(x(i, 2)) = LCase(LookupStr) Then
        If Str = "" Then
            Str = x(i, 1)
        Else
            Str = Str & ", " & x(i, 1)
        End If
    End If
Next i
CustomConcatenate = Str
End Function

Open in new window

Then use this function as below...

In C2
=CustomConcatenate($A$2:$B$11,B2)

Open in new window

and then copy it down.
CustomConcatenate.xlsm---Microsoft-E.jpg
Refer to the attached for details.
CustomConcatenate.xlsm
1
 
TEJAS NIMBALKARAuthor Commented:
Thanks
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Tejas!
Please take a minute to accept the answer to mark your question as Solved.
0
 
aikimarkCommented:
How many rows and how many distinct values are you processing?
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The chosen answer resolved the original question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.