Some Data Manipulation

G

geliseev

Board Regular
Joined
Feb 3, 2009
Messages
79
Hi,


I have data by unique student ID that includes parent ID, let’s say like this:

<table class="MsoNormalTable" style="width:117.65pt;margin-left:-1.05pt;border-collapse:collapse;mso-yfti-tbllook: 1184;mso-padding-alt:0in 0in 0in 0in" border="0" cellpadding="0" cellspacing="0" width="157"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:15.0pt"> <td style="width:63.65pt;border:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="85">
Student ID​
</td> <td style="width:.75in;border:solid windowtext 1.0pt; border-left:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="72">
Parent ID​
</td> </tr> <tr style="mso-yfti-irow:1;height:15.0pt"> <td style="width:63.65pt;border:solid windowtext 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="85">
1​
</td> <td style="width:.75in;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="72">
11​
</td> </tr> <tr style="mso-yfti-irow:2;height:15.0pt"> <td style="width:63.65pt;border:solid windowtext 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="85">
2​
</td> <td style="width:.75in;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="72">
11​
</td> </tr> <tr style="mso-yfti-irow:3;height:15.0pt"> <td style="width:63.65pt;border:solid windowtext 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="85">
3​
</td> <td style="width:.75in;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="72">
12​
</td> </tr> <tr style="mso-yfti-irow:4;height:15.0pt"> <td style="width:63.65pt;border:solid windowtext 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="85">
4​
</td> <td style="width:.75in;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="72">
13​
</td> </tr> <tr style="mso-yfti-irow:5;height:15.0pt"> <td style="width:63.65pt;border:solid windowtext 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="85">
5​
</td> <td style="width:.75in;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="72">
13​
</td> </tr> <tr style="mso-yfti-irow:6;mso-yfti-lastrow:yes;height:15.0pt"> <td style="width:63.65pt;border:solid windowtext 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="85">
6​
</td> <td style="width:.75in;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="72">
13​
</td> </tr> </tbody></table>


And I need to rearrange it to list by unique parent ID, preserving student info, in the end, like this:

<table class="MsoNormalTable" style="width:117.65pt;margin-left:-1.05pt;border-collapse:collapse;mso-yfti-tbllook: 1184;mso-padding-alt:0in 0in 0in 0in" border="0" cellpadding="0" cellspacing="0" width="157"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:15.0pt"> <td style="width:63.65pt;border:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="85"> Parent ID
</td> <td style="width:.75in;border:solid windowtext 1.0pt; border-left:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="72"> Student ID
</td> </tr> <tr style="mso-yfti-irow:1;height:15.0pt"> <td style="width:63.65pt;border:solid windowtext 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="85">
11​
</td> <td style="width:.75in;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="72"> 1, 2
</td> </tr> <tr style="mso-yfti-irow:2;height:15.0pt"> <td style="width:63.65pt;border:solid windowtext 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="85">
12​
</td> <td style="width:.75in;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="72">
3​
</td> </tr> <tr style="mso-yfti-irow:3;mso-yfti-lastrow:yes;height:15.0pt"> <td style="width:63.65pt;border:solid windowtext 1.0pt; border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="85">
13​
</td> <td style="width:.75in;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="72"> 4, 5, 6
</td> </tr> </tbody></table>


Any idea how this can be done?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
I think you could use a nested if function similar to the following. You might need to tweak it a bit though. I assume your columns are setup Student Id in A and Parent Id in B

=IF(B1=B$2,CONCATENATE(A1,", ",A$2),IF(B1=B$3,CONCATENATE(A1,", ",A$3),IF(B1=B$4,CONCATENATE(A1,", ",A$4),IF(B1=B$5,CONCATENATE(A1,", ",A$5),IF(B1=B$6,CONCATENATE(A1,", ",A$6),A1)))))
 
Upvote 0
Thanks.

Yes, this could work for a small set of data, but not if you have 5,000 rows and you need to compare each cell to the previous ones to see if they match ;)

There has to be a better, more universal solution to this...



Desu Nota from Columbus said:
I think you could use a nested if function similar to the following. You might need to tweak it a bit though. I assume your columns are setup Student Id in A and Parent Id in B

=IF(B1=B$2,CONCATENATE(A1,", ",A$2),IF(B1=B$3,CONCATENATE(A1,", ",A$3),IF(B1=B$4,CONCATENATE(A1,", ",A$4),IF(B1=B$5,CONCATENATE(A1,", ",A$5),IF(B1=B$6,CONCATENATE(A1,", ",A$6),A1)))))
Click to expand...
 
Upvote 0
yeah...........not the best idea unless you fathom writing 5,000+ if statements!!!!!!!!!!! Which i don't believe to be possible even in Excel 2010. Hopefully someone much smarter with excel than I will solve this quandary. I am curious now about how to solve this.
 
Last edited:
Upvote 0
By macro, assuming that Column A and Column B contain the data, and that A1:B1 contains the column headers, maybe...

