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

[83dons]

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:

StatusText	Attendance Date	Appointment Date	Event Type	Episode Reference	PIN	Start Date
GreenCircle	04/03/2019​		Triage	EP205143	12345	01/03/2019​
GreenCircle	22/03/2019​	22/03/2019​	Nurse Telephone Contact - Initial	EP205143	12345	01/03/2019​
GreenCircle	05/06/2019​	05/06/2019​	Adviser Assessment - Review	EP205143	12345	01/03/2019​
GreenCircle	24/07/2019​	24/07/2019​	Adviser Assessment - Review	EP205143	12345	01/03/2019​
GreenCircle	19/09/2019​	19/09/2019​	Adviser Assessment - Review	EP205143	12345	01/03/2019​
GreenCircle	28/11/2019​	28/11/2019​	Adviser Assessment - Review	EP205143	12345	01/03/2019​
GreenCircle	20/01/2020​	20/01/2020​	Adviser Assessment - Review	EP205143	12345	01/03/2019​
GreenCircle	04/03/2019​		Triage	EP205144	87654	01/03/2019​
GreenCircle	15/03/2019​		Nurse Telephone Contact - Initial	EP205144	87654	01/03/2019​
GreenCircle	14/05/2019​		Adviser Assessment - Review	EP205144	87654	01/03/2019​
GreenCircle	04/03/2019​		Triage	EP205145	11145	01/03/2019​
GreenCircle	19/03/2019​	19/03/2019​	Medical Assessment	EP205145	11145	01/03/2019​
GreenCircle	31/05/2019​	31/05/2019​	Medical Review	EP205145	11145	01/03/2019​
GreenCircle	05/03/2019​		Triage	EP50177	45432	05/03/2019​
GreenCircle	29/04/2019​	29/04/2019​	Medical Review	EP50177	45432	05/03/2019​

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:

Attendance Date	Episode Reference​	PIN	Start Date	First Offered Appointment Date
04/03/2019	EP205143​	12345​	01/03/2019	22/03/2019
04/03/2019	EP205144​	87654​	01/03/2019	15/03/2019
04/03/2019	EP205145​	11145​	01/03/2019	19/03/2019
05/03/2019	EP50177​	45432​	05/03/2019	29/04/2019

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!

 

[83dons]

For those who would like the solution I found it on another Board. Cross posting I know but it saves typing the full code here and perhaps not acknowledging the person that provided the code:

SOLUTION