Grouping data

[Rbd340]

Hi,
I'm looking for a way to concatenate vertically based on column A and B and put it in a new tab:
Basically my data looks like this:

Column1 Column2 Column3
A 1 DataXx
A 1 DataAA
A 1 DataBB
A 2 DataXx
A 2 DataCc
A 2 DataAA
B 1 DataCC
B 1 DataAA
B 1 DataFF
B 1 DataWW
B 2 DataRR
B 2 DataXX

and the preferred output would
Column1 Column2 Column3
A 1 DataXx,DataAA,DataBB
B 2 DataXx,DataCc,DataAA
A 1 DataCC,DataAA,DataFF,DataWW
B 2 DataRR,DataXX

Anyone kind enough to help me with this? The excel changes in length and values in column 1 and 2 can have different numbers of rows.
I have to do this a lot and it would help if I have a macro.

Thanks a lot in advance!

 

[Rbd340]

I can not reproduce such errors or understand how that could happen if the active sheet has its data in columns A:C. If you are still having that problem with a particular sheet's data, could you upload that sheet as an actual file to a public file-share site (eg Dropbox) and provide a link here to that file so we could take a look at it and see if the same happens to us?

The code ran fine for me on the sample data that you provided in your previous post.

Hi Peter,

Here is the an example of a file where the issue occurs when I run the script:
https://www.dropbox.com/s/oakikhfw8xi4v9n/Example.xlsx?dl=0

Kind regards,
Ricardo

 

[Peter_SSs]

Here is the an example of a file where the issue occurs when I run the script:

Thanks. Looking back, I should probably have asked you about the size of the data - 500,000+ rows is rather large!

Try this version.

