Forum Home
Press F1
Thread ID: 38456	2003-10-07 19:41:00	Excel function	appanna (3735)	Press F1

Post ID	Timestamp	Content	User
181173	2003-10-07 19:41:00	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	appanna (3735)
181174	2003-10-08 00:09:00	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	odyssey (4613)
181175	2003-10-08 01:07:00	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??	appanna (3735)
181176	2003-10-08 01:27:00	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	odyssey (4613)
181177	2003-10-08 02:00:00	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.	appanna (3735)
181178	2003-10-11 21:06:00	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	Russell D (18)
1