rearrange data for a school timetables

[jadawl01]

Well
I am a total newbie, but I am wiling to learn
I have some raw data in a .ods file produced by a timetabling program
like this:

\1

I need data to be formated like this layout:
\1

Basically, I need to
1- pull the table's head (in red) from teacher column E

2- pull subject, student set, and room from and put them together into a single cell, according to day and period
fo example, data from row 2, (with A2=friday1 and B2=09:00) from row spreadsheet
would be put at C7 int the formatted timetable

3- in order to prepare data , I also need to check if the a cell in day column contain the number 2
if so transform period by adding 6
for example, for day tuesday2
tranform day tuesday2 period 08:00 into tuesday1 period 14:00
tranform day tuesday2 period 09:00 into into tuesday1 period 15:00
tranform day tuesday2 period 10:00 into into into tuesday1 period 16:00
and so on for other XXX2 days

I attached the .xsl file
and a wanted timetable layout
Hope I explained my problem clearely

[JBeaucaire]

Try this macro on your sheet, it will create and format the sheets as needed. Install these macros into a regular module.

Please Login or Register  to view this content.

How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

[jadawl01]

THank so much for help
will try it and came back to you with any feedback

[jadawl01]

THanks again
the macro is working fine except for some cases:
for example: techer ar2 has a course on friday, period 17:00
Acording to initial data, this course should be on friday, period 09:00
Last question: cold you please comment the macro , so I can understand what is going ?
This way, I can learn more about macros an VB

[JBeaucaire]

the macro is working fine except for some cases:
for example: teacher ar2 has a course on friday, period 17:00
Acording to initial data, this course should be on friday, period 09:00

Of course I played around a bit with your data as I was constructing the macro. I see some of the numbers I changed didn't get changed back.

That's OK, though, you're running this macro on YOUR sheet, not mine, right?

Last question: cold you please comment the macro , so I can understand what is going ?
This way, I can learn more about macros an VB

Sure!

Please Login or Register  to view this content.

[jadawl01]

Thanks for the comment
I really appreciated it
I am also reading tutorials on Macros for biginners
But I didnot figure out how to put all teachers tables in ONE sheet, one after the other separated by some blank space or free rows

[JBeaucaire]

Would you want that in addition to the extra sheets or instead of? We can add a MASTER listing at the end, pretty easy.

[JBeaucaire]

Here's a new routine added separately. You can leave the command I added at the end of the main routine and it will run it automatically, or you can remove it from there and run it manually when you want.

Please Login or Register  to view this content.

[jadawl01]

Well
It worked for me
I add it as a separate macro
I am making progress !! Now I am able to understand what is going
First, we test if a sheet named "MasterList" already exist (but connont really understand:MasterList!A1 )

Please Login or Register  to view this content.

if not, create it:

Please Login or Register  to view this content.

otherwise, activate it, and empty all cells content

Please Login or Register  to view this content.

We only want to copy sheet with name different from feuil1, feuil2 and farabitimetables

Please Login or Register  to view this content.

then, we copy all sheets, one by one to our Masterlist sheet

Please Login or Register  to view this content.

we left an empty row :

Please Login or Register  to view this content.

I changed it 3 rows apart by : NR = NR + 11
From other tutorials on internet I understand that you refers to range by to left and bottom right corners !!!
But I cant really understand this reference:
Range("A" & NR)

Lastely, I would like to merge adjcent cells in a row, if they have same content regarding column C (students set)

this:
http://img401.imageshack.us/img401/5168/before.png
to be formatted like this
http://img523.imageshack.us/img523/3314/afterx.png

[JBeaucaire]

Again, run this a separate macro, or add a line to the bottom of the main macro to activate it automatically as part of that sequence.

Please Login or Register  to view this content.

