PDA

View Full Version : Excel function



appanna
08-10-2003, 08:41 AM
Hi
I have a small problem ineed help with. In Col A I have the names of the shops and in Col B the names of persons. There are more than one persons to a shop so the name of shop is repeated. What I want is all the persons in a shop concatenated in one cell. To illustrate

Shop Person
x 1
x 2
x 3
y 4
y 5

What I want is

x 1,2,3
y 4,5

Can somebody help please
Thanks

odyssey
08-10-2003, 01:09 PM
To perform the function you have described, I think the best way is probably to use a vb script. I have created a sample script below for you.

Couple of things to note about the script are:
1) the variable array arrData contains the unique list of shopnames (if you have a unique listing of the shopnames in the spreadsheet you may want to substitute the array for a named range!)
2) I have used three named ranges, the first one is "Shops" (ie Col A in your example), "Names" (ie Col B in your example), and "Output" (e.g. Col C). To define a named range in Excel, select the cells of interest (e.g. A1:A5) and then go to Insert -> Name -> Define and type in the name of the range (e.g. "Shops", but without the quotes!).

Hope this helps...

Public Sub MatchShopToNames()

Dim intCount1 As Integer
Dim intCount2 As Integer
Dim arrData As Variant
Dim arrStr As String

'Array of shop names
arrData = Array("x", "y")

'Repeat for as many shops in array
For intCount1 = 0 To UBound(arrData)
arrStr = arrData(intCount1) & " "
For intCount2 = 1 To Range("Names").Count
If Range("Shops").Cells(intCount2) = arrData(intCount1) Then
arrStr = arrStr & Range("Names").Cells(intCount2) & ", "
End If
Next
If Right(arrStr, 2) = ", " Then arrStr = Left(arrStr, Len(arrStr) - 2)
Range("Output").Cells(intCount1 + 1) = arrStr
Next

End Sub

appanna
08-10-2003, 02:07 PM
Thanks Odyssey ... I copied the vb and inserted the ranges but it doesnt run. For Output whats the range? I've selected the same number of rows as for the other two but a different column. For arrdata I have a unique list of shops which I named "outlet" and for the cola I named "shops"; the names in col B I put in "Names. I copied your vb as under

Public Sub MatchShopToNames()
Dim intCount1 As Integer
Dim intCount2 As Integer
Dim arrData As Variant
Dim arrStr As String

'Array of shop names
arrData = Array("outlet")

'Repeat for as many shops in array
For intCount1 = 0 To UBound(arrData)
arrStr = arrData(intCount1) & " "
For intCount2 = 1 To Range("Names").Count
If Range("shops").Cells(intCount2) = arrData(intCount1) Then
arrStr = arrStr & Range("Names").Cells(intCount2) & ", "
End If
Next
If Right(arrStr, 2) = ", " Then arrStr = Left(arrStr, Len(arrStr) - 2)
Range("Output").Cells(intCount1 + 1) = arrStr
Next
End Sub

Is there anything wrong here??

odyssey
08-10-2003, 02:27 PM
Yip, the code needs to be updated to take into account the new range outlet... try this. If you still have problems post your email address and I will send the workbook to you.

Public Sub MatchShopToNames()

Dim intCount1 As Integer
Dim intCount2 As Integer
Dim arrStr As String

For intCount1 = 1 To Range("Outlet").Count
arrStr = Range("Outlet").Cells(intCount1) & " "
For intCount2 = 1 To Range("Names").Count
If Range("Shops").Cells(intCount2) = Range("Outlet").Cells(intCount1) Then
arrStr = arrStr & Range("Names").Cells(intCount2) & ", "
End If
Next
If Right(arrStr, 2) = ", " Then arrStr = Left(arrStr, Len(arrStr) - 2)
Range("Output").Cells(intCount1) = arrStr
Next

End Sub

appanna
08-10-2003, 03:00 PM
This works but there is a problem. The outlet is also included in the concetatnation. I wanted in the adjoining cell of all the names in the shop. Secondly it offsets one row down and excludes the first name in the shop but includes the first name in the next shop. Can this be fixed?
My email address is indus_nz@hotmail.com. Maybe I could email it to you. the sheet is a small one with 570 rows.

Russell D
12-10-2003, 10:06 AM
Don't know if you have this fixed yet.

But to make it work, include the header in each range name..
Also,
Copy your outlet range to the column immediately to the left of itself then edit the line of code to the following
arrStr = Range("Outlet").Offset(0, 1).Range("Outlet").Cells(intCount1) & " "

and it works for me.

Nice code Odysee