Sort list into Groups by Cell (Non repeating Columns)

[ccn2623]

HI

I have been trying to group data, not like grouping under the Data Tab or Analyse, but take a list and group in this case by the 1st and 2nd column, but not keep repeating the value down the list values.

Included are 2 images, 1st is the data, 2nd is how i would like the output to look.

Essentially grouping on 1st and 2nd column and listing the 3rd column

Many thanks for any advice

Chris

 

[kevin9999]

Try this on a copy of your data.

Option Explicit
Sub Group_Data()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<<< change to actual sheet name
    
    Dim Str1 As String, Str2 As String
    Str1 = ws.Range("A1")
    Str2 = ws.Range("B1")
    
    Dim i As Long, Arr
    Arr = ws.Range("A1").CurrentRegion
    
    For i = 2 To UBound(Arr, 1)
        If Arr(i, 1) = Str1 Then Arr(i, 1) = "" Else Str1 = Arr(i, 1)
        If Arr(i, 2) = Str2 Then Arr(i, 2) = "" Else Str2 = Arr(i, 2)
    Next i
    
    ws.Range("A1").Resize(UBound(Arr, 1), UBound(Arr, 2)).Value = Arr
End Sub

Before

ccn2623.xlsm
	A	B	C
1	Entity Name	Process Name	Resource Item Name
2	Commercial - London Commercial - London	London Market	CQE
3	Commercial - London Commercial - London	London Market	AXCO
4	Commercial - London Commercial - London	London Market	KITE
5	Commercial - London Commercial - London	London Market	RSAred
6	Commercial - London Commercial - London	London Market	Scribe
7	Commercial - London Commercial - London	London Market	CSS
8	Commercial - London Commercial - London	London Market	DART
9	Commercial - London Commercial - London	London Market	GeoRisk Portfolio
10	Commercial - London Commercial - London	London Market	Internet
11	Commercial - London Commercial - London	London Market	MS DYNAMICS
12	Commercial - London Commercial - London	London Market	MS Outlook
13	Commercial - London Commercial - London	London Market	Galaxy
14	Commercial - London Commercial - London	London Market	Sharepoint
15	Commercial - London Commercial - London	London Market	WIP Pricing Tool
16	Commercial - London Commercial - London	Global Network	AXCO
17	Commercial - London Commercial - London	Global Network	Information Platform
18	Commercial - London Commercial - London	Global Network	Lexis Nexis
19	Commercial - London Commercial - London	Global Network	MS Outlook
20	Commercial - London Commercial - London	Global Network	CQE
21	Commercial - London Commercial - London	Global Network	Galaxy
22	Commercial - London Commercial - London	Global Network	GeoRisk Portfolio
23	Commercial - London Commercial - London	Global Network	Internet
24	Commercial - London Commercial - London	Global Network	KITE
25	Commercial - London Commercial - London	Global Network	Sharepoint
26	Commercial - Motability Operations - Liverpool	Customer Service Telephony	CUE
27	Commercial - Motability Operations - Liverpool	Customer Service Telephony	IEX WORKFORCE MANAGEMENT
28	Commercial - Motability Operations - Liverpool	Customer Service Telephony	Internet
29	Commercial - Motability Operations - Liverpool	Customer Service Telephony	MMR VERINT
30	Commercial - Motability Operations - Liverpool	Customer Service Telephony	Respond
31	Commercial - Technical Claims - Liverpool	Third Party Recoveries	CCS
32	Commercial - Technical Claims - Liverpool	Third Party Recoveries	Internet
33	Commercial - Technical Claims - Liverpool	Third Party Recoveries	Respond
34
Sheet1

After

ccn2623.xlsm
	A	B	C
