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]

Math is aligned to Jones and Robinson not to Smith so how I can align Math to Smith if it is not exist there? (this is Excel not freestyle )
I didn't change any value
would be better if you copy table from post#9 , prepare and post here what you want to achieve

 

[AndrewNic]

Hey sandy666,
Sorry - not trying to be difficult. I understand that is a complex operation.
The transpose is linked to the StudentCode. Hopefully, this makes it a little clearer:

From this…
StudentFirstName	StudentLastName	StudentCode	StudentYearLevel	StudentFormGroup	StudentHouseGroup	Subject	ClassCode	Teacher	AssessmentArea	Result
Firstname	StudentLastName	ZBB0014	Year 8	P3M	MELBA	English	BENG12E	Mr Smith	Uses personal strengths to prepare for complex learning tasks	Very Good
Firstname	StudentLastName	ZBB0014	Year 8	P3M	MELBA	English	BENG12E	Mr Smith	Effectively uses digital resources (laptop, school software etc.) for learning	Mostly
Firstname	StudentLastName	ZBB0014	Year 8	P3M	MELBA	Maths	BMAT11B	Mr Jones	Uses personal strengths to prepare for complex learning tasks	Excellent
Firstname	StudentLastName	ZBB0014	Year 8	P3M	MELBA	PE	BPE4S6A	Mr Blue	Uses personal strengths to prepare for complex learning tasks	Very Good
Firstname	StudentLastName	ZBB0014	Year 8	P3M	MELBA	PE	BPE4S6A	Mr Blue	On time	Consistently
Firstname	StudentLastName	ZBB0014	Year 8	P3M	MELBA	Technology	BTEC5S4B	Ms Green	Uses personal strengths to prepare for complex learning tasks	Very Good
Firstname	StudentLastName	AZB0012	Year 7	P2N	LAKESIDE	English	7ENGC	Mr Yellow	Uses personal strengths to complete learning tasks	Excellent
Firstname	StudentLastName	AZB0012	Year 7	P2N	LAKESIDE	Health	7PPEC	Ms Orange	Uses personal strengths to complete learning tasks	Outstanding
Firstname	StudentLastName	AZB0012	Year 7	P2N	LAKESIDE	Humanities	7GLBC	Ms Orange	Uses personal strengths to complete learning tasks	Outstanding
To this…			removed			transposed to the end of each students first line
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	Result.8	Subject2	ClassCode2	Teacher2	AssessmentArea.7	Result.7	AssessmentArea.8	Result.8	AssessmentArea.9	Result.9	AssessmentArea.10	Result.10	AssessmentArea.11	Result.11	AssessmentArea.12	Result.12	Subject3	ClassCode3	Teacher3	AssessmentArea.13	Result.13	AssessmentArea.14	Result.14	AssessmentArea.15	Result.15
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	Mostly	Maths - Get the Basics B	BMAT11B	Mr Jones	Accesses resources on learning management systems	Mostly	Effectively uses digital resources (laptop, school software etc.) for learning	Mostly	Accepts the responsibility associated with independence	Very Good	Ready for learning	Consistently	Uses self-reflection to build confidence and adaptability	Excellent	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

 

[sandy666]

I think you should post source table and type: this is representative example of source data
because your data is changed from post to post
then post expected result from posted representative source data

how you got this from original subject?

Subject
English
English
Maths
PE
PE
Technology
English
Health
Humanities
Subject
English - Forging Ahead B

 

[AndrewNic]

Appreciate you patience sandy666. I’ll look into doing what you suggested.

 

[sandy666]

?

 

[AndrewNic]

Does it matter if the cell data is all different? Wouldn't a VBA macro function based on column position?

 

[sandy666]

I don't use vba but Power Query so I don't know if it matters for vba

anyway representative source data and expected result will be appreciated

 

[sandy666]

this is an example only:

source data

