Transpose multiple rows to a single row based on change of cell and then remove repeated data

[AndrewNic]

Hello,
I'm in need of some assistance with re-organising our data. We need to transpose and remove duplicates from an excel CSV so it can be accepted by other program or introduced to a PDF form.

We get this:

StudentCode	First	Last	Year	Form	House	Subject	Code	Teacher	Assessment	Result
ABC123	John	Smith	Year 8	P3M	MELB	English	BENG	Jane SMITH	On Time	Yes
ABC123	John	Smith	Year 8	P3M	MELB	English	BENG	Jane SMITH	Equipped	Yes
CBA321	Thomas	Jones	Year 7	PN2	NEWT	Maths	BMAT	Tim APPLE	On Time	Yes
CBA321	Thomas	Jones	Year 7	PN2	NEWT	English	BENG	Jane SMITH	Ready to learn	Sometimes

We need:

StudentCode

First

Last

Year

Form

House

Subject

Code

Teacher

Assessment

Result

Assessment

Result

Subject

Teacher

Assessment

Result

ABC123

John

Smith

Year 8

P3M

MELB

English

ENGB

Jane SMITH

On Time

Yes

Equipped

Yes

Maths

Tim Apple

On Time

Yes

CBA

Thomas

Jones

Year 7

PN2

NEWT

Maths

BMAT

Tim APPLE

On Time

Yes

Equipped

Yes

English

Jane SMITH

Ready to Learn

Sometimes

Put simply, we need a macro that adds the subject, teacher, assessment, and result to the end of each change of student code and removes the repeated student code, first, last, year, form and house.

Really appreciate anyone's help. I tried editing various macro but cant get anything to work.

Cheers,

Andrew

 

[sandy666]

your expected result is illogical or I misunderstood your explanation

part of the result

StudentCode	First	Last	Year	Form	House	Subject	Code	Teacher	Assessment	Result
ABC123	John	Smith	Year 8	P3M	MELB	English	BENG	Jane SMITH	On Time	Yes
CBA321	Thomas	Jones	Year 7	PN2	NEWT	Maths	BMAT	Tim APPLE	On Time	Yes

 

[AndrewNic]

Hi Sandy666,

Sorry if my explanation wasn't very clear. Let me see if I can illustrate it a little better. We have this...

SemesterReportAllResultsCsvExport Generated - 2020-07-31_0715PM.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	StudentFirstName	StudentLastName	StudentCode	StudentYearLevel	StudentFormGroup	StudentHouseGroup	Subject	ClassCode	Teacher	AssessmentArea	Result
2	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Uses personal strengths to prepare for complex learning tasks	Very Good
3	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Uses empathy to understand and accept others	Outstanding
4	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Respects learning environment	Consistently
5	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Collaborates with students and teachers	Outstanding
6	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Participates in online meetings	Mostly
7	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Seeks clarification of tasks	At Times
8	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Accesses resources on learning management systems	Mostly
9	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Effectively uses digital resources (laptop, school software etc.) for learning	Mostly
10	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Mr Jones	Uses personal strengths to prepare for complex learning tasks	Excellent
11	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Mr Jones	On time	Mostly
12	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Mr Jones	Accepts the responsibility associated with independence	Very Good
13	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Mr Jones	Ready for learning	Consistently
14	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Mr Jones	Uses self-reflection to build confidence and adaptability	Excellent
15	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Ms Robinson	Equipped for Class	Consistently
16	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Ms Robinson	Uses empathy to understand and accept others	Excellent
17	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Ms Robinson	Respects learning environment	Consistently
18	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Ms Robinson	Collaborates with students and teachers	Excellent
SemesterReportAllResultsCsvExpo

We need to transpose the data so we have the Subject, Class, Teacher, AssessmentArea, and Result added to the end of the first line of the StudentFirstName, Lastname, StudentCode, Formgroup, StudentHouseGroup. For every change in StudentCode (a unique code) we also need to remove the empty rows left by the previous transpose and repeat the process.

SemesterReportAllResultsCsvExport Generated - 2020-07-31_0715PM.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	StudentFirstName	StudentLastName	StudentCode	StudentYearLevel	StudentFormGroup	StudentHouseGroup	Subject	ClassCode	Teacher	AssessmentArea1	Result1	AssessmentArea2	Result2	AssessmentArea3	Result3	AssessmentArea4	Result4	AssessmentArea5	Result5	AssessmentArea6	Result6
2	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Uses personal strengths to prepare for complex learning tasks	Very Good	Uses empathy to understand and accept others	Outstanding	Respects learning environment	Consistently	Collaborates with students and teachers	Outstanding	Participates in online meetings	Mostly	Seeks clarification of tasks	At Times
3	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith
4	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith
5	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith
6	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith
7	Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith
SemesterReportAllResultsCsvExpo