In the previous macro, the Evaluate(ISREF... line is simply Excel running a worksheet formula in memory instead of a cell to see what the answer is. In VBA, this is a very simple way to see a sheet exists by trying to look at cell A1 on that sheet.

The NR variable is the "next row" numerically stored. The first time through the loop, that translates into "A1", the next time through "A10", etc.

[jadawl01]

hanks so much for help, I really appreciate it
Again, worked like a charm !
I only noticed that if I run the last macro from MasterList sheet, it formats the active range only (one teacher's timetable)
If I run it from farabitimetable sheet (original data sheet) it foramts all teacher's timetables
But this is not a real issue at all.

Now, I will try to format sheet Right To Left (arabic), so I will edit all the tree macros, then I will come back to you with results
Hope I can accheive it

[jadawl01]

Before formatting timetable Right To Left, I tried modifying first macro to acheive directly the result obtained by secod macro without creating a sheet for every single teacher

please, take a look a the code bellow
I didnot excute it, because, I am sure it is weired somewhere
I surely did some idiot edits !!
Basically, I "imitated" your code, mixed with some knowledge coming from my intensive readings in the last 3 days
I created a new sheet called Masterlist2, I removed all lines which was used to create a new sheet for every teacher
then I added a variable "s" to make an offset between each teacher's timetable in the same sheet

Please Login or Register  to view this content.

[JBeaucaire]

I don't know what you mean by right to left. But if you want to "skip" the creation of individual sheets, the logic of the entire set of macros would actually have to be redone.

That's not necessary. Leave them as separate, let them create, merge, format...and now delete the unneeded extra sheets.

This new set of macros includes the new one and ran as a whole in 2 seconds on the sample sheet you posted.

Please Login or Register  to view this content.

[jadawl01]

Yes
you are right: this way our macros will be modular, so I can use "modules" separatelly or in conjonction

About Right To Left
I mean timetable should be read frm Right To Left like this
-17:00 --16:00 -- 15:00 -- 14:00 -- 11:00 -- 10:00 -- 09:00 -- 08:00 -
---___-----___-------___-----___------___-------___--------___------___----Mnday
---___-----___-------___-----___------___-------___-------___-------___----Tuesday

and so on ..
( later, timetables will be translated into Arabic language, which is Right To Left)
But I think I can do it myself simply by inverting references to Columns here:

Please Login or Register  to view this content.

and so on...

and also in FormatSheet subroutine..
Here is the changes:

Please Login or Register  to view this content.

and so on ...

am I right ?

[JBeaucaire]

Yes, you are on the right track, but imagine if you had simply presented the "output" in that desired format, you wouldn't have to do any of that, I could have just given you what you needed.

You'll have to also examine the "copy" code and make sure it encompasses the new range properly.

And the MERGE code and make sure it is processing the correct columns still, if those have changed overall position in any way.

Lots of extra work.

[jadawl01]

Lots of extra work.

Yes
But, for a newbie, extra work means learning
So thanks a lot for helping me to learn and to solve my problem !!
So here we go (tested and working )
the first Macro

Please Login or Register  to view this content.

Second Macro needs no changes !!

The third Macro:

Please Login or Register  to view this content.

[JBeaucaire]

Very nice indeed. Well done.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

[jadawl01]

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

Done !

If that takes care of your need,

Nearly..
except for one thing, I tried to do it myself
I tried a loop to copy rows from output made by third Macro
with no succes
I want to output data based on days, on day per sheet
Each row represents a teacher
like this
http://img268.imageshack.us/img268/2452/underdays.png

[shg]

I would change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

... because

1. It's readable

2. It doesn't have gaps between cases like the first code

3. It caters to dates/times that include a date

4. The added case allows testing for invalid data

[jadawl01]

Thanks a lot shg
I will try it, and let you now

Again a big thanks to JBeaucaire
I modified the second macro, so I can create a worksheet containing the day's data like this
http://img268.imageshack.us/img268/2452/underdays.png

it doesnot loop to automatically create all worksheet days, I have to modify the row reference each time the produce the desired day's shedule
Here is the Macro
all credits go to JBeaucaire

Please Login or Register  to view this content.

Here is the result's screenshoot:
http://img35.imageshack.us/img35/272...ayschedule.png

[jadawl01]

Now, I can declare my problem as solved
the output from first macro was very usefull
I can use it to display data in defferent format

Last question: can I publish thoses macros on other sites, and what credit informations should I use? simply a link back to this thread ?

Thanks so much for this unvaluable help