VBA to extract values from grouped data and populate a new worksheet

Hi I am struggling to create the VBA code I need to perform a fairly complicated task.

I have an initial worksheet called 'Data_KPI4' which is in the format of below:

##See BEFORE worksheet attached##

So I need the VBA to perform the following steps:

• Iterate through the worksheet and group into separate sets of rows based on 'Episode Reference'. For example the first group for EP205143 would have 7 rows from the worksheet above.
• For each group search for the row that has 'Event Type' = 'Triage' and store the values in that row for 'Attendance Date', 'Episode Reference', 'PIN' & 'Start Date'. Ideally these will be written to a new worksheet as a new row in that worksheet.
• Also within that same group I need to create a list of dates appending all Appointment Dates in that group to the list and also append to this list all Attendance Dates in that group (which are for Event Types not equal to Triage - ie discount any Attendance Dates for Triage events). To then sort this list from oldest to newest dates and to take the earliest date in the list and add as 'First Offered Appointment Date' it to the row in the new worksheet that was populated in the previous step.
• To iterate through all groups creating new rows for every group in the new worksheet.
The final output should be a new worksheet called 'FinalData_KPI4' which looks like the following:

##See AFTER worksheet attached##

A bit of an ask but would be very grateful for any help with this as I am working in the health sector and this will help our data projections no end from the manual audit we currently have to do!

Thanks for having a go.

On running I get "Runtime error 5 - Invalid procedure call or argument" and it debugs to line j = z(a(i, 5))

Any ideas?

The original file is much larger than the test one I quoted above (it has over 700 rows) so that might be an issue.

I also just tried downloading your file and running Macro test in Excel 2013 and I get a different error "Runtime error type 13 - type mismatch" and on debug it refers to line a(i, 2) = CDate(a(i, 2))

Any ideas?

Thanks I suspect you are right about the system setting (not sure how I check that but we are in the Uk so use dd-mm-yyyy to my knowledge although the system locale may be set differently) but is there a typo in the fix line I think:

a(i, 2) = DateSerial(CLng(Right$(a(i, 2), 4)), CLng(Mid$(a(i, 2), 4, 2)), CLng(Left$(a(i, 2), 2)))

- as I still get error 13 and on that line now.

Just checked and my current system locale is English (United Kingdom) which has region settings for short date as dd/MM/yyyy and for long date dd MMMM yyyy.

if I copy and paste the rows from my original table over the top of the one you saved and sent me your sort works. That is good thanks.

Now I just need to work out how to upscale this for the main file of 700 or so rows.

I think you are right there are 4 or 5 blank Attendance Date cells for the Triage events. I there any way to build in a way of ignoring these groups entirely and not taking over a row for them to the new worksheet?

I think all rows should have a Triage event.

Hi

karedog's method is efficient.

I tried a different approach, using a helper-column and advanced-filter-extract to get the First Offered Appointment Date.
The assumption is that the First Offered Appointment Date is the one immediately following the Triage record.
I have added comments to the vba code to help you follow the method I used.
To get the First Appointent records, click the button on the sheet [FinalData_KPI4]

zeddy

Originally Posted by zzzeddy

Hi

karedog's method is efficient.

I tried a different approach, using a helper-column and advanced-filter-extract to get the First Offered Appointment Date.
The assumption is that the First Offered Appointment Date is the one immediately following the Triage record.
I have added comments to the vba code to help you follow the method I used.
To get the First Appointent records, click the button on the sheet [FinalData_KPI4]

zeddy

Hi I tried this for my example2 file below and it doesn't seem to run. Can you get it to run and provide the expected output as per the example2 file?

The assumption is that the First Offered Appointment Date is the one immediately following the Triage record - I am not sure this is correct as it needs to be the earliest date from a list of appointment dates and attended dates (not including attended date for Triage event) in that group. I am not sure if it will always be in date order in these 2 columns safer to create a list of the possible dates then select the earliest one.

Originally Posted by 83dons

• Also within that same group I need to create a list of dates appending all Appointment Dates in that group to the list and also append to this list all Attendance Dates in that group (which are for Event Types not equal to Triage - ie discount any Attendance Dates for Triage events). To then sort this list from oldest to newest dates and to take the earliest date in the list and add as 'First Offered Appointment Date' it to the row in the new worksheet that was populated in the previous step.

Hi, as per my original post. All Appointment Dates within that group and all Attendance Dates within that group (minus the Attendance Date for Triage event). From that list of dates from both places I need to return the earliest date listed. I now its a little complicated but hope that is clear?

Thanks very much for all your help here. It might be useful (for me and others) if you had time sometime to add some comments to the code as I would be interested to know what each part does to better understand how it works.

I will do a final manual spot check in the morning using the full data sheet and the new module but it certainly seems to have worked perfectly. Thanks again.

Thanks I will try this new method as well to see if I get the same results.

Also thanks to karedog for providing the really helpful comments to the code. I can now visualise better how it works. I have never used the collection object before.