In the end, we should have:

Student1Name Student1LastName Student1Code Student1YearLevel Student1Form Student1House Subject1 ClassCode Teacher Assessment1 Result1 Assessment2 Result2 Assessment3 Result3 ..... Assessment6 Result6 Subject2 Classcode2 Teacher2 Assessment7 Result7....

Student2Name Student2LastName Student2Code Student1YearLevel Student2Form Student2House Subject1 ClassCode Teacher Assessment1 Result1 Assessment2 Result2 Assessment3 Result3 ..... Assessment6 Result6 Subject2 Classcode2 Teacher2 Assessment7 Result7....

I was thinking that we should be able to count the results for a subject, transpose the subject results, remove the rows, repeat until end, then if change in StudentCode, count subject assessments, tranposes, remove rows... so forth and so on. I understand the logic of what I want to do, I just dont have the VB coding skills to write the code.

I hope this helps. Thanks in advance - I really appreciate anything that we enable me to do this.

Andrew

 

[sandy666]

maybe

StudentFirstName	StudentLastName	StudentCode	StudentYearLevel	StudentFormGroup	StudentHouseGroup	Subject	ClassCode	Teacher	AssessmentArea.1	Result.1	AssessmentArea.2	Result.2	AssessmentArea.3	Result.3	AssessmentArea.4	Result.4	AssessmentArea.5	Result.5	AssessmentArea.6	Result.6	AssessmentArea.7	Result.7	AssessmentArea.8	Result.8	AssessmentArea.9
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Uses personal strengths to prepare for complex learning tasks	Very Good	Uses empathy to understand and accept others	Outstanding	Respects learning environment	Consistently	Collaborates with students and teachers	Outstanding	Participates in online meetings	Mostly	Seeks clarification of tasks	At Times	Accesses resources on learning management systems	Mostly	Effectively uses digital resources (laptop	Mostly	school software etc.) for learning
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Mr Jones	Uses personal strengths to prepare for complex learning tasks	Excellent	On time	Mostly	Accepts the responsibility associated with independence	Very Good	Ready for learning	Consistently	Uses self-reflection to build confidence and adaptability	Excellent
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Ms Robinson	Equipped for Class	Consistently	Uses empathy to understand and accept others	Excellent	Respects learning environment	Consistently	Collaborates with students and teachers	Excellent

 

[AndrewNic]

Hey Sandy666,

That would work. As long as the column headers for AssessmentArea and AssessmentResult related as there is likely to be upto 36 assessments and results for each student.

Is that possible?

 

[sandy666]

as you can see Assessment9 doesn't have Result9 (should have?)

note: refresh thread, I changed table

btw. my solution doesn't work on Mac

 

[AndrewNic]

It should - in the excel sheet, each student can have up to 6 subjects with 6 assessment areas and 6 assessment results.

Changes make perfect sense.

 

[sandy666]

give me a sec, i'll try to find what i lost ?

 

[sandy666]

see if this is ok for you

StudentFirstName	StudentLastName	StudentCode	StudentYearLevel	StudentFormGroup	StudentHouseGroup	Subject	ClassCode	Teacher	AssessmentArea.1	Result.1	AssessmentArea.2	Result.2	AssessmentArea.3	Result.3	AssessmentArea.4	Result.4	AssessmentArea.5	Result.5	AssessmentArea.6	Result.6	AssessmentArea.7	Result.7	AssessmentArea.8	Result.8
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Uses personal strengths to prepare for complex learning tasks	Very Good	Uses empathy to understand and accept others	Outstanding	Respects learning environment	Consistently	Collaborates with students and teachers	Outstanding	Participates in online meetings	Mostly	Seeks clarification of tasks	At Times	Accesses resources on learning management systems	Mostly	Effectively uses digital resources (laptop, school software etc.) for learning	Mostly
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Mr Jones	Uses personal strengths to prepare for complex learning tasks	Excellent	On time	Mostly	Accepts the responsibility associated with independence	Very Good	Ready for learning	Consistently	Uses self-reflection to build confidence and adaptability	Excellent
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Ms Robinson	Equipped for Class	Consistently	Uses empathy to understand and accept others	Excellent	Respects learning environment	Consistently	Collaborates with students and teachers	Excellent

 

[AndrewNic]

ah... after the 6th result for English it needs to add the results for maths to the same row:

StudentName StudentLastName ... Subject ClassCode Teacher AssessArea.1 Result.1 ... AssessArea.6 Result.6 Subject2 ClassCode2 Teache2 AssessArea.7 Result.7 AssessArea.8 Result.8 ...

Detect change in StudentCode

Student2Name Student2LastName ... and so on

I hope that makes sense?