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

[Karenola888]

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!

Curr. Name	Date Completed	Student
JVBasket	2/10/2014	Blow, Joe
JVSoftball	5/28/2015	Blow, Joe
SkiClub	5/29/2015	Blow, Joe
JVGSoccer	10/21/2014	Blow, Joe
JVGSoccer	10/31/2013	Blow, Joe
VIndoorTr	2/16/2015	Doe, Jane
VFieldHcky	10/31/2013	Doe, Jane
VFieldHcky	10/31/2014	Doe, Jane
		Smith, E
UnifiedBasket	4/8/2015	Williams, A
Mentor	6/6/2014	Reading, J
IceHockey	2/15/2014	Homes, D
IceHockey	2/20/2015	Homes, D

<tbody>
</tbody>

Student	Curr 1	Curr 2	Curr 3	Curr 4	Curr 5
Blow, Joe	JVBasket	2/10/2014	JVSoftball	5/28/2015	SkiClub	5/29/2015	JVGSoccer	10/21/2014	JVGSoccer	10/31/2013
Doe, Jane	VIndoorTr	2/16/2015	VFieldHcky	10/31/2013	VFieldHcky	10/31/2014
Smith, E
Williams, A	UnifiedBasket	4/8/2015
Reading, J	Mentor	6/6/2014
Homes, D	2/15/2014	2/15/2014	IceHockey	2/20/2015

<tbody>
</tbody>

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

 

[Gingertrees]

This is not easily accomplished. What does Curr 1 Curr 2 etc stand for? That was not in your original table.

 

[Karenola888]

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.

 

[Karenola888]

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.

Blow, Joe	JVBasket	JVSoftball	SkiClub	JVGSoccer	JVGSoccer
	2/10/2014	5/28/2015	5/29/2015	10/21/2014	10/31/2013
Doe, Jane	VIndoorTr	VFieldHcky	VFieldHcky
	2/16/2015	10/31/2013	10/31/2014
Smith, E
Williams, A	UnifiedBasket
	4/8/2015
Reading, J	Mentor
	6/6/2014
Homes, D	IceHockey	IceHockey
	2/15/2014	2/20/2015

<colgroup><col><col><col span="2"><col span="2"></colgroup><tbody>
</tbody>

 

[MickG]

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.

[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

 

[tybaltlives]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
11								8	9	9	10	10	11
12	Curr. Name	Date Completed	Student		fst row	count
13	JVBasket	2/10/2014	Blow, Joe		13	5	Blow, Joe	JVBasket	2/10/2014	JVSoftball	5/28/2015	SkiClub	5/29/2015
14	JVSoftball	5/28/2015	Blow, Joe		18	3	Doe, Jane	VIndoorTr	2/16/2015	VFieldHcky	10/31/2013	VFieldHcky	10/31/2014
15	SkiClub	5/29/2015	Blow, Joe		21	1	Smith, E	0
16	JVGSoccer	10/21/2014	Blow, Joe		22	1	Williams, A	UnifiedBasket	4/8/2015
17	JVGSoccer	10/31/2013	Blow, Joe		23	1	Reading, J	Mentor	6/6/2014
18	VIndoorTr	2/16/2015	Doe, Jane		24	2	Homes, D	IceHockey	2/15/2014	IceHockey	2/20/2015
19	VFieldHcky	10/31/2013	Doe, Jane
20	VFieldHcky	10/31/2014	Doe, Jane					=IF(COUNT(G13:$H13)<$F13,INDIRECT("a"&$E13+COLUMN()-H$11),"")
21			Smith, E					=IF(COUNT(G13:$H13)<$F13,INDIRECT("b"&$E13+COLUMN()-I$11),"")
Transpose data col to row

Cell Formulas
Range	Formula
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),"")

 

[Karenola888]

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!!!

 

[MickG]

You're very welcome.
Glad it works for you !!