StudentFirstName	StudentLastName	StudentCode	StudentYearLevel	StudentFormGroup	StudentHouseGroup	Subject	ClassCode	Teacher	AssessmentArea	Result
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
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Uses empathy to understand and accept others	Outstanding
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Respects learning environment	Consistently
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Collaborates with students and teachers	Outstanding
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Participates in online meetings	Mostly
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Seeks clarification of tasks	At Times
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	English - Forging Ahead B	BENG12E	Mr Smith	Accesses resources on learning management systems	Mostly
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
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
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Mr Jones	On time	Mostly
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Mr Jones	Accepts the responsibility associated with independence	Very Good
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Mr Jones	Ready for learning	Consistently
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Mr Jones	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
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Ms Robinson	Uses empathy to understand and accept others	Excellent
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Ms Robinson	Respects learning environment	Consistently
Firstname	StudentLastName	ABB0014	Year 8	P3M	MELBA	Maths - Get the Basics B	BMAT11B	Ms Robinson	Collaborates with students and teachers	Excellent

Expected result

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]

ok... I need a VBA macro that will help me reorganise the data in my spreadsheet (CSV) so that:

1. After every change in subject, the subject, teacher, classcode, assessments and results are added to that student's first row of appearance.
2. The duplicate and empty cells that remain from transposing the subject data are removed and the remaining data is moved up
3. After every change in the StudentCode the loop returns to step 1.

Constant strings are StudentCode and ClassCode

The column headers are:

StudentFirstName	StudentLastName	StudentCode	StudentYearLevel	StudentFormGroup	StudentHouseGroup	Subject	ClassCode	Teacher	AssessmentArea	Result

Here is an example of what I'm after:

From this…
StudentFirstName	StudentLastName	StudentCode	StudentYearLevel	StudentFormGroup	StudentHouseGroup	Subject	ClassCode	Teacher	AssessmentArea	Result
Firstname	StudentLastName	ABC123	StudentYearLevel	StudentFormGroup	StudentHouseGroup	subject.1	ClassCode	Teacher1	AssessmentArea.1	Result.1
Firstname	StudentLastName	ABC123	StudentYearLevel	StudentFormGroup	StudentHouseGroup	subject.1	ClassCode	Teacher1	AssessmentArea.2	Result.2
Firstname	StudentLastName	ABC123	StudentYearLevel	StudentFormGroup	StudentHouseGroup	subject.1	ClassCode	Teacher1	AssessmentArea.3	Result.3
Firstname	StudentLastName	ABC123	StudentYearLevel	StudentFormGroup	StudentHouseGroup	subject.2	ClassCode	Teacher2	AssessmentArea.4	Result.4
Firstname	StudentLastName	ABC123	StudentYearLevel	StudentFormGroup	StudentHouseGroup	subject.2	ClassCode	Teacher2	AssessmentArea.5	Result.5
Firstname	StudentLastName	ABC123	StudentYearLevel	StudentFormGroup	StudentHouseGroup	subject.2	ClassCode	Teacher2	AssessmentArea.6	Result.6
Firstname	StudentLastName	ABC123	StudentYearLevel	StudentFormGroup	StudentHouseGroup	subject.3	ClassCode	Teacher3	AssessmentArea.7	Result.7
Firstname	StudentLastName	ABC123	StudentYearLevel	StudentFormGroup	StudentHouseGroup	subject.3	ClassCode	Teacher3	AssessmentArea.8	Result.8
Firstname	StudentLastName	ABC123	StudentYearLevel	StudentFormGroup	StudentHouseGroup	subject.3	ClassCode	Teacher3	AssessmentArea.9	Result.9
1	transposed to the end of each students first line based on ClassCode
2	Remove cells
3	Repeat based on StudentCode
To this…
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	Subject.2	ClassCode.2	Teacher.2	AssessmentArea.7	Result.7	AssessmentArea.8	Result.8	AssessmentArea.9	Result.9	AssessmentArea.10	Result.10	…
Firstname	StudentLastName	Studentcode	StudentYearLevel	StudentFormGroup	StudentHouseGroup	Subject	Classcode	Teacher.1	AssessmentArea.1	Result.1	AssessmentArea.2	Result.2	AssessmentArea.3	Result.3	AssessmentArea.4	Result.4	AssessmentArea.5	Result.5	AssessmentArea.6	Result.6	Subject.2	ClassCode.2	Teacher.2	AssessmentArea.7	Result.7	AssessmentArea.8	Result.8	AssessmentArea.9	Result.9	AssessmentArea.10	Result.10	…

Thanks

 

[sandy666]

sure, so you will need to wait for any master of vba
have a nice day my friend