Combining data from multiple rows into one row

[jodeyes]

I am trying to combine data for users who have multiple rows into one single row. All other data is the same except for one column, so I want that one column to reflect all of the data in the first column for that user, separated by commas.

Current Sample

Login_ID	Sys1_ID	Emp_No	Market	Name_of_User	Store_ID	ProfileName	Job Title	Coordinator
A123456	12635004	12635004	050	Barbara Jones	5001	ASSOC2	Retail Sales Associate	Mary Smith
B123456	12632959	12632959	050	Elissa Donnabella	5004	ASSOC2	Retail Sales Associate	Mary Smith
C123456	12597915	12597915	050	Marcia Brady	5003	ASSTMNGR	Retail Sales Leader	Mary Smith
D123456	12612768	12612768	050	Brad Pitt	5001	ASSOC2	Retail Sales Associate	Mary Smith
E123456	11345	12533788	050	Jennifer Aniston	5007	ASSTMNGR	Retail Sales Leader	Mary Smith
F123456	4389	12531662	050	Bob Jones	5000	REGMNGR	Sr Analyst, Business Analysis	Mary Smith
F123456	4389	12531662	050	Bob Jones	5001	REGMNGR	Sr Analyst, Business Analysis	Mary Smith
F123456	4389	12531662	050	Bob Jones	5002	REGMNGR	Sr Analyst, Business Analysis	Mary Smith
F123456	4389	12531662	050	Bob Jones	5003	REGMNGR	Sr Analyst, Business Analysis	Mary Smith
F123456	4389	12531662	050	Bob Jones	5004	REGMNGR	Sr Analyst, Business Analysis	Mary Smith
F123456	4389	12531662	050	Bob Jones	5005	REGMNGR	Sr Analyst, Business Analysis	Mary Smith
F123456	4389	12531662	050	Bob Jones	5006	REGMNGR	Sr Analyst, Business Analysis	Mary Smith
F123456	4389	12531662	050	Bob Jones	5007	REGMNGR	Sr Analyst, Business Analysis	Mary Smith
G123456	12645049	12645049	050	Avery Shriber	5002	ASSOC2	Retail Sales Associate	Mary Smith
H123456	12216	12533491	050	Louise Penny	5002	INVASSOC	Inventory Sales Associate	Mary Smith
I123456	12633078	12633078	050	Karen Smith	5004	ASSOC2	Retail Sales Associate	Mary Smith
I123456	12633078	12633078	050	Karen Smith	6006	ASSOC2	Retail Sales Associate	Mary Smith
J123456	11738	12533406	050	Jose Rodriguez	5006	ASSTMNGR	Retail Sales Leader	Mary Smith
K123456	12637433	12637433	050	Eleanor Page	5002	ASSOC2	Retail Sales Associate	Mary Smith

<tbody>
</tbody>

What I'm trying to achieve

Login_ID	Sys1_ID	Emp_No	Market	Name_of_User	Store_ID	ProfileName	Job Title	Coordinator
A123456	12635004	12635004	050	Barbara Jones	5001	ASSOC2	Retail Sales Associate	Mary Smith
B123456	12632959	12632959	050	Elissa Donnabella	5004	ASSOC2	Retail Sales Associate	Mary Smith
C123456	12597915	12597915	050	Marcia Brady	5003	ASSTMNGR	Retail Sales Leader	Mary Smith
D123456	12612768	12612768	050	Brad Pitt	5001	ASSOC2	Retail Sales Associate	Mary Smith
E123456	11345	12533788	050	Jennifer Aniston	5007	ASSTMNGR	Retail Sales Leader	Mary Smith
F123456	4389	12531662	050	Bob Jones	5000, 5001, 5002, 5003, 5004, 5005, 5006, 5007	REGMNGR	Sr Analyst, Business Analysis	Mary Smith
G123456	12645049	12645049	050	Avery Shriber	5002	ASSOC2	Retail Sales Associate	Mary Smith
H123456	12216	12533491	050	Louise Penny	5002	INVASSOC	Inventory Sales Associate	Mary Smith
I123456	12633078	12633078	050	Karen Smith	5004, 6006	ASSOC2	Retail Sales Associate	Mary Smith
J123456	11738	12533406	050	Jose Rodriguez	5006	ASSTMNGR	Retail Sales Leader	Mary Smith
K123456	12637433	12637433	050	Eleanor Page	5002	ASSOC2	Retail Sales Associate	Mary Smith

<tbody>
</tbody>

Thank you!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Amalgamatedata()
   Dim Cl As Range
   Dim Rng As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("E2", Range("E" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Cl.Offset(, 1)
         Else
            .Item(Cl.Value).Value = .Item(Cl.Value) & ", " & Cl.Offset(, 1).Value
            If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub

 

[jodeyes]

That worked great, Fluff! Thanks SO much!!!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Navi_G]

Hi & welcome to MrExcel.
How about

Sub Amalgamatedata()
   Dim Cl As Range
   Dim Rng As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("E2", Range("E" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Cl.Offset(, 1)
         Else
            .Item(Cl.Value).Value = .Item(Cl.Value) & ", " & Cl.Offset(, 1).Value
            If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub

Its Amazing Vba code i first time try thanks.

Thanks
Navi_G