Data transformation help - transaction data currently in columns needs to altered

F

friday13th

New Member
Joined
Nov 16, 2013
Messages
2
Hi guys

I have a set of data, pulled from Google Analytics in the following format:

Transaction ID | Product Name
1 | milk
1 | bread
2 | jam
3 | bread
3 | honey


I need to transform this data into the following format:


Transaction ID | Product Name
1 | milk, bread
2 | jam
3 | bread, honey


Basically, I want each row to have a unique transaction ID, rather than have the products split onto individual rows.

Do I need to use VBA or similar to make this transformation - or am I missing something very obvious !?

Thanks
 
Sort by date Sort by votes
friday13th,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?


The macro will copy your raw data to an array.

Then, the macro will sort the data by column A ascending, column B ascending.

Then the results will be written to column E and F.

Then the data in column A and B will be replaced by the array.

Sample raw data:


Excel 2007
ABCDEF
1Transaction IDProduct Name
21milk
31bread
42jam
53bread
63honey
7
Sheet1


After the macro:


Excel 2007
ABCDEF
1Transaction IDProduct NameTransaction IDProduct Name
21milk1bread, milk
31bread2jam
42jam3bread, honey
53bread
63honey
7
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Explicit
Sub ReorgData()
' hiker95, 11/16/2013
' http://www.mrexcel.com/forum/excel-questions/739798-data-transformation-help-transaction-data-currently-columns-needs-altered.html
Dim oa As Variant
Dim r As Long, lr As Long, n As Long, nr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
oa = Range("A2:B" & lr)
Range("A2:B" & lr).Sort key1:=Range("A2"), order1:=1, key2:=Range("B2"), order1:=1
Columns("E:F").ClearContents
Range("E1").Resize(, 2).Value = Range("A1").Resize(, 2).Value
For r = 2 To lr
  n = Application.CountIf(Columns(1), Cells(r, 1).Value)
  nr = Range("E" & Rows.Count).End(xlUp).Offset(1).Row
  If n = 1 Then
    Range("E" & nr).Resize(, 2).Value = Range("A" & r).Resize(, 2).Value
  Else
    Range("E" & nr).Value = Range("A" & r).Value
    Range("F" & nr).Value = Join(Application.Transpose(Range("B" & r & ":B" & r + n - 1)), ", ")
  End If
  r = r + n - 1
Next r
Range("A2:B" & lr) = oa
Columns("E:F").AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.
 
Last edited:
Upvote 0
Thank you for the detailed help!


hiker95 said:
friday13th,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?


The macro will copy your raw data to an array.

Then, the macro will sort the data by column A ascending, column B ascending.

Then the results will be written to column E and F.

Then the data in column A and B will be replaced by the array.

Sample raw data:

Excel 2007
ABCDEF
Transaction IDProduct Name
milk
bread
jam
bread
honey

<colgroup><col style="width: 25pxpx"><col><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: right"][/TD]

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

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

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

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

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

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

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

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

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

[TD="align: right"][/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: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



After the macro:

Excel 2007
ABCDEF
Transaction IDProduct NameTransaction IDProduct Name
milkbread, milk
breadjam
jambread, honey
bread
honey

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

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

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

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

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

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

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

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

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

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

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

[TD="align: right"][/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: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Explicit
Sub ReorgData()
' hiker95, 11/16/2013
' http://www.mrexcel.com/forum/excel-questions/739798-data-transformation-help-transaction-data-currently-columns-needs-altered.html
Dim oa As Variant
Dim r As Long, lr As Long, n As Long, nr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
oa = Range("A2:B" & lr)
Range("A2:B" & lr).Sort key1:=Range("A2"), order1:=1, key2:=Range("B2"), order1:=1
Columns("E:F").ClearContents
Range("E1").Resize(, 2).Value = Range("A1").Resize(, 2).Value
For r = 2 To lr
  n = Application.CountIf(Columns(1), Cells(r, 1).Value)
  nr = Range("E" & Rows.Count).End(xlUp).Offset(1).Row
  If n = 1 Then
    Range("E" & nr).Resize(, 2).Value = Range("A" & r).Resize(, 2).Value
  Else
    Range("E" & nr).Value = Range("A" & r).Value
    Range("F" & nr).Value = Join(Application.Transpose(Range("B" & r & ":B" & r + n - 1)), ", ")
  End If
  r = r + n - 1
Next r
Range("A2:B" & lr) = oa
Columns("E:F").AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.
Click to expand...
 
Upvote 0
friday13th,

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.


Thank you for the detailed help!
Click to expand...

You are very welcome. Glad I could help.


The following macro is very fast because of the use of the Scripting.Dictionary.

Sample raw data:


Excel 2007
ABCDEF
1Transaction IDProduct Name
21milk
31bread
42jam
53bread
63honey
7
Sheet1


After the new macro:


Excel 2007
ABCDEF
1Transaction IDProduct NameTransaction IDProduct Name
21milk1milk, bread
31bread2jam
42jam3bread, honey
53bread
63honey
7
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Option Explicit
Sub ReorgDataV2()
' hiker95, 11/16/2013
' http://www.mrexcel.com/forum/excel-questions/739798-data-transformation-help-transaction-data-currently-columns-needs-altered.html
' Thank you MickG
Dim c As Range, rng As Range
Set rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each c In rng
    If Not .Exists(c.Value) Then
      .Add c.Value, c.Offset(, 1)
    Else
      .Item(c.Value) = .Item(c.Value) & ", " & c.Offset(, 1)
    End If
  Next
  Range("E1").Resize(.Count, 2) = Application.Transpose(Array(.keys, .Items))
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgDataV2 macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Need to transform columns-lists into a coincidence matrix (array)
Replies
9
Views
268
Cubist
Cubist
S
  • Question Question
HYPERLINK() function Seems Unable to Jump to Another Sheets Table and Filter
Replies
6
Views
730
Skyybot
Skyybot
B
  • Solved
Average of last X transactions by customer
Replies
5
Views
357
Dave Patton
D
D
  • Question Question
Data Validation List without duplicates
2 3 4
Replies
30
Views
1K
kvsrinivasamurthy
K
datatronics505
  • Solved
Absolute reference to a cell of another table
Replies
5
Views
448
datatronics505
datatronics505

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