Adjusting information with VBA

[tos]

Hi all

Here I have a sample of a large file I got for people taking a certain class.

Name Class 1 Class 2 Class 3 Class 4 Class 5
George MO1 TU3
Amy MO2 TU4 WE3
Michael MO7
MOrk MO8 WE1
Eric MO16
James MO17 TU3 FI1 SA1 MO2
Paul MO18

Now what I would like excel to do is that for example for Amy (she takes 3 classes) 2 extra rows are inserted and copied so that the table looks like this:

Name Class 1 Class 2 Class 3 Class 4 Class 5
George MO1 TU3
Amy MO2 TU4 WE3
Amy TU4 TU4 WE3
Amy WE3 TU4 WE3
Michael MO7
MOrk MO8 WE1
Eric MO16
James MO17 TU3 FI1 SA1 MO2
Paul MO18

Like this I can eventually delete the other classes and only keep column class 1.

Is it possible to let excel run through the file, see how much classes a person takes and then put these in the first column?

Thanks in advance

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[tos]

See file attached. I already tried some things in vba but its not working properly. I think it can be more efficient

[jaslake]

Hi tos
This code is in the attached

Please Login or Register  to view this content.

Let me know of issues.

[tos]

Thanks! that works fine. Could you confirm me if I was close with what I wrote?
Is it possible with "if" functions?

[watersev]

hi, tos, alternative much faster option, run code "test"

[jaslake]

Hi tos

This is what I envision the code would look like using "If" statements

Please Login or Register  to view this content.

Of course you'll still need to move the "Class" stuff around but your original code didn't do that so I'll leave you to play with an approach to do that.