Grouping data

Status
Not open for further replies.
R

Rbd340

New Member
Joined
Oct 11, 2017
Messages
13
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!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Rbd340,

Welcome to the Board.

Is your output correct? Shouldn't it be...

Excel Workbook
JKL
17Column1Column2Column3
18A1DataXx,DataAA,DataBB
19A2DataXx,DataCc,DataAA
20B1DataCC,DataAA,DataFF,DataWW
21B2DataRR,DataXX
Sheet1



Cheers,

tonyyy
 
Upvote 0
Assuming ...
- Tony's result is the correct one
- Data is in columns A:C of the active sheet (with headings in row 1) when the code is run

.. try this in a copy of your workbook.

Code: 
Sub CombineData()
  Dim a As Variant
  Dim i As Long, r As Long
  
  a = Range("A1", Range("C" & Rows.Count).End(xlUp)).Value
  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
      a(r, 1) = a(i, 1): a(r, 2) = a(i, 2): a(r, 3) = a(i, 3)
    Else
      a(r, 3) = a(r, 3) & "," & a(i, 3)
    End If
  Next i
  With Sheets.Add(After:=ActiveSheet)
    With .Range("A1:C1").Resize(r)
      .Value = a
      .Columns.AutoFit
    End With
  End With
End Sub
 
Upvote 0
Hi Peter,

This is absolute gold. Tony was indeed right and thank you so much for this.
It works amazingly fast and will save me a lot of time.

Would it be possible as well if I have two sheets with the same values in Column 1 and 2 but different values of 3 to run the macro on both sheets and combine the result into the new sheet?

Once again thank you very much.

With regards,

Ricardo
 
Upvote 0
Rbd340 said:
Hi Peter,

This is absolute gold. Tony was indeed right and thank you so much for this.
It works amazingly fast and will save me a lot of time.

Would it be possible as well if I have two sheets with the same values in Column 1 and 2 but different values of 3 to run the macro on both sheets and combine the result into the new sheet?

Once again thank you very much.

With regards,

Ricardo
Click to expand...

Hmm, for some unknown reason the macro doesn't work for all my sheets. It doen't show the headers and while the third column gets adjusted, no values are added.

See screenshot:
KXlRYfB
KXlRYfB.png
 