Sub CombineData_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, r As Long
  
  a = Range("A1", Range("C" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 3)
  r = 1
  For i = 2 To UBound(a)
    If a(i, 1) <> a(i - 1, 1) Or a(i, 2) <> a(i - 1, 2) Then
      r = r + 1
      b(r, 1) = a(i, 1): b(r, 2) = a(i, 2): b(r, 3) = a(i, 3)
    Else
      b(r, 3) = a(r, 3) & "," & a(i, 3)
    End If
  Next i
  Application.ScreenUpdating = False
  With Sheets.Add(After:=ActiveSheet)
    With .Range("A1:C1").Resize(r)
      .Value = b
      .Rows(1).Value = Application.Index(a, 1, 0)
      .Columns.AutoFit
    End With
  End With
  Application.ScreenUpdating = True
End Sub

 

[Rbd340]

Thanks. Looking back, I should probably have asked you about the size of the data - 500,000+ rows is rather large!

Try this version.
[/code]

Maybe I should have told you that as well
Unfortunately the formula doesn't quite work yet, it does set the headers correctly now and makes a third column but it doesn't put all accounts in the third column.

For Example, SubscriptionID 19, UserID 38216643 shows only 2 accounts, where I would expect 43 accounts. Looking at the files his output is the last account of the user above him and his own last account.

 

[Peter_SSs]

I did have a typo in my last code, it should have been

    Else
      b(r, 3) = b(r, 3) & "," & a(i, 3)
    End If

However, that will not solve your problem.

I believe that you are running foul of one (or more) of Excel's limits. For example, there is a limit of how many characters a cell can hold (32,767). For one example in your sample data (A=11563, B =34671499) there are over 2,200 rows. The concatenation of the column C values for these rows would produce a string of about 80,000 characters, more than double what a cell will hold! I believe there are other sets of rows with similar circumstances.

It does make me wonder, even if it was possible to put such values into a cell, what you intend to do with those cells? They would be pretty meaningless to look at.

 

[Rbd340]

I did have a typo in my last code, it should have been

    Else
      b(r, 3) = b(r, 3) & "," & a(i, 3)
    End If

However, that will not solve your problem.

I believe that you are running foul of one (or more) of Excel's limits. For example, there is a limit of how many characters a cell can hold (32,767). For one example in your sample data (A=11563, B =34671499) there are over 2,200 rows. The concatenation of the column C values for these rows would produce a string of about 80,000 characters, more than double what a cell will hold! I believe there are other sets of rows with similar circumstances.

It does make me wonder, even if it was possible to put such values into a cell, what you intend to do with those cells? They would be pretty meaningless to look at.

Thank you I wasn't aware of this.
What I'm trying to do is group the users (column2) that have access to the same accounts (column 3) within the same subscription (column1).
I have multiple sheets about the same users (sheet for account settings, sheet for permissions, sheet for sign limits etc.) and only when the users all have the same info I can group them together.
The idea was to group all the data for each user in one cell so that I can compare them but unfortunately it seems like this won't work.

Do you per chance have a solution how to circumvent this?

 

[Peter_SSs]

What I'm trying to do is group the users (column2) that have access to the same accounts (column 3) within the same subscription (column1).

.. only when the users all have the same info I can group them together.

Your original sample and expected results seems to me to be grouping the accounts (columns 3) not the users (column 2).
If you are trying to group the users that have the same info, then for the original sample data, would it not be something like this? (I have highlighted some to try to show how I have grouped them)

Book1
	A	B	C	D	E	F	G
1	Subscription ID	User_ID	Account		Account	Subscription ID	User
2	A	1	DataXx		DataXx	A	1,2
3	A	1	DataAA		DataAA	A	1,2
4	A	1	DataBB		DataBB	A	1
5	A	2	DataXx		DataCc	A	2
6	A	2	DataCc		DataCC	B	1
7	A	2	DataAA		DataAA	B	1
8	B	1	DataCC		DataFF	B	1
9	B	1	DataAA		DataWW	B	1
10	B	1	DataFF		DataRR	B	2
11	B	1	DataWW		DataXX	B	2
12	B	2	DataRR
13	B	2	DataXX
Sheet9

 

[Rbd340]

Your original sample and expected results seems to me to be grouping the accounts (columns 3) not the users (column 2).
If you are trying to group the users that have the same info, then for the original sample data, would it not be something like this? (I have highlighted some to try to show how I have grouped them)

Hi Peter,

In the below link is what I'm trying to accomplish.
https://www.dropbox.com/s/v01i7rnszf851id/Example file.xlsx?dl=0

For each sheet in the workbook (except Output) I need to check if the users's permissions and settings are the same, if so, I need to group them.
I wanted to use your script to combine the data from the 'account permission' sheet into one cell per user & subscription combination. Then add the data from 'general permissions' and 'personal settings' to that cell and then compare them. If I find a match then I can group the user.

 

[tonyyy]

Given the descriptions and various data samples, it's still not clear (maybe just to me) what the input looks like or what the expected output should be. Having said that, the structured nature of the data seems to lend itself to a PivotTable solution...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Sample Data									PivotTable
2	Subscription ID	Usernumber	Account	Single1	Single2	Jointly1	Jointly2			Account	Subscription ID	Usernumber	Count of Single1	Count of Single2	Count of Jointly1	Count of Jointly2
3	1002222	UA012345	NL97XXXX1	Y	N	Y	N			NL97XXXX1	1002222	UA012345	1	1	1	1
4	1002222	UA012345	NL97XXXX3	Y	N	Y	N					UA123456	1	1	1	1
5	1002222	UA012345	NL97XXXX2	Y	N	Y	N			NL97XXXX2	1002222	UA012345	1	1	1	1
6	1002222	UA012345	NL97XXXX4	Y	N	Y	N					UA123456	1	1	1	1
7	1002222	UA123456	NL97XXXX1	Y	N	Y	N			NL97XXXX3	1002222	UA012345	1	1	1	1
8	1002222	UA123456	NL97XXXX3	Y	N	Y	N					UA123456	1	1	1	1
9	1002222	UA123456	NL97XXXX2	Y	N	Y	N			NL97XXXX4	1002222	UA012345	1	1	1	1
10	1002222	UA123456	NL97XXXX4	Y	N	Y	N					UA123456	1	1	1	1
11	1002222	UA123457	NL97XXXX5	Y	Y	Y	N			NL97XXXX5	1002222	UA123457	1	1	1	1
12	1002223	UA123457	NL97XXXX5	Y	Y	Y	N				1002223	UA123457	1	1	1	1
Sheet1

 

[Peter_SSs]

In the below link is what I'm trying to accomplish.

I don't understand how that Output sheet result was arrived at.

 

[Rbd340]

Hi Peter,

I've made an attempt to explain my problem, my goal and with a better example file here:
https://www.mrexcel.com/forum/excel-questions/1031450-grouping-data-part-2-a.html#post4950685

Thank you for all the help so far!