Data Conversion

[Matthew12]

I'm hoping somebody here can help me with my problem as im well and truly fed up with it!

I have thousands of rows like in the example image below. This record summarises the attendance for a course of treatment.
At system level this record is linked to the patient attending and the worker and venue facilitating. These fields are important as we need
to be able generate case studies for clients, monitor worker performance and produce reports for our partners - all of which include attendance info.
(it actually goes up to 10 sessions in case it makes a difference)

I need to convert the data above so that i can aggreagate each booked session to a single date field and still be able to filter at row level by clients,
workers and venues. The below table would be ideal, with each session having its own record with the relevant client, worker and venue data.

Is there anyway this can be achieved? Big thanks for any help you can provide! If you require further information please just ask.

 

[Joe4]

You are welcome!
Glad we got it to work for you.

 

[Matthew12]

You are welcome!
Glad we got it to work for you.

I just did a test on the actual data source and there seems to be an issue if there are columns the session attendance cols have ended. I didnt include them in the original example due to confidentiality. Is there anyway we can get it to stop and move onto the next row after the session 10 date field has been passed?

Thanks

 

[Joe4]

if there are columns the session attendance cols have ended

I am not sure I follow what you are trying to say there. Can you make up a simple example to show us what you mean?

 

[Matthew12]

I just did a test on the actual data source and there seems to be an issue if there are columns the session attendance cols have ended. I didnt include them in the original example due to confidentiality. Is there anyway we can get it to stop and move onto the next row after the session 10 date field has been passed?

Thanks

That should read

I just did a test on the actual data source and there seems to be an issue if there are columns after the session columns have ended. I didnt include them in the original example due to confidentiality. Is there anyway we can get it to stop and move onto the next row after the session 10 date field has been passed? Session 10 is column AT, so i would need everything after to be ignored.

Thanks again

 

[Joe4]

Does every single record end at column AT then?+

If so, change this line:

Do Until srcWS.Cells(myRow, myCol) = ""

to this:

Do Until myCol > 46

 

[Matthew12]

Does every single record end at column AT then?+

If so, change this line:

Do Until srcWS.Cells(myRow, myCol) = ""

to this:

Do Until myCol > 46

That works perfectly! Thanks again!

 

[Rick Rothstein]

Session 10 is column AT

How is Session 10 ending at Column AT? You showed Session 1 starting at Column D and that each session has 2 fields to it (Session Outcome and Session Date)... by my calculations, the date for Session 10 would end at Column W.