CB

Try this user defined function

Function OtherItems(ThisRw As Range, AllRws As Range) As String

Dim cell As Range
Dim OthLst() As String
Dim i As Long, j As Long
Dim Temp As String
Dim ItmLst As String

i = 1

If Application.CountIf(AllRws.Columns(2), ThisRw.Value) = 1 Then
ItmLst = "Sole item"
Else

'Fill array by box
For Each cell In AllRws.Columns(2).Cells
If cell.Row < ThisRw.Row Then
If cell.Value = ThisRw.Value Then
ReDim Preserve OthLst(1 To i)
OthLst(i) = cell.Offset(0, -1).Value
i = i + 1
End If
End If
Next cell

'Sort array
For i = LBound(OthLst) To UBound(OthLst) - 1
For j = i + 1 To UBound(OthLst)
If OthLst(i) OthLst(j) Then
Temp = OthLst(j)
OthLst(j) = OthLst(i)
OthLst(i) = Temp
End If
Next j
Next i

For i = LBound(OthLst) To UBound(OthLst)
ItmLst = ItmLst & OthLst(i) & ","
Next i

ItmLst = Left(ItmLst, Len(ItmLst) - 1)
End If

OtherItems = ItmLst

End Function

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"cornishbloke " wrote in
message ...
Thanks for the suggestions,

although Pivot tables do provide the same information, I specifically
wanted to show this information on a row-by-row basis next to the items
(as shown in my example).

One of the problems is that I don't know in advance which cells will be
'concantenated', the other is that the number of cells concantenated
will change depending upon how many cells meet the criteria.

Can anyone suggest a solution to the example I provided?


---
Message posted from http://www.ExcelForum.com/