Code: 
[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] RearrangeData()

    [color=darkblue]Dim[/color] oDict [color=darkblue]As[/color] [color=darkblue]Object[/color]
    [color=darkblue]Dim[/color] vArray() [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] vInput [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]Set[/color] oDict = CreateObject("Scripting.Dictionary")
    
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    vInput = Range("A2:B" & LastRow)
    
    Cnt = 0
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](vInput)
        [color=darkblue]If[/color] oDict.Exists(vInput(i, 2)) [color=darkblue]Then[/color]
            vArray(2, oDict.Item(vInput(i, 2))) = vArray(2, oDict.Item(vInput(i, 2))) & ", " & vInput(i, 1)
        [color=darkblue]Else[/color]
            Cnt = Cnt + 1
            [color=darkblue]ReDim[/color] [color=darkblue]Preserve[/color] vArray(1 [color=darkblue]To[/color] 2, 1 To Cnt)
            vArray(1, Cnt) = vInput(i, 2)
            vArray(2, Cnt) = vInput(i, 1)
            oDict.Add vInput(i, 2), Cnt
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
    Range("A1").Value = "Parent ID"
    
    Range("B1").Value = "Student ID"
    
    Range("A2:B" & LastRow).ClearContents
    
    Range("A2").Resize(Cnt, 2) = WorksheetFunction.Transpose(vArray)
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

Note that the above code needs to be place in a regular module (Alt+F11 > Insert > Module, copy/paste into the regular module, and then Alt+Q).
 
Upvote 0
Wow, that was quick :)

I tested it on a small set of data and seems to be working fine. I will test it out more rigorously a bit later and will report findings.

Thanks so much!

Domenic said:
By macro, assuming that Column A and Column B contain the data, and that A1:B1 contains the column headers, maybe...

Code: 
[FONT=Verdana][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] RearrangeData()

    [COLOR=darkblue]Dim[/COLOR] oDict [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] vArray() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] vInput [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Cnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] oDict = CreateObject("Scripting.Dictionary")
    
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    vInput = Range("A2:B" & LastRow)
    
    Cnt = 0
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vInput)
        [COLOR=darkblue]If[/COLOR] oDict.Exists(vInput(i, 2)) [COLOR=darkblue]Then[/COLOR]
            vArray(2, oDict.Item(vInput(i, 2))) = vArray(2, oDict.Item(vInput(i, 2))) & ", " & vInput(i, 1)
        [COLOR=darkblue]Else[/COLOR]
            Cnt = Cnt + 1
            [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] vArray(1 [COLOR=darkblue]To[/COLOR] 2, 1 To Cnt)
            vArray(1, Cnt) = vInput(i, 2)
            vArray(2, Cnt) = vInput(i, 1)
            oDict.Add vInput(i, 2), Cnt
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
    Range("A1").Value = "Parent ID"
    
    Range("B1").Value = "Student ID"
    
    Range("A2:B" & LastRow).ClearContents
    
    Range("A2").Resize(Cnt, 2) = WorksheetFunction.Transpose(vArray)
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
[/FONT]
Note that the above code needs to be place in a regular module (Alt+F11 > Insert > Module, copy/paste into the regular module, and then Alt+Q).
Click to expand...
 
Upvote 0
geliseev,


Sample data in worksheet Sheet1 before the macro:


Excel Workbook
ABCDE
1Student IDParent ID
2111
3211
4312
5413
6513
7613
8
Sheet1





After the macro:


Excel Workbook
ABCDE
1Student IDParent IDParent IDStudent ID
2111111, 2
3211123
4312134, 5, 6
5413
6513
7613
8
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, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub ReorgData()
' hiker95, 05/17/2011
' http://www.mrexcel.com/forum/showthread.php?t=550681
Dim LR As Long, a As Long, SR As Long, ER As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:B" & LR).Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("A2") _
  , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
  Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
Columns(2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns(4), Unique:=True
Range("E1").Value = Range("A1").Value
LR = Cells(Rows.Count, 4).End(xlUp).Row
For a = 2 To LR Step 1
  SR = Application.Match(Cells(a, 4), Columns(2), 0)
  ER = Application.Match(Cells(a, 4), Columns(2), 1)
  If SR <> ER Then
    Cells(a, 5) = Join(Application.Transpose(Range("A" & SR & ":A" & ER)), ", ")
  Else
    Cells(a, 5).Value = Cells(SR, 1).Value
  End If
Next a
Columns("D:E").AutoFit
LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:B" & LR).Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
  , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
  Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
Application.ScreenUpdating = True
End Sub


Then run the ReorgData macro.
 
Upvote 0
geliseev said:
Wow, that was quick :)

I tested it on a small set of data and seems to be working fine. I will test it out more rigorously a bit later and will report findings.

Thanks so much!
Click to expand...

You're very welcome! Thanks for the feedback!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
D
  • Question Question
How to output a dynamic range where the height of the range varies
Replies
0
Views
1K
dagda13
D

Forum statistics

Threads
1,224,560
Messages
6,179,519
Members
452,921
Latest member
BBQKING

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