Formatting an imported CSV file.

[Ramballah]

Hey all,
I have imported an CSV file and now i get this:

Now this has all the information i want but theres too much information i wanna keep...
So in the end i want it to look like this in a different sheet (master sheet)

Now maybe you notice that i left out the SLB Individueel class. thats because its just a break. u only have to go there if u get personally invited by a teacher. Uhm im not sure if its even possible for it to look like this through macro's or formulas? But basically the concept is. I'll be uploading a new CSV file every week and it will make 5 new columns like this so i can just hide these 5 columns. So i can keep the current week here but still save the old weeks. Now if u got questions please ask me. I also give you the workbook here
https://www.dropbox.com/s/swqdqoa8ltm01w1/Book1.xlsx?dl=0

 

[sandy666]

It will be good if you learn PowerQuery It will not hurt or even help now and in the future.

with an alternative view it is simpler

PowerQuery and PivotTable (automated):

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

28/01/2019​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

29/01/2019​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

30/01/2019​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

31/01/2019​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

01/02/2019​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

28/04/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BE[/td][td=bgcolor:#DDEBF7]FABV[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]BE[/td][td=bgcolor:#DDEBF7]DU[/td][td=bgcolor:#DDEBF7]Excel[/td][td=bgcolor:#DDEBF7]FABV[/td][td=bgcolor:#DDEBF7]LL[/td][td=bgcolor:#DDEBF7]SLB individueel[/td][td=bgcolor:#DDEBF7]BA[/td][td=bgcolor:#DDEBF7]DU[/td][td=bgcolor:#DDEBF7]EN[/td][td=bgcolor:#DDEBF7]Excel[/td][td=bgcolor:#DDEBF7]KD Ondernemerschap[/td][td=bgcolor:#DDEBF7]NE[/td][td=bgcolor:#DDEBF7]BA[/td][td=bgcolor:#DDEBF7]BE[/td][td=bgcolor:#DDEBF7]EN[/td][td=bgcolor:#DDEBF7]FABV[/td][td=bgcolor:#DDEBF7]NE[/td][td=bgcolor:#DDEBF7]BA[/td][td=bgcolor:#DDEBF7]BU[/td][td=bgcolor:#DDEBF7]FABV[/td][td=bgcolor:#DDEBF7]KD Ondernemerschap[/td][td=bgcolor:#DDEBF7]RV[/td][td=bgcolor:#DDEBF7]SLB individueel[/td][td=bgcolor:#DDEBF7]TEST[/td][td=bgcolor:#DDEBF7]TEST2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]FW165[/td][td=bgcolor:#DDEBF7]FW164[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]FW165[/td][td=bgcolor:#DDEBF7]FW122[/td][td=bgcolor:#DDEBF7]FW162[/td][td=bgcolor:#DDEBF7]FW162[/td][td=bgcolor:#DDEBF7]FW166[/td][td=bgcolor:#DDEBF7]FW156[/td][td=bgcolor:#DDEBF7]FW161[/td][td=bgcolor:#DDEBF7]FW117[/td][td=bgcolor:#DDEBF7]FW116[/td][td=bgcolor:#DDEBF7]FW157[/td][td=bgcolor:#DDEBF7]FW161[/td][td=bgcolor:#DDEBF7]FW165[/td][td=bgcolor:#DDEBF7]FW161[/td][td=bgcolor:#DDEBF7]FW165[/td][td=bgcolor:#DDEBF7]FW116[/td][td=bgcolor:#DDEBF7]FW164[/td][td=bgcolor:#DDEBF7]FW165[/td][td=bgcolor:#DDEBF7]FW161[/td][td=bgcolor:#DDEBF7]FW167[/td][td=bgcolor:#DDEBF7]FW157[/td][td=bgcolor:#DDEBF7]FW166[/td][td=bgcolor:#DDEBF7]FW157[/td][td=bgcolor:#DDEBF7]FW156[/td][td=bgcolor:#DDEBF7]FW165[/td][td=bgcolor:#DDEBF7]FW165[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]wi1a[/td][td=bgcolor:#DDEBF7]pp04[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]wi1a[/td][td=bgcolor:#DDEBF7]bs19[/td][td=bgcolor:#DDEBF7]sb41[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]sl08[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]ho3b[/td][td=bgcolor:#DDEBF7]bs19[/td][td=bgcolor:#DDEBF7]jo2l[/td][td=bgcolor:#DDEBF7]sb41[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]iw99[/td][td=bgcolor:#DDEBF7]ho3b[/td][td=bgcolor:#DDEBF7]wi1a[/td][td=bgcolor:#DDEBF7]jo2l[/td][td=bgcolor:#DDEBF7]pp04[/td][td=bgcolor:#DDEBF7]iw99[/td][td=bgcolor:#DDEBF7]ho3b[/td][td=bgcolor:#DDEBF7]ve4a[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]ve3p[/td][td=bgcolor:#DDEBF7]wi1a[/td][td=bgcolor:#DDEBF7]wi1a[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

14:30:00​

[/td][td=bgcolor:#DDEBF7]

11:30:00​

[/td][td=bgcolor:#DDEBF7]

13:00:00​

[/td][td=bgcolor:#DDEBF7]

12:00:00​

[/td][td=bgcolor:#DDEBF7]

09:30:00​

[/td][td=bgcolor:#DDEBF7]

15:30:00​

[/td][td=bgcolor:#DDEBF7]

13:30:00​

[/td][td=bgcolor:#DDEBF7]

11:00:00​

[/td][td=bgcolor:#DDEBF7]

15:00:00​

[/td][td=bgcolor:#DDEBF7]

13:30:00​

[/td][td=bgcolor:#DDEBF7]

16:30:00​

[/td][td=bgcolor:#DDEBF7]

15:00:00​

[/td][td=bgcolor:#DDEBF7]

10:00:00​

[/td][td=bgcolor:#DDEBF7]

09:00:00​

[/td][td=bgcolor:#DDEBF7]

12:00:00​

[/td][td=bgcolor:#DDEBF7]

11:30:00​

[/td][td=bgcolor:#DDEBF7]

10:00:00​

[/td][td=bgcolor:#DDEBF7]

13:00:00​

[/td][td=bgcolor:#DDEBF7]

14:00:00​

[/td][td=bgcolor:#DDEBF7]

08:30:00​

[/td][td=bgcolor:#DDEBF7]

14:00:00​

[/td][td=bgcolor:#DDEBF7]

12:00:00​

[/td][td=bgcolor:#DDEBF7]

09:00:00​

[/td][td=bgcolor:#DDEBF7]

15:00:00​

[/td][td=bgcolor:#DDEBF7]

11:00:00​

[/td][td=bgcolor:#DDEBF7]

08:30:00​

[/td][td=bgcolor:#DDEBF7]

12:00:00​

[/td][td=bgcolor:#DDEBF7]

12:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

15:30:00​

[/td][td=bgcolor:#DDEBF7]

12:30:00​

[/td][td=bgcolor:#DDEBF7]

14:30:00​

[/td][td=bgcolor:#DDEBF7]

13:00:00​

[/td][td=bgcolor:#DDEBF7]

10:30:00​

[/td][td=bgcolor:#DDEBF7]

17:00:00​

[/td][td=bgcolor:#DDEBF7]

15:00:00​

[/td][td=bgcolor:#DDEBF7]

12:00:00​

[/td][td=bgcolor:#DDEBF7]

15:30:00​

[/td][td=bgcolor:#DDEBF7]

14:30:00​

[/td][td=bgcolor:#DDEBF7]

17:30:00​

[/td][td=bgcolor:#DDEBF7]

16:00:00​

[/td][td=bgcolor:#DDEBF7]

11:30:00​

[/td][td=bgcolor:#DDEBF7]

10:00:00​

[/td][td=bgcolor:#DDEBF7]

13:30:00​

[/td][td=bgcolor:#DDEBF7]

12:30:00​

[/td][td=bgcolor:#DDEBF7]

11:00:00​

[/td][td=bgcolor:#DDEBF7]

14:00:00​

[/td][td=bgcolor:#DDEBF7]

15:00:00​

[/td][td=bgcolor:#DDEBF7]

10:00:00​

[/td][td=bgcolor:#DDEBF7]

15:00:00​

[/td][td=bgcolor:#DDEBF7]

13:30:00​

[/td][td=bgcolor:#DDEBF7]

10:30:00​

[/td][td=bgcolor:#DDEBF7]

16:00:00​

[/td][td=bgcolor:#DDEBF7]

12:00:00​

[/td][td=bgcolor:#DDEBF7]

09:00:00​

[/td][td=bgcolor:#DDEBF7]

13:00:00​

[/td][td=bgcolor:#DDEBF7]

13:00:00​

[/td][/tr]
[/table]

 

[sandy666]

or like this

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]

28/01/2019​

[/td][td]BE[/td][td]FW165[/td][td]wi1a[/td][td]

14:30:00​

[/td][td]

15:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]FABV[/td][td]FW164[/td][td]pp04[/td][td]

11:30:00​

[/td][td]

12:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]ve3p[/td][td]

13:00:00​

[/td][td]

14:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

29/01/2019​

[/td][td]BE[/td][td]FW165[/td][td]wi1a[/td][td]

12:00:00​

[/td][td]

13:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]DU[/td][td]FW122[/td][td]bs19[/td][td]

09:30:00​

[/td][td]

10:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Excel[/td][td]FW162[/td][td]sb41[/td][td]

15:30:00​

[/td][td]

17:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]FABV[/td][td]FW162[/td][td]ve3p[/td][td]

13:30:00​

[/td][td]

15:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]LL[/td][td]FW166[/td][td]sl08[/td][td]

11:00:00​

[/td][td]

12:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]SLB individueel[/td][td]FW156[/td][td]ve3p[/td][td]

15:00:00​

[/td][td]

15:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

30/01/2019​

[/td][td]BA[/td][td]FW161[/td][td]ho3b[/td][td]

13:30:00​

[/td][td]

14:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]DU[/td][td]FW117[/td][td]bs19[/td][td]

16:30:00​

[/td][td]

17:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]EN[/td][td]FW116[/td][td]jo2l[/td][td]

15:00:00​

[/td][td]

16:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Excel[/td][td]FW157[/td][td]sb41[/td][td]

10:00:00​

[/td][td]

11:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]KD Ondernemerschap[/td][td]FW161[/td][td]ve3p[/td][td]

09:00:00​

[/td][td]

10:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]NE[/td][td]FW165[/td][td]iw99[/td][td]

12:00:00​

[/td][td]

13:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

31/01/2019​

[/td][td]BA[/td][td]FW161[/td][td]ho3b[/td][td]

11:30:00​

[/td][td]

12:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]BE[/td][td]FW165[/td][td]wi1a[/td][td]

10:00:00​

[/td][td]

11:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]EN[/td][td]FW116[/td][td]jo2l[/td][td]

13:00:00​

[/td][td]

14:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]FABV[/td][td]FW164[/td][td]pp04[/td][td]

14:00:00​

[/td][td]

15:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]NE[/td][td]FW165[/td][td]iw99[/td][td]

08:30:00​

[/td][td]

10:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

01/02/2019​

[/td][td]BA[/td][td]FW161[/td][td]ho3b[/td][td]

14:00:00​

[/td][td]

15:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]BU[/td][td]FW167[/td][td]ve4a[/td][td]

12:00:00​

[/td][td]

13:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]FABV[/td][td]FW157[/td][td]ve3p[/td][td]

09:00:00​

[/td][td]

10:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]KD Ondernemerschap[/td][td]FW166[/td][td]ve3p[/td][td]

15:00:00​

[/td][td]

16:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]RV[/td][td]FW157[/td][td]ve3p[/td][td]

11:00:00​

[/td][td]

12:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]SLB individueel[/td][td]FW156[/td][td]ve3p[/td][td]

08:30:00​

[/td][td]

09:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]TEST[/td][td]FW165[/td][td]wi1a[/td][td]

12:00:00​

[/td][td]

13:00:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

28/04/2019​

[/td][td]TEST2[/td][td]FW165[/td][td]wi1a[/td][td]

12:00:00​

[/td][td]

13:00:00​

[/td][/tr]
[/table]

 

[Ramballah]

I like the last version better, that looks like its easier to read off.. Can you teach me how you did that?

 

[sandy666]

here is an example for the last two: example alternative

to see steps in PowerQuery use Show Queries from Data tab then double click on table on the right side. It will open PowerQuery Editor and on the right side you'll see steps
You need prepare table first if you want it to use in PivotTable
After all you can simply use this table as source for PT and play with PT styles and options to get view like you want