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

AndrewNic

New Member
Joined
Aug 2, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:

StudentCodeFirstLastYearFormHouseSubjectCodeTeacherAssessmentResult
ABC123JohnSmithYear 8P3MMELBEnglishBENGJane SMITHOn TimeYes
ABC123JohnSmithYear 8P3MMELBEnglishBENGJane SMITHEquippedYes
CBA321ThomasJonesYear 7PN2NEWTMathsBMATTim APPLEOn TimeYes
CBA321ThomasJonesYear 7PN2NEWTEnglishBENGJane SMITHReady to learnSometimes

We need:
StudentCodeFirstLastYearFormHouseSubjectCodeTeacherAssessmentResultAssessmentResultSubjectTeacherAssessmentResult
ABC123JohnSmithYear 8P3MMELBEnglishENGBJane SMITHOn TimeYesEquippedYesMathsTim AppleOn TimeYes
CBAThomasJonesYear 7PN2NEWTMathsBMATTim APPLEOn TimeYesEquippedYesEnglishJane SMITHReady to LearnSometimes

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 I'm happy for a PowerQuery if it can result in the same outcome... placing each student's results from all of their classes on a single row.
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
your source data

StudentFirstNameStudentLastNameStudentCodeStudentYearLevelStudentFormGroupStudentHouseGroupSubjectClassCodeTeacherAssessmentAreaResult
FirstnameStudentLastNameABC123StudentYearLevelStudentFormGroupStudentHouseGroupsubject.1ClassCodeTeacher1AssessmentArea.1Result.1
FirstnameStudentLastNameABC123StudentYearLevelStudentFormGroupStudentHouseGroupsubject.1ClassCodeTeacher1AssessmentArea.2Result.2
FirstnameStudentLastNameABC123StudentYearLevelStudentFormGroupStudentHouseGroupsubject.1ClassCodeTeacher1AssessmentArea.3Result.3
FirstnameStudentLastNameABC123StudentYearLevelStudentFormGroupStudentHouseGroupsubject.2ClassCodeTeacher2AssessmentArea.4Result.4
FirstnameStudentLastNameABC123StudentYearLevelStudentFormGroupStudentHouseGroupsubject.2ClassCodeTeacher2AssessmentArea.5Result.5
FirstnameStudentLastNameABC123StudentYearLevelStudentFormGroupStudentHouseGroupsubject.2ClassCodeTeacher2AssessmentArea.6Result.6
FirstnameStudentLastNameABC123StudentYearLevelStudentFormGroupStudentHouseGroupsubject.3ClassCodeTeacher3AssessmentArea.7Result.7
FirstnameStudentLastNameABC123StudentYearLevelStudentFormGroupStudentHouseGroupsubject.3ClassCodeTeacher3AssessmentArea.8Result.8
FirstnameStudentLastNameABC123StudentYearLevelStudentFormGroupStudentHouseGroupsubject.3ClassCodeTeacher3AssessmentArea.9Result.9