1	Entity Name	Process Name	Resource Item Name
2	Commercial - London Commercial - London	London Market	CQE
3			AXCO
4			KITE
5			RSAred
6			Scribe
7			CSS
8			DART
9			GeoRisk Portfolio
10			Internet
11			MS DYNAMICS
12			MS Outlook
13			Galaxy
14			Sharepoint
15			WIP Pricing Tool
16		Global Network	AXCO
17			Information Platform
18			Lexis Nexis
19			MS Outlook
20			CQE
21			Galaxy
22			GeoRisk Portfolio
23			Internet
24			KITE
25			Sharepoint
26	Commercial - Motability Operations - Liverpool	Customer Service Telephony	CUE
27			IEX WORKFORCE MANAGEMENT
28			Internet
29			MMR VERINT
30			Respond
31	Commercial - Technical Claims - Liverpool	Third Party Recoveries	CCS
32			Internet
33			Respond
34
Sheet1

 

[kevin9999]

Probably safer with a Sort done first...

Option Explicit
Sub Group_Data_2()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<<< change to actual sheet name
    
    ws.Columns("A:C").Sort Key1:=ws.Range("A1"), order1:=xlAscending, Key2:=ws.Range("B1"), order2:=xlAscending, Header:=xlYes
    
    Dim Str1 As String, Str2 As String
    Str1 = ws.Range("A1")
    Str2 = ws.Range("B1")
    
    Dim i As Long, Arr
    Arr = ws.Range("A1").CurrentRegion
    
    For i = 2 To UBound(Arr, 1)
        If Arr(i, 1) = Str1 Then Arr(i, 1) = "" Else Str1 = Arr(i, 1)
        If Arr(i, 2) = Str2 Then Arr(i, 2) = "" Else Str2 = Arr(i, 2)
    Next i
    
    ws.Range("A1").Resize(UBound(Arr, 1), UBound(Arr, 2)).Value = Arr
End Sub

 

[ccn2623]

Probably safer with a Sort done first...

Option Explicit
Sub Group_Data_2()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<<< change to actual sheet name
   
    ws.Columns("A:C").Sort Key1:=ws.Range("A1"), order1:=xlAscending, Key2:=ws.Range("B1"), order2:=xlAscending, Header:=xlYes
   
    Dim Str1 As String, Str2 As String
    Str1 = ws.Range("A1")
    Str2 = ws.Range("B1")
   
    Dim i As Long, Arr
    Arr = ws.Range("A1").CurrentRegion
   
    For i = 2 To UBound(Arr, 1)
        If Arr(i, 1) = Str1 Then Arr(i, 1) = "" Else Str1 = Arr(i, 1)
        If Arr(i, 2) = Str2 Then Arr(i, 2) = "" Else Str2 = Arr(i, 2)
    Next i
   
    ws.Range("A1").Resize(UBound(Arr, 1), UBound(Arr, 2)).Value = Arr
End Sub

Thank you so much, worked a treat ! Most grateful

 

[kevin9999]

Thank you so much, worked a treat ! Most grateful

Happy to help, and welcome to the forum

 

[ccn2623]

Happy to help, and welcome to the forum

Oooo Advancing on from your solution, I was wondering if the Data related to the values in column "A1" could be written into separate Tabs?
I have now got to email the persons in Col "F" & "G" the list of what they are responsible for, so thought it may be better to write to individual tabs or perhaps there is a better solution/idea?

 

[kevin9999]

Oooo Advancing on from your solution, I was wondering if the Data related to the values in column "A1" could be written into separate Tabs?
I have now got to email the persons in Col "F" & "G" the list of what they are responsible for, so thought it may be better to write to individual tabs or perhaps there is a better solution/idea?

It's certainly possible to create new sheets for each group in column A, however that is considerably more (and different) from your original post, and therefore warrants a new question. (And don't forget to mark answers that do what you asked for as a 'Solution' )
Create a new question and I'll watch out for it - although I won't get to it for a while, by which time another volunteer on the forum could have solved it for you.

 

[ccn2623]

It's certainly possible to create new sheets for each group in column A, however that is considerably more (and different) from your original post, and therefore warrants a new question. (And don't forget to mark answers that do what you asked for as a 'Solution' )
Create a new question and I'll watch out for it - although I won't get to it for a while, by which time another volunteer on the forum could have solved it for you.

Ah got it, found the solution button (Done)
Ill create a new question. Thank you again for your help