Transpose numbers as values in a column change using a macro or VBA - Excel 2013

K

Karenola888

New Member
Joined
Sep 15, 2015
Messages
4
I know how to do a typical transpose of numbers, but this is trickier. I'd like to transpose the curricular name and date completed to go across the spreadsheet until a student name changes, and then start the transpose again. Here is a sample of the data I'm working with. I need it to look as way below. Any ideas / help is appreciated! Thank you!

[TABLE="class: grid, width: 281"]
<tbody>[TR]
[TD]Curr. Name[/TD]
[TD]Date Completed[/TD]
[TD]Student[/TD]
[/TR]
[TR]
[TD]JVBasket[/TD]
[TD="align: right"]2/10/2014[/TD]
[TD]Blow, Joe[/TD]
[/TR]
[TR]
[TD]JVSoftball[/TD]
[TD="align: right"]5/28/2015[/TD]
[TD]Blow, Joe[/TD]
[/TR]
[TR]
[TD]SkiClub[/TD]
[TD="align: right"]5/29/2015[/TD]
[TD]Blow, Joe[/TD]
[/TR]
[TR]
[TD]JVGSoccer[/TD]
[TD="align: right"]10/21/2014[/TD]
[TD]Blow, Joe[/TD]
[/TR]
[TR]
[TD]JVGSoccer[/TD]
[TD="align: right"]10/31/2013[/TD]
[TD]Blow, Joe[/TD]
[/TR]
[TR]
[TD]VIndoorTr[/TD]
[TD="align: right"]2/16/2015[/TD]
[TD]Doe, Jane[/TD]
[/TR]
[TR]
[TD]VFieldHcky[/TD]
[TD="align: right"]10/31/2013[/TD]
[TD]Doe, Jane[/TD]
[/TR]
[TR]
[TD]VFieldHcky[/TD]
[TD="align: right"]10/31/2014[/TD]
[TD]Doe, Jane[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Smith, E[/TD]
[/TR]
[TR]
[TD]UnifiedBasket[/TD]
[TD="align: right"]4/8/2015[/TD]
[TD]Williams, A[/TD]
[/TR]
[TR]
[TD]Mentor[/TD]
[TD="align: right"]6/6/2014[/TD]
[TD]Reading, J[/TD]
[/TR]
[TR]
[TD]IceHockey[/TD]
[TD="align: right"]2/15/2014[/TD]
[TD]Homes, D[/TD]
[/TR]
[TR]
[TD]IceHockey[/TD]
[TD="align: right"]2/20/2015[/TD]
[TD]Homes, D[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 733"]
<tbody>[TR]
[TD]Student[/TD]
[TD="colspan: 2"]Curr 1[/TD]
[TD="colspan: 2"]Curr 2[/TD]
[TD="colspan: 2"]Curr 3[/TD]
[TD="colspan: 2"]Curr 4[/TD]
[TD="colspan: 2"]Curr 5[/TD]
[/TR]
[TR]
[TD]Blow, Joe[/TD]
[TD]JVBasket[/TD]
[TD="align: right"]2/10/2014[/TD]
[TD]JVSoftball[/TD]
[TD="align: right"]5/28/2015[/TD]
[TD]SkiClub[/TD]
[TD="align: right"]5/29/2015[/TD]
[TD]JVGSoccer[/TD]
[TD="align: right"]10/21/2014[/TD]
[TD]JVGSoccer[/TD]
[TD="align: right"]10/31/2013[/TD]
[/TR]
[TR]
[TD]Doe, Jane[/TD]
[TD]VIndoorTr[/TD]
[TD="align: right"]2/16/2015[/TD]
[TD]VFieldHcky[/TD]
[TD="align: right"]10/31/2013[/TD]
[TD]VFieldHcky[/TD]
[TD="align: right"]10/31/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smith, E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Williams, A[/TD]
[TD]UnifiedBasket[/TD]
[TD="align: right"]4/8/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reading, J[/TD]
[TD]Mentor[/TD]
[TD="align: right"]6/6/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Homes, D[/TD]
[TD="align: right"]2/15/2014[/TD]
[TD="align: right"]2/15/2014[/TD]
[TD]IceHockey[/TD]
[TD="align: right"]2/20/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Transpose numbers as values in a column change using a macro or VBA - Excel 2013
 
Sort by date Sort by votes
This is not easily accomplished. What does Curr 1 Curr 2 etc stand for? That was not in your original table.
 
Upvote 0
Curricular Activity. I'm working in a school system these need to be tracked for each student. They need to have a certain number before they can graduate. If I can build them going across the spreadsheet for each student - it's much easier for the teacher to track. For example, JVBasket under Curr Name is one. My appreviation of Curr 1 is to let the teacher know this is the first curricular activity and the date it was completed.
 
Upvote 0
I've been thinking maybe if I add a blank row each time a student name changes, I can somehow reference the name and build a transpose (of only the Curr. Name and Date Completed) by student only (until it hits a blank line), then drops to the next student and transposes again - all the way through the data. I tried some recording what I want to do and the data ends up looking like below, which is fine. I believe the challenge is referencing the correct cell. Again, any ideas are welcome! Thanks.
[TABLE="class: grid, width: 514"]
<colgroup><col><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]Blow, Joe
[/TD]
[TD]JVBasket[/TD]
[TD]JVSoftball[/TD]
[TD]SkiClub[/TD]
[TD]JVGSoccer[/TD]
[TD]JVGSoccer[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2/10/2014[/TD]
[TD="align: right"]5/28/2015[/TD]
[TD="align: right"]5/29/2015[/TD]
[TD="align: right"]10/21/2014[/TD]
[TD="align: right"]10/31/2013[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Doe, Jane[/TD]
[TD]VIndoorTr[/TD]
[TD]VFieldHcky[/TD]
[TD]VFieldHcky[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2/16/2015[/TD]
[TD="align: right"]10/31/2013[/TD]
[TD="align: right"]10/31/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smith, E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Williams, A[/TD]
[TD]UnifiedBasket[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4/8/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reading, J[/TD]
[TD]Mentor[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6/6/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Homes, D[/TD]
[TD]IceHockey[/TD]
[TD]IceHockey[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2/15/2014[/TD]
[TD="align: right"]2/20/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
This code should produce your original requirement, if you would like it altered to your latest Arrangement , let me know.
The code results will be shown on sheet2.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG16Sep16
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
ReDim Ray(1 To Rng.Count, 1 To 3)
n = 1
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            n = n + 1
            Ray(1, 1) = "Student": Ray(1, 2) = "Curr 1"
            Ray(n, 1) = Dn.Value: Ray(n, 2) = Dn.Offset(, -2).Value: Ray(n, 3) = Dn.Offset(, -1).Value
            .Add Dn.Value, Array(n, 3, 1)
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Dn.Value)
            Q(1) = Q(1) + 2: Q(2) = Q(2) + 1
            oMax = Application.Max(Q(1), oMax)
            [COLOR="Navy"]If[/COLOR] UBound(Ray, 2) < oMax [COLOR="Navy"]Then[/COLOR] ReDim Preserve Ray(1 To Rng.Count, 1 To Q(1))
            Ray(1, Q(1) - 1) = "Curr " & Q(2)
            Ray(Q(0), Q(1) - 1) = Dn.Offset(, -2): Ray(Q(0), Q(1)) = Dn.Offset(, -1).Value
            .Item(Dn.Value) = Q
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(n, oMax)
    .Value = Ray
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
here is what I came up with. Not entirely happy with it.

Cells H13 and I13 (as a pair) can be copied individually over to the right for Joe blow. At that point, the row may be copied downwards for everyone else. There are helper cells required. You need to find the forst row of data (13 in my case) and count the number of occurances of student names. The entire initial table needs to be sorted by student name (alphabetical) and date completed (increasing order). Finally you need a helper row (starts in H11) that basically is the first column number of the transposed data (8 in my case) and incremented by 1 every other Curr. Name. so 8-9-9-10-10-11-11, etc. You only need to do this once though for the most Curr's a student has in your data set. You manually add the Curr 1, Curr 2, etc.


Excel 2010
ABCDEFGHIJKLM
11899101011
12Curr. NameDate CompletedStudentfst rowcount
13JVBasket2/10/2014Blow, Joe135Blow, JoeJVBasket2/10/2014JVSoftball5/28/2015SkiClub5/29/2015
14JVSoftball5/28/2015Blow, Joe183Doe, JaneVIndoorTr2/16/2015VFieldHcky10/31/2013VFieldHcky10/31/2014
15SkiClub5/29/2015Blow, Joe211Smith, E0
16JVGSoccer10/21/2014Blow, Joe221Williams, AUnifiedBasket4/8/2015
17JVGSoccer10/31/2013Blow, Joe231Reading, JMentor6/6/2014
18VIndoorTr2/16/2015Doe, Jane242Homes, DIceHockey2/15/2014IceHockey2/20/2015
19VFieldHcky10/31/2013Doe, Jane
20VFieldHcky10/31/2014Doe, Jane=IF(COUNT(G13:$H13)<$F13,INDIRECT("a"&$E13+COLUMN()-H$11),"")
21Smith, E=IF(COUNT(G13:$H13)<$F13,INDIRECT("b"&$E13+COLUMN()-I$11),"")
Transpose data col to row
Cell Formulas
RangeFormula
E13=MATCH(G13,$C$13:$C$25,0)+12
F13=COUNTIF($C$13:$C$25,G13)
H13=IF(COUNT(G13:$H13)<$F13,INDIRECT("a"&$E13+COLUMN()-H$11),"")
I13=IF(COUNT(G13:$H13)<$F13,INDIRECT("b"&$E13+COLUMN()-I$11),"")
J13=IF(COUNT($H13:I13)<$F13,INDIRECT("a"&$E13+COLUMN()-J$11),"")
K13=IF(COUNT($H13:I13)<$F13,INDIRECT("b"&$E13+COLUMN()-K$11),"")
 
Upvote 0
Mick, this is BRILLIANT. It does exactly what my first request covered. Wow. This is absolutely fine. I put the second request in only because I couldn't get around how to do the first. I would like to understand more how you did what you did and I want to go through this line by line. I may have a couple of questions if you don't mind questions. :) Thanks a million times!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Using a dynamic formula to calculate value of reinvested dividends over changing time periods
Replies
2
Views
3K
Tuckbox
T
J
  • Question Question
How to Transpose large data in Excel?
Replies
1
Views
2K
MickG
M
N
  • Question Question
Highlight rows based on multiple cell values by date
Replies
8
Views
487
nitaly
N
K
  • Question Question
VBA macro HELP- Column to rows
Replies
2
Views
582
offthelip
O
N
  • Question Question
Insert Rows Where Column Value Changes
Replies
4
Views
862
Neild137
N

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