Return multiple values concatenated??

A

A. Hoeben

New Member
Joined
Jul 5, 2012
Messages
5
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):
[TABLE="width: 255"]
<tbody>[TR]
[TD]Object_Path
[/TD]
[TD]MemberName
[/TD]
[/TR]
[TR]
[TD]Server1
[/TD]
[TD]AdminGroup1
[/TD]
[/TR]
[TR]
[TD]Server1
[/TD]
[TD]AdminGroup2
[/TD]
[/TR]
[TR]
[TD]Server1
[/TD]
[TD]AdminGroup3
[/TD]
[/TR]
[TR]
[TD]Server1
[/TD]
[TD]PowerUsers1
[/TD]
[/TR]
[TR]
[TD]Server1
[/TD]
[TD]PowerUsers2
[/TD]
[/TR]
[TR]
[TD]Server2
[/TD]
[TD]AdminGroup1
[/TD]
[/TR]
[TR]
[TD]Server2
[/TD]
[TD]AdminGroup3
[/TD]
[/TR]
[TR]
[TD]Server2
[/TD]
[TD]PowerUsers1
[/TD]
[/TR]
[TR]
[TD]Server3
[/TD]
[TD]AdminGroup1
[/TD]
[/TR]
[TR]
[TD]Server3
[/TD]
[TD]AdminGroup2
[/TD]
[/TR]
[TR]
[TD]Server4
[/TD]
[TD]AdminGroup1
[/TD]
[/TR]
[TR]
[TD]Server4
[/TD]
[TD]PowerUsers1
[/TD]
[/TR]
</tbody>[/TABLE]

Desired outcome:
[TABLE="width: 724"]
<tbody>[TR]
[TD]Object_Path
[/TD]
[TD]MemberName
[/TD]
[/TR]
[TR]
[TD]Server1
[/TD]
[TD]AdminGroup1, AdminGroup2, AdminGroup3, PowerUser1, PowerUser2
[/TD]
[/TR]
[TR]
[TD]Server2
[/TD]
[TD]AdminGroup1, AdminGroup3, PowerUsers1
[/TD]
[/TR]
[TR]
[TD]Server3
[/TD]
[TD]AdminGroup1, AdminGroup2
[/TD]
[/TR]
[TR]
[TD]Server4
[/TD]
[TD]AdminGroup1, PowerUsers1
[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Welcome to the board!

Code: 
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
 
Upvote 0
Try this

Code: 
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
 
Upvote 0
Another way using UDF:
Excel 2010
ABCDE
Object_Path MemberName
Server1 AdminGroup1Server1 AdminGroup1,AdminGroup2,AdminGroup3,PowerUsers1,PowerUsers2
Server1 AdminGroup2Server2 AdminGroup1,AdminGroup3,PowerUsers1
Server1 AdminGroup3Server3 AdminGroup1,AdminGroup2
Server1 PowerUsers1Server4 AdminGroup1,PowerUsers1
Server1 PowerUsers2
Server2 AdminGroup1
Server2 AdminGroup3
Server2 PowerUsers1
Server3 AdminGroup1
Server3 AdminGroup2
Server4 AdminGroup1
Server4 PowerUsers1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=SUBSTITUTE(AConcat(IF(A2:A13=D2,","&B2:B13,"")),",","",1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
Code: 
[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]
 
Upvote 0
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. :mad2: Thanks anyway - I learned a lot and ultimately that's what it's all about!
 
Upvote 0
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.

[TABLE="width: 432"]
<tbody>[TR]
[TD]Box[/TD]
[TD]Name[/TD]
[TD]Column1[/TD]
[TD]Column2[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Corel [/TD]
[TD="align: right"]7[/TD]
[TD]Corel , Steph , Jen , Amanda [/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Steph [/TD]
[TD="align: right"]8[/TD]
[TD]Carol, Amer, Vicky, Charlotte, David[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Jen [/TD]
[TD="align: right"]9[/TD]
[TD]Kam, Gwen , Raj, David, Mark , Emma[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Amanda [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Carol[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Amer[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Vicky[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Charlotte[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]David[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Kam[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Gwen [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Raj[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]David[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Mark [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Emma[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col span="3"><col></colgroup>[/TABLE]



Tinbendr said:
Welcome to the board!

Code: 
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
Click to expand...
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Need macro for copying values from single column and pasting in multiple columns
2
Replies
10
Views
1K
moramramesh
M
R
  • Question Question
UDF to lookup and return multiple values concatenated into one cell without duplicates
2
Replies
11
Views
3K
Fluff
Fluff
B
  • Question Question
Concatenate multiple values from table row into one cell
2
Replies
10
Views
2K
BraveDaveIA
B
O
  • Question Question
Excel 2013, Indirect #ref! Multiple workbooks and a need to use concatenate
Replies
0
Views
630
oregonIT
O
M
  • Question Question
Return value from multiple sheet index match?
Replies
0
Views
661
Melbs_CJW
M

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top