Upvote 0
Rbd340 said:
Hmm, for some unknown reason the macro doesn't work for all my sheets. It doen't show the headers and while the third column gets adjusted, no values are added.
Click to expand...
Not quite sure what I am looking at.
- Sheet8 is in workbook Example. The other sheet in that book is Sheet5. The sample raw data you have shown is on Sheet1 in workbook Book4. Can you clarify what is happening there and what was the active sheet (& in what workbook) when the code was run? What workbook is the code in?
- I can't do anything with pictures except look at them. :)
If you could post a small amount of the sample data in a form that can be copied (like Tony's has done in his post) to test with it would be good. My signature block below has further information about that.
 
Upvote 0
I am a little concerned about the structure of Pbd340s table:

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
Click to expand...
The first two data rows contain 3 data items whilst the others do not.

Have I missed something in what Pbd340 requires, or has he inadvertently misplaced one item, Tonyyy has created a solution around that table, and Pbd340 has "proofread" it as correct to his intentions? We, unfortunately, can all do that.
 
Upvote 0
Peter_SSs said:
Not quite sure what I am looking at.
- Sheet8 is in workbook Example. The other sheet in that book is Sheet5. The sample raw data you have shown is on Sheet1 in workbook Book4. Can you clarify what is happening there and what was the active sheet (& in what workbook) when the code was run? What workbook is the code in?
- I can't do anything with pictures except look at them. :)
If you could post a small amount of the sample data in a form that can be copied (like Tony's has done in his post) to test with it would be good. My signature block below has further information about that.
Click to expand...

Hi,

I ran the script on the active workbook 5 and sheet 8 was the newly created sheet by the macro. Workbook4, sheet 1 has the original data copied for screenshot purposes. For an unknown reason on some sheets the macro does not create the headers and leaves column C empty (and sometimes B as well). Thanks for looking into the issue and sorry for the confusion.

Example below

Subscription ID

<tbody>
</tbody>
User_ID

<tbody>
</tbody>
Data

<tbody>
</tbody>
11861

<tbody>
</tbody>
73052713

<tbody>
</tbody>
NL17784951-EURUnlimited

<tbody>
</tbody>
11861

<tbody>
</tbody>
73052713

<tbody>
</tbody>
NL33784954-EURUnlimited

<tbody>
</tbody>
11861

<tbody>
</tbody>
83330567

<tbody>
</tbody>
NL17784951-EURUnlimited

<tbody>
</tbody>
11861

<tbody>
</tbody>
83330567

<tbody>
</tbody>
NL33784954-EURUnlimited

<tbody>
</tbody>
118612

<tbody>
</tbody>
30772749

<tbody>
</tbody>
NL272114133-USDUnlimited

<tbody>
</tbody>
118612

<tbody>
</tbody>
30772749

<tbody>
</tbody>
NL71651687616-EURUnlimited

<tbody>
</tbody>
118612

<tbody>
</tbody>
55875536

<tbody>
</tbody>
NL272114133-USDUnlimited

<tbody>
</tbody>
118612

<tbody>
</tbody>
55875536

<tbody>
</tbody>
NL71651687616-EURUnlimited

<tbody>
</tbody>
118612

<tbody>
</tbody>
55875536

<tbody>
</tbody>
NL272114133-USDUnlimited

<tbody>
</tbody>
118612

<tbody>
</tbody>
55875536

<tbody>
</tbody>
NL71651687616-EURUnlimited

<tbody>
</tbody>
118613

<tbody>
</tbody>
16893993

<tbody>
</tbody>
GB67238859261291-USDLimited5USD

<tbody>
</tbody>
118613

<tbody>
</tbody>
26526142

<tbody>
</tbody>
GB67238859261291-USDLimited1USD

<tbody>
</tbody>
118613

<tbody>
</tbody>
29008291

<tbody>
</tbody>
GB67238859261291-USDUnlimited

<tbody>
</tbody>
118613

<tbody>
</tbody>
81890375

<tbody>
</tbody>
GB67238859261291-USDUnlimited

<tbody>
</tbody>
118613

<tbody>
</tbody>
88654851

<tbody>
</tbody>
GB67238859261291-USDUnlimited

<tbody>
</tbody>
118613

<tbody>
</tbody>
98647637

<tbody>
</tbody>
GB67238859261291-USDLimited15USD

<tbody>
</tbody>
118614

<tbody>
</tbody>
20035869

<tbody>
</tbody>
NL157728322-EURUnlimited

<tbody>
</tbody>
118614

<tbody>
</tbody>
20035869

<tbody>
</tbody>
NL157728322-EURUnlimited

<tbody>
</tbody>
118614

<tbody>
</tbody>
20035869

<tbody>
</tbody>
NL157728322-EURUnlimited

<tbody>
</tbody>
118614

<tbody>
</tbody>
20035869

<tbody>
</tbody>
NL157728322-EURUnlimited

<tbody>
</tbody>
118615

<tbody>
</tbody>
2265716

<tbody>
</tbody>
NL9819524-EURLimited1EURLimited25EUR

<tbody>
</tbody>
118615

<tbody>
</tbody>
27399877

<tbody>
</tbody>

<tbody>
</tbody>
NL9819524-EURLimited2EURLimited25EUR

<tbody>
</tbody>
118615

<tbody>
</tbody>
35150887

<tbody>
</tbody>
NL9819524-EURLimited2EURLimited25EUR

<tbody>
</tbody>
118615

<tbody>
</tbody>
87627263

<tbody>
</tbody>
NL9819524-EURLimited5EURLimited1EUR

<tbody>
</tbody>
118617

<tbody>
</tbody>
66899051

<tbody>
</tbody>
NL317579624-EURUnlimited

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Rbd340 said:
Hi,

I ran the script on the active workbook 5 and sheet 8 was the newly created sheet by the macro. Workbook4, sheet 1 has the original data copied for screenshot purposes. For an unknown reason on some sheets the macro does not create the headers and leaves column C empty (and sometimes B as well). Thanks for looking into the issue and sorry for the confusion.

Example below
Click to expand...

Hope this reads better, can't delete my old post.
Excel Workbook
ABC
1Subscription IDUser_IDData
21186173052713NL17784951-EURUnlimited
31186173052713NL33784954-EURUnlimited
41186183330567NL17784951-EURUnlimited
51186183330567NL33784954-EURUnlimited
611861230772749NL272114133-USDUnlimited
711861230772749NL71651687616-EURUnlimited
811861255875536NL272114133-USDUnlimited
911861255875536NL71651687616-EURUnlimited
1011861258725364NL272114133-USDUnlimited
1111861258725364NL71651687616-EURUnlimited
1211861316893993GB67238859261291-USDLimited5USD
1311861326526142GB67238859261291-USDLimited1USD
1411861329008291GB67238859261291-USDUnlimited
1511861381890375GB67238859261291-USDUnlimited
1611861388654851GB67238859261291-USDUnlimited
1711861398647637GB67238859261291-USDLimited15USD
1811861420035869NL157728322-EURUnlimited
1911861420035869NL317728325-EURUnlimited
2011861420035869NL747728327-EURUnlimited
2111861420035869NL8753862-EURUnlimited
221186152265716NL9819524-EURLimited1EURLimited25EUR
2311861527399877NL9819524-EURLimited2EURLimited25EUR
2411861535150887NL9819524-EURLimited2EURLimited25EUR
2511861587627263NL9819524-EURLimited5EURLimited1EUR
2611861766899051NL317579624-EURUnlimited
Sheet2
 
Last edited:
Upvote 0
BrianJN1 said:
The first two data rows contain 3 data items whilst the others do not.

Have I missed something in what Pbd340 requires, or has he inadvertently misplaced one item,...
Click to expand...
I think that you have missed something ..
In the original sample data, 3 rows had "A" in column 1 and "1" in column 2
3 rows had "A" & "2"
4 rows had "B" & "1"
2 rows had "B" & "2"
So the OP's preferred output for column C was correct. What was incorrect were some of the values in the first 2 columns of the preferred output.

Rbd340 said:
For an unknown reason on some sheets the macro does not create the headers and leaves column C empty (and sometimes B as well).
Click to expand...
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.
 
Upvote 0
Status
Not open for further replies.

Similar threads

D
  • Question
dynamic data changes
Replies
0
Views
125
DSCHROE67
D
B
  • Question
Groups/Arrays/Patterns Help
Replies
5
Views
356
JamesCanale
JamesCanale
K
  • Question
Previously working very complex VBA now stopped
Replies
7
Views
167
Alex Blakenburg
A
V
  • Question
VBA: Clear Contents for Multiple Groups of Columns based on Data
Replies
3
Views
274
VBA Beginner 2024
V
K
  • Question
Help with a formula please
Replies
2
Views
252
kralin
K

Forum statistics

Threads
1,215,475
Messages
6,125,028
Members
449,205
Latest member
Eggy66

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