Alternative copy and paste transpose

[alip43]

I have exported a report from our school management system which produces an excel spreadsheet of each child (UPN) and all the classes they attend.

Unfortunately, I can only get this info out in the format below

UPN Class
N382212098028 10x/En2
10x/Ma1
10x/Pe2
10x/Sc1
10A/Fr1
10B/Sc1
X382204498051 8P1/Dt
8PL1/Fr
8PL1/Gg
8PL1/Hi
8PL1/Sp
8WB/Ar
8WB/Dr
8WB/Is
8WB/It
8WB/Mu
8p/En2
8p/Ma2
8p/Pe2
8p/Sc2
CLS 8WB

However, to enable me to continue to my next step I need the information in the format as below:-

N382212098028 10x/En2 10x/Ma1 10x/Pe2 10x/Sc1 etc . . .
X382204498051 8P1/Dt 8PL1/Fr 8PL1/Gg 8PL1/Hi etc . . .

Note, that the number of classes is different for each student. Can you help please as I don't think the repetitive strain will cope with copy and transpose pasting 850+ students.

 

[GlennUK]

What will that next step be? It seems to me that you are trying to get away from a normalized list ( which is what you are almost starting with ), and that usually isn't a good idea.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

I have to strongly agree with Glenn. By denormalizing your data, you will severely hamper Excel's capabilities and thereby greatly increase the difficulty of achieving nearly any kind of analysis you wish to employ on your data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alip43]

Hi
The reason I am trying to get the info like this is as follows:-
In school we have a VLE. The people that run this VLE have instructions to enable us to take our students and classes out of SIMS (our student management system, along with our staff and classes and also a third .csv containing classes and subjects.
When we have produced these 3 .csv files in the format they stipulate we can then upload them to the VLE and when a student clicks on 'my courses' they will just get the info that is pertanent to them.
Complicated I know, but the instructions I have show a screen shot of how the data needs to look - I am beginning to think that their screenshot has been prepared using the copy, paste transpose method (there is only a handful of students on the screenshot) without thinking that this is not feasible for a whole school!

 

[SkipVought]

Fill the UPN values...
[tt]
UPN Class
N382212098028 10x/En2
N382212098028 10x/Ma1
N382212098028 10x/Pe2
N382212098028 10x/Sc1
N382212098028 10A/Fr1
N382212098028 10B/Sc1
X382204498051 8P1/Dt
X382204498051 8PL1/Fr
X382204498051 8PL1/Gg
X382204498051 8PL1/Hi
X382204498051 8PL1/Sp
X382204498051 8WB/Ar
X382204498051 8WB/Dr
X382204498051 8WB/Is
X382204498051 8WB/It
X382204498051 8WB/Mu
X382204498051 8p/En2
X382204498051 8p/Ma2
X382204498051 8p/Pe2
X382204498051 8p/Sc2
[/tt]
Name your ranges using the headings.

Column c formula to number each UPN group
[tt]
=IF(A2=A1,C1+1,1)
[/tt]
Column D formula for the UPN
[tt]
=IF(C2=1,A2,"")
[/tt]
and copy down
Columns E and to right formula
[tt]
=IF(COUNTIF(UPN,$A2)+4<COLUMN(),"",(INDEX(OFFSET($B$1,MATCH($A2,UPN,0)+$C2-1+COLUMN()-5,0,COUNTIF(UPN,$A2),1),$C2,1)))
[/tt]
Here's what mine looks like...
[tt]
UPN Class FilterHERE
N382212098028 10x/En2 1 N382212098028 10x/En2 10x/Ma1 10x/Pe2 10x/Sc1 10A/Fr1 10B/Sc1
N382212098028 10x/Ma1 2 10x/Pe2 10x/Sc1 10A/Fr1 10B/Sc1 8P1/Dt 8PL1/Fr
N382212098028 10x/Pe2 3 10A/Fr1 10B/Sc1 8P1/Dt 8PL1/Fr 8PL1/Gg 8PL1/Hi
N382212098028 10x/Sc1 4 8P1/Dt 8PL1/Fr 8PL1/Gg 8PL1/Hi 8PL1/Sp 8WB/Ar
N382212098028 10A/Fr1 5 8PL1/Gg 8PL1/Hi 8PL1/Sp 8WB/Ar 8WB/Dr 8WB/Is
N382212098028 10B/Sc1 6 8PL1/Sp 8WB/Ar 8WB/Dr 8WB/Is 8WB/It 8WB/Mu
X382204498051 8P1/Dt 1 X382204498051 8P1/Dt 8PL1/Fr 8PL1/Gg 8PL1/Hi 8PL1/Sp 8WB/Ar 8WB/Dr 8WB/Is 8WB/It 8WB/Mu 8p/En2 8p/Ma2 8p/Pe2 8p/Sc2
X382204498051 8PL1/Fr 2 8PL1/Gg 8PL1/Hi 8PL1/Sp 8WB/Ar 8WB/Dr 8WB/Is 8WB/It 8WB/Mu 8p/En2 8p/Ma2 8p/Pe2 8p/Sc2 0 0
X382204498051 8PL1/Gg 3 8PL1/Sp 8WB/Ar 8WB/Dr 8WB/Is 8WB/It 8WB/Mu 8p/En2 8p/Ma2 8p/Pe2 8p/Sc2 0 0 0 0
X382204498051 8PL1/Hi 4 8WB/Dr 8WB/Is 8WB/It 8WB/Mu 8p/En2 8p/Ma2 8p/Pe2 8p/Sc2 0 0 0 0 0 0
X382204498051 8PL1/Sp 5 8WB/It 8WB/Mu 8p/En2 8p/Ma2 8p/Pe2 8p/Sc2 0 0 0 0 0 0 0 0
X382204498051 8WB/Ar 6 8p/En2 8p/Ma2 8p/Pe2 8p/Sc2 0 0 0 0 0 0 0 0 0 0
X382204498051 8WB/Dr 7 8p/Pe2 8p/Sc2 0 0 0 0 0 0 0 0 0 0 0 0
X382204498051 8WB/Is 8 0 0 0 0 0 0 0 0 0 0 0 0 0 0
X382204498051 8WB/It 9 0 0 0 0 0 0 0 0 0 0 0 0 0 0
X382204498051 8WB/Mu 10 0 0 0 0 0 0 0 0 0 0 0 0 0 0
X382204498051 8p/En2 11 0 0 0 0 0 0 0 0 0 0 0 0 0 0
X382204498051 8p/Ma2 12 0 0 0 0 0 0 0 0 0 0 0 0 0 0
X382204498051 8p/Pe2 13 0 0 0 0 0 0 0 0 0 0 0 0 0 0
X382204498051 8p/Sc2 14 0 0 0 0 0 0 0 0 0 0 0 0 0 0
[/tt]
AutoFilter on COlumn D to display Non Blanks.




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alip43]

Works like a dream Skip - thanks so much I would never have been able to work it out.
Regards
Alison