Your expected reultt (imho improper result because you've 3 different groups of subjects and teachers and not so many assessments and results . Expected result should be created from existing representative example but not guessing what will be if will be)

StudentFirstNameStudentLastNameStudentCodeStudentYearLevelStudentFormGroupStudentHouseGroupSubjectClassCodeTeacherAssessmentArea.1Result.1AssessmentArea.2Result.2AssessmentArea.3Result.3AssessmentArea.4Result.4AssessmentArea.5Result.5AssessmentArea.6Result.6Subject.2ClassCode.2Teacher.2AssessmentArea.7Result.7AssessmentArea.8Result.8AssessmentArea.9Result.9AssessmentArea.10Result.10
FirstnameStudentLastNameStudentcodeStudentYearLevelStudentFormGroupStudentHouseGroupSubjectClasscodeTeacher.1AssessmentArea.1Result.1AssessmentArea.2Result.2AssessmentArea.3Result.3AssessmentArea.4Result.4AssessmentArea.5Result.5AssessmentArea.6Result.6Subject.2ClassCode.2Teacher.2AssessmentArea.7Result.7AssessmentArea.8Result.8AssessmentArea.9Result.9AssessmentArea.10Result.10


so my result is (from your source data)

StudentFirstNameStudentLastNameStudentCodeStudentYearLevelStudentFormGroupStudentHouseGroupSubjectClassCodeTeacherAssessmentArea.1AssessmentArea.2AssessmentArea.3Result.1Result.2Result.3
FirstnameStudentLastNameABC123StudentYearLevelStudentFormGroupStudentHouseGroupsubject.1ClassCodeTeacher1AssessmentArea.1AssessmentArea.2AssessmentArea.3Result.1Result.2Result.3
FirstnameStudentLastNameABC123StudentYearLevelStudentFormGroupStudentHouseGroupsubject.2ClassCodeTeacher2AssessmentArea.4AssessmentArea.5AssessmentArea.6Result.4Result.5Result.6
FirstnameStudentLastNameABC123StudentYearLevelStudentFormGroupStudentHouseGroupsubject.3ClassCodeTeacher3AssessmentArea.7AssessmentArea.8AssessmentArea.9Result.7Result.8Result.9


or you want like this

subject1subject2subject3teacher1teacher2teacher3


all is possible but detailed explanation is required not general imagination
 
Last edited:
Upvote 0
DB to Report.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1StudentFirstNameStudentLastNameStudentCodeStudentYearLevelStudentFormGroupStudentHouseGroupSubjectClassCodeTeacherAssessmentAreaResult
2FirstnameStudentLastNameABB0014Year 8P3MMELBAEnglish - Forging Ahead BBENG12EMr SmithUses personal strengths to prepare for complex learning tasksVery Good
3FirstnameStudentLastNameABB0014Year 8P3MMELBAEnglish - Forging Ahead BBENG12EMr SmithUses empathy to understand and accept othersOutstanding
4FirstnameStudentLastNameABB0014Year 8P3MMELBAEnglish - Forging Ahead BBENG12EMr SmithRespects learning environmentConsistently
5FirstnameStudentLastNameABB0014Year 8P3MMELBAEnglish - Forging Ahead BBENG12EMr SmithCollaborates with students and teachersOutstanding
6FirstnameStudentLastNameABB0014Year 8P3MMELBAEnglish - Forging Ahead BBENG12EMr SmithParticipates in online meetingsMostly
7FirstnameStudentLastNameABB0014Year 8P3MMELBAEnglish - Forging Ahead BBENG12EMr SmithSeeks clarification of tasksAt Times
8FirstnameStudentLastNameABB0014Year 8P3MMELBAEnglish - Forging Ahead BBENG12EMr SmithAccesses resources on learning management systemsMostly
9FirstnameStudentLastNameABB0014Year 8P3MMELBAEnglish - Forging Ahead BBENG12EMr SmithEffectively uses digital resources (laptop, school software etc.) for learningMostly
10FirstnameStudentLastNameABB0014Year 8P3MMELBAMaths - Get the Basics BBMAT11BMr JonesUses personal strengths to prepare for complex learning tasksExcellent
11FirstnameStudentLastNameABB0014Year 8P3MMELBAMaths - Get the Basics BBMAT11BMr JonesOn timeMostly
12FirstnameStudentLastNameABB0014Year 8P3MMELBAMaths - Get the Basics BBMAT11BMr JonesAccepts the responsibility associated with independenceVery Good
13FirstnameStudentLastNameABB0014Year 8P3MMELBAMaths - Get the Basics BBMAT11BMr JonesReady for learningConsistently
14FirstnameStudentLastNameABB0014Year 8P3MMELBAMaths - Get the Basics BBMAT11BMr Jones Uses self-reflection to build confidence and adaptabilityExcellent
15FirstnameStudentLastNameABB0014Year 8P3MMELBAMaths - Get the Basics BBMAT11BMs RobinsonEquipped for ClassConsistently
16FirstnameStudentLastNameABB0014Year 8P3MMELBAMaths - Get the Basics BBMAT11BMs RobinsonUses empathy to understand and accept othersExcellent
17FirstnameStudentLastNameABB0014Year 8P3MMELBAMaths - Get the Basics BBMAT11BMs RobinsonRespects learning environmentConsistently
18FirstnameStudentLastNameABB0014Year 8P3MMELBAMaths - Get the Basics BBMAT11BMs RobinsonCollaborates with students and teachersExcellent
19
20
21
22StudentFirstNameStudentLastNameStudentCodeStudentYearLevelStudentFormGroupStudentHouseGroupSubjectClassCodeTeacherAssessmentArea 1Result 1AssessmentArea 2Result 2AssessmentArea 3Result 3AssessmentArea 4Result 4AssessmentArea 5Result 5AssessmentArea 6Result 6AssessmentArea 7Result 7AssessmentArea 8Result 8
23FirstnameStudentLastNameABB0014Year 8P3MMELBAEnglish - Forging Ahead BBENG12EMr SmithUses personal strengths to prepare for complex learning tasksVery GoodUses empathy to understand and accept othersOutstandingRespects learning environmentConsistentlyCollaborates with students and teachersOutstandingParticipates in online meetingsMostlySeeks clarification of tasksAt TimesAccesses resources on learning management systemsMostlyEffectively uses digital resources (laptop, school software etc.) for learningMostly
24FirstnameStudentLastNameABB0014Year 8P3MMELBAMaths - Get the Basics BBMAT11BMr JonesUses personal strengths to prepare for complex learning tasksExcellentOn timeMostlyAccepts the responsibility associated with independenceVery GoodReady for learningConsistently Uses self-reflection to build confidence and adaptabilityExcellent
25FirstnameStudentLastNameABB0014Year 8P3MMELBAMaths - Get the Basics BBMAT11BMs RobinsonEquipped for ClassConsistentlyUses empathy to understand and accept othersExcellentRespects learning environmentConsistentlyCollaborates with students and teachersExcellent
Sheet1


Select A1 and Create table.

Open Power Query/Get and Transform. Click on New Query.
Open blank query in the editor, launch Advanced Editor and paste in the following code.


Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grouped = Table.Group(Source, {"StudentFirstName", "StudentLastName", "StudentCode", "StudentYearLevel", "StudentFormGroup", "StudentHouseGroup", "Subject", "ClassCode", "Teacher"},
        {{"Col", each  let
        t1 =Table.UnpivotOtherColumns(Table.AddIndexColumn([[AssessmentArea],[Result]],"in",1),{"in"},"At","Va"),
        t2 =Table.CombineColumns(Table.TransformColumnTypes(t1,{{"in", type text}}),{"At","in"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Mer"),
        t3 =Table.Pivot(t2,List.Distinct(t2[Mer]), "Mer", "Va")
            in t3
        }}),
    #"Expanded Col" = Table.ExpandTableColumn(Grouped, "Col", List.Union(List.Transform(Grouped[Col],each Table.ColumnNames(_))))
in
    #"Expanded Col"
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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