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

83dons

New Member
Joined
Apr 1, 2019
Messages
6
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:

StatusTextAttendance DateAppointment DateEvent TypeEpisode ReferencePINStart Date
GreenCircle
04/03/2019​
TriageEP20514312345
01/03/2019​
GreenCircle
22/03/2019​
22/03/2019​
Nurse Telephone Contact - InitialEP20514312345
01/03/2019​
GreenCircle
05/06/2019​
05/06/2019​
Adviser Assessment - ReviewEP20514312345
01/03/2019​
GreenCircle
24/07/2019​
24/07/2019​
Adviser Assessment - ReviewEP20514312345
01/03/2019​
GreenCircle
19/09/2019​
19/09/2019​
Adviser Assessment - ReviewEP20514312345
01/03/2019​
GreenCircle
28/11/2019​
28/11/2019​
Adviser Assessment - ReviewEP20514312345
01/03/2019​
GreenCircle
20/01/2020​
20/01/2020​
Adviser Assessment - ReviewEP20514312345
01/03/2019​
GreenCircle
04/03/2019​
TriageEP20514487654
01/03/2019​
GreenCircle
15/03/2019​
Nurse Telephone Contact - InitialEP20514487654
01/03/2019​
GreenCircle
14/05/2019​
Adviser Assessment - ReviewEP20514487654
01/03/2019​
GreenCircle
04/03/2019​
TriageEP20514511145
01/03/2019​
GreenCircle
19/03/2019​
19/03/2019​
Medical AssessmentEP20514511145
01/03/2019​
GreenCircle
31/05/2019​
31/05/2019​
Medical ReviewEP20514511145
01/03/2019​
GreenCircle
05/03/2019​
TriageEP5017745432
05/03/2019​
GreenCircle
29/04/2019​
29/04/2019​
Medical ReviewEP5017745432
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​
PINStart DateFirst Offered Appointment Date
04/03/2019
EP205143​
12345​
01/03/201922/03/2019
04/03/2019
EP205144​
87654​
01/03/201915/03/2019
04/03/2019
EP205145​
11145​
01/03/201919/03/2019
05/03/2019
EP50177​
45432​
05/03/201929/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!
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

83dons

New Member
Joined
Apr 1, 2019
Messages
6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,223
Messages
5,576,827
Members
412,748
Latest member
MikeyP14
Top