Return multiple values concatenated??

[A. Hoeben]

Hi,
I'm a very lightweight user of Excel and until now have been able to do everything I need to do using the help files and whatnot. Now I need to manipulate my data in a way that is not simple. I have to create a one-to-many relationship and return the multiple values concatenated into a single field.

Example (I've got 3000+ rows of this and only 347 unique server names):

Object_Path	MemberName
Server1	AdminGroup1
Server1	AdminGroup2
Server1	AdminGroup3
Server1	PowerUsers1
Server1	PowerUsers2
Server2	AdminGroup1
Server2	AdminGroup3
Server2	PowerUsers1
Server3	AdminGroup1
Server3	AdminGroup2
Server4	AdminGroup1
Server4	PowerUsers1

<tbody>
</tbody>

Desired outcome:

Object_Path	MemberName
Server1	AdminGroup1, AdminGroup2, AdminGroup3, PowerUser1, PowerUser2
Server2	AdminGroup1, AdminGroup3, PowerUsers1
Server3	AdminGroup1, AdminGroup2
Server4	AdminGroup1, PowerUsers1

<tbody>
</tbody>

I don't understand scripting or array formulas, but I can learn! I just need to be pointed in the right direction. I hope I've made this clear - it's pretty simple and I could do it in notepad if it were just a few rows, but 3000 is too daunting!

Thanks in advance for reading and helping!
--Alyssa

 

[Tinbendr]

Welcome to the board!

Sub MergeGroups()
Dim WS As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Ctr As Long
Dim Temp$
    'Result Row
    Ctr = 1

    Set WS = ActiveWorkbook.ActiveSheet
    With WS
        'Last row of column A with data.
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        'Loop through each item in Col A.
        For Each Rng In Range("A2:A" & LastRow)
            'Compare to next item down.  If equal build user string.
            If Rng.Offset(1, 0).Value = Rng.Value Then
                Temp$ = Temp$ & Rng.Offset(0, 1).Value & ", "
            Else
                'Servers no longer match.
                Ctr = Ctr + 1
                'Add last match
                Temp$ = Temp$ & Rng.Offset(0, 1).Value
                'Post to Col C & D.
                .Range("C" & Ctr).Value = Rng.Value
                .Range("D" & Ctr).Value = Temp$
                'Clear Temp$
                Temp$ = ""
            End If
        Next
    End With
End Sub

 

[A. Hoeben]

Wow, thank you so much! That worked perefctly!

 

[lostitagain]

Try this

Sub SimplifyList()
Dim i, j, FinalRowFrom, FinalRowTo As Long
'identify the last row with data in it.
FinalRowFrom = Range("A" & Rows.Count).End(xlUp).Row
'Selects the data, copies to a new column and removes duplicates
Range("A2:A" & FinalRowFrom).Select
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$D$2:$D$" & FinalRowFrom).RemoveDuplicates Columns:=1, Header:=xlNo
'Loops through the original list
For i = 2 To FinalRowFrom
   FinalRowTo = Range("D" & Rows.Count).End(xlUp).Row
   'Loops through new list
   For j = 2 To FinalRowTo
      If Range("A" & i).Value = Range("D" & j).Value Then              'if the object path matches
         Range("E" & j).Value = Range("E" & j).Value & Range("B" & i).Value & ", "      'add the member name to the list
      End If
   Next j
Next i
End Sub

 

[Robert Mika]

Another way using UDF:
Excel 2010

	A	B	C	D	E
1	Object_Path	MemberName
2	Server1	AdminGroup1		Server1	AdminGroup1,AdminGroup2,AdminGroup3,PowerUsers1,PowerUsers2
3	Server1	AdminGroup2		Server2	AdminGroup1,AdminGroup3,PowerUsers1
4	Server1	AdminGroup3		Server3	AdminGroup1,AdminGroup2
5	Server1	PowerUsers1		Server4	AdminGroup1,PowerUsers1
6	Server1	PowerUsers2
7	Server2	AdminGroup1
8	Server2	AdminGroup3
9	Server2	PowerUsers1
10	Server3	AdminGroup1
11	Server3	AdminGroup2
12	Server4	AdminGroup1
13	Server4	PowerUsers1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula E2 {=SUBSTITUTE(AConcat(IF(A2:A13=D2,","&B2:B13,"")),",","",1)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself [COLOR=#00007F]Function[/COLOR] AConcat(a [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Variant[/COLOR], [COLOR=#00007F]Optional[/COLOR] Sep [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]String[/COLOR] = "") [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]String[/COLOR] [COLOR=#007F00]'By Harlan Grove, March 2002[/COLOR] [COLOR=#00007F]Dim[/COLOR] Y [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Variant[/COLOR] [COLOR=#00007F]If[/COLOR] [COLOR=#00007F]TypeOf[/COLOR] a [COLOR=#00007F]Is[/COLOR] Range [COLOR=#00007F]Then[/COLOR] [COLOR=#00007F]For[/COLOR] [COLOR=#00007F]Each[/COLOR] Y [COLOR=#00007F]In[/COLOR] a.Cells AConcat = AConcat & Y.Value & Sep [COLOR=#00007F]Next[/COLOR] Y [COLOR=#00007F]ElseIf[/COLOR] IsArray(a) [COLOR=#00007F]Then[/COLOR] [COLOR=#00007F]For[/COLOR] [COLOR=#00007F]Each[/COLOR] Y [COLOR=#00007F]In[/COLOR] a AConcat = AConcat & Y & Sep [COLOR=#00007F]Next[/COLOR] Y [COLOR=#00007F]Else[/COLOR] AConcat = AConcat & a & Sep [COLOR=#00007F]End[/COLOR] [COLOR=#00007F]If[/COLOR] AConcat = Left(AConcat, Len(AConcat) - Len(Sep)) [COLOR=#00007F]End[/COLOR] [COLOR=#00007F]Function[/COLOR] [/TD] [/TR] </tbody>[/TABLE]

<tbody>

 

[A. Hoeben]

Thanks everyone for your help! And of course, Murphy's Law would dictate that as soon as I get this spreadsheet put together and ready to deliver, management tells me it's no longer needed. Thanks anyway - I learned a lot and ultimately that's what it's all about!

 

[Sazzle14]

Hi I used your formula in trying to solve a puzzle I have, and it has got really close to what I need. It presented the results in col 1 and 2 below (C and D in your formula). However, What I need is to get the results separately i.e. Results for "7" in one cell in the spreadsheet and Results for "8" in another and the same with 9. So I need the formula to specify what it is looking for in column "BoX" but deliver the results in the same way it did for Column 1 and 2. I.e. it brings back multiple values with either a "," to separate or if possible a soft return {alt & Enter}
I know I am asking a lot but would so appreciate your help, I have tried Index and other VBA solutions and not having much luck.

Box	Name	Column1	Column2
7	Corel	7	Corel , Steph , Jen , Amanda
7	Steph	8	Carol, Amer, Vicky, Charlotte, David
7	Jen	9	Kam, Gwen , Raj, David, Mark , Emma
7	Amanda
8	Carol
8	Amer
8	Vicky
8	Charlotte
8	David
9	Kam
9	Gwen
9	Raj
9	David
9	Mark
9	Emma

<tbody>
</tbody><colgroup><col span="3"><col></colgroup>

Welcome to the board!

Sub MergeGroups()
Dim WS As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Ctr As Long
Dim Temp$
    'Result Row
    Ctr = 1

    Set WS = ActiveWorkbook.ActiveSheet
    With WS
        'Last row of column A with data.
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        'Loop through each item in Col A.
        For Each Rng In Range("A2:A" & LastRow)
            'Compare to next item down.  If equal build user string.
            If Rng.Offset(1, 0).Value = Rng.Value Then
                Temp$ = Temp$ & Rng.Offset(0, 1).Value & ", "
            Else
                'Servers no longer match.
                Ctr = Ctr + 1
                'Add last match
                Temp$ = Temp$ & Rng.Offset(0, 1).Value
                'Post to Col C & D.
                .Range("C" & Ctr).Value = Rng.Value
                .Range("D" & Ctr).Value = Temp$
                'Clear Temp$
                Temp$ = ""
            End If
        Next
    End With
End Sub

 

[jtakw]

Hi,

This is almost a 6 year old thread, you might want to start a New Thread, with a descriptive Subject Title and explain clearly what you need and what you've tried in your Post.