Move data from once cell to another cell on the same worksheet

[alessio971]

Hello,

I have seen many posts requesting help to move data between worksheet but nothing about cell to cell within the same worksheet.
I tried to amend answers suggested on other posts but I don't have enough excel knowledge to do so.

Here is a bit of background to my problem and what I am trying to do.
I work for a public school where student's attendance and attending classes on time is very very important. To keep track of student's attendance each student has been provided with a card to be swiped before entering a class. Every day I receive a txt file with an ID number (student) and times (entry to each class)
I copy this data to excel, and it looks like this

a	b	c	d	e	f	g	h
rxy0022	8	9	10.10	11.04	12	1.08	2.20
rxy0023	8	9.02	10	11.14	12	1.10	2

What I'd like to do, is to move the data for each student to a specific column in the same row as follows:
if value under column 'b' was 1, the data (1) will be moved to column AA1
if value under column 'b' was 2, the data (1) will be moved to column AA2
if value under column 'b' was 3, the data (1) will be moved to column AA3
in this case the value under column 'b' is 8, therefore the data (8) will be moved to column AA8
when values have decimals like column 'd' = 10.10, the data will be moved to column AA10

Data under column 'g' and 'h' are for the afternoon classes.
The data should be moved as per the other example but to column BA1 if data is 1, BA2 if data is 2 ... and so on.

I hope I was able to clearly explain my problem. If I was not, please do ask for clarification.

Thank you in advance for your help.

Kind Regards
Alex

[patel45]

attach a sample file with before and after table

[alessio971]

Hello,

I have attached a sample file.

sample data.xlsx

Thank you

[alessio971]

Sorry please see updated xls file 'sample data v2'
sample data v2.xlsx

Regards

[Mowgli]

Alessio,

Just wanting to check. Do you have a start time for first class and an end time for the last one? This would allow easier transfer of 1:00 as it would only occur once in the afternoon and the early morning would be ignored.

Also, would you like headings etc put in and highlighting of cells where a class is missed (red) or a class is attended late (yellow)?

[alessio971]

All morning classes start at 8am and end at 12nn. Afternoon classes start at 1pm and end by 3pm during winter and 5pm during summer.
No heading is required. As per formatting I usually use the conditional formatting tool. Where I can I like to do things myself.
If it is not too much of a pain, could you add some comments to the code so I can understand how the code works? For now I am only an admin assistant but learning new skills will help in the future.

Thank you

[Mowgli]

Alessio,

See if this does what you are wanting. It is not very neat and am sure plenty of more learned contributors on this forum could come up with a more elegant code however it shoud be easy enough to understand and step through. If you have any questions about any part of it just let me know and I will try to answer as best I can.

This forum is a great place to learn, pretty much everything I have learnt to date about VBA has come from these pages so good luck

Mowgli

sample data v3.xlsm

[alessio971]

Mowgli,

Thank you so much for your help. The code does exactly what I needed.
Now I'll be able to provide teachers / mentors a report by the end of the day so any issues can be address the very next day. I know this will make a big difference and in the long run will help all students.

Thank you also for the detailed comments. I can't wait to read the code and try to understand how it works.

Regards

[Mowgli]

Glad to be of help.

Don't forget to mark your thread SOLVED if you are happy with the solution!

[alessio971]

Yes *very* happy.

Thank you.