Finding a repeated value in Col A, then merging all values in next cell (Col B) into one cell

[emedley]

Please could someone provide a formula to do the following?

I have a table with 3 columns A, B, C
Col A is an ITEM number
Col B is the EDITION number of the ITEM
Col C is where I manually enter the ITEM number I am interested in
Col D is where the final result needs to be displayed

Example: I enter the ITEM number of interest to me in Col C, in this case "p5".
The formula I need (to be in Col D) finds all instances of ITEM "p5" in Col A, then returns all of the ITEM's EDITION values (Col B) into one cell, separating each value with a hash (#)

Conditions:
ITEM numbers (Col A) are not sorted and can be repeated at random intervals.
[TABLE="width: 479"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]COL A
ITEM[/TD]
[TD]COL B
ITEM'S EDITION[/TD]
[TD]COL C
CHOSEN ITEM[/TD]
[TD]COL D
ITEM's EDITIONS[/TD]
[/TR]
[TR]
[TD]p1[/TD]
[TD]1a[/TD]
[TD]p5[/TD]
[TD]j#1#f3[/TD]
[/TR]
[TR]
[TD]p2[/TD]
[TD]4r[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p5[/TD]
[TD]j[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p5[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p3[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p10[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p4[/TD]
[TD]3c[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p5[/TD]
[TD]f3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mohammad2060]

use this UDF:

Function vlookupall(sSearch As String, rRange As Range, _
Optional lLookupCol As Long, Optional sDel As String) As String
Dim i As Long, sTemp As String
If lLookupCol > rRange.Columns.Count Or sSearch = "" Or _
(lLookupCol < 0 And rRange.Columns.Count > 1) Then
vlookupall = CVErr(xlErrValue)
Exit Function
End If
vlookupall = ""
For i = 1 To rRange.Rows.Count
If rRange(i, 1).Text = sSearch Then
If lLookupCol >= 0 Then
vlookupall = vlookupall & sTemp & rRange(i, lLookupCol).Text
Else
vlookupall = vlookupall & sTemp & rRange(i).Offset(0, lLookupCol).Text
End If
sTemp = sDel
End If
Next i
End Function

 

[emedley]

I tried running it but got an error message:
"Compile error:
Expected End Sub"

 

[mohammad2060]

http://s9.picofile.com/d/8326712334/91514d8c-4e0a-4db4-a3be-0bcbe6c09f4c/Vlookup_All.xlsm

 

[emedley]

Found a formula-based solution!

(Assume Column headers are Row 1 and data starts in row 2)

Array formula (press CTRL/Shift/Enter) to enter in Col D is:

=TEXTJOIN("#",1,REPT($B$2:$B$11,1*($A$2:$A$11=C$2)))

 

[emedley]

Thanks, but it's in Arabic script, which I cannot read.

Good news is, I've since found a solution using an (array) formula, which I have posted.

 

[Peter_SSs]

You could also use this slightly simpler array formula
=TEXTJOIN("#",1,IF(A2:A11=C2,B2:B11,""))

 

[emedley]

Ah yes, a succinct solution. Thank you!

 

[Peter_SSs]

Ah yes, a succinct solution. Thank you!

You're welcome.