Dear All,
I need help with an assignment.
In my place of work, we manage patients (entitled) for healthcare. We get the list of patients from the insurance agency as PDF file and they are not ready to give it in any other format. At the moment, we operate manually and therefore, to identify a patient, we have to search the using the find tool. This has become very cumbersome and we thought of doing something a little semi automated by creating a small database for patients as they are released every month. This will help us keep proper record of activities so we decided to use Excel to manage the list and later move to Access or any other DBMS.
We got a commercial PDF to Excel converter which gives about 90% accuracy in conversion and I think I can manage with that. My challenge now is that:
1. The list is formatted in Master-Detail (Header-List) Format.
2. We need it formatted in Individual Record format
3. It took 3 weeks to get the first trial completed manually having 3 people working on it. By then A new list (comprising of about 50,000 patients) would have been released as new one comes in every month.
4. Therefore, I need a macro to help me scan through the converted Excel document, extract the relevant data and rearrange them in the required format (on the same sheet or on a new sheet (preferably)).
Kindly help me please. My job depends on this.
Please see some relevant samples below.
A screen shot of a sample data section with some explanations
This is a cross section of the data. It shows how data is formated. There are more up and more down. Rows between lists are not equal in all cases. There are Headers and Footers in between as exported from the PDF document.
In this example:
1. Row 26645 is a header (and there are footers too) so it will be removed and not in the output
2. Row 26645 will be splitted as follows:
i. Hospital No is unique to each hospital. I will form a new column
ii. Hospital Name Will form a new Column. *** It can be ignored because I have the list of hospitals and can merge that.
3. Rows 26647 to 26649 carries a Family Record. The next family
4. Row 26647 contains Family ID number and Family Name. Only the Family ID is required here without the asterisks. This number will be used for every family member
5. Column A contains UnidPersonId in the family and Family Name. 0=Principal, 1 to N for others as indicated. So A new Column called Client_No will contain FamilyNumber+PatientNO. (eg. for 26648, ClientNo will be 027922670), then Another Column for FamilyName: (Sanusi in this case)
6. Column B is "First Name"
7. Column C contains "Date of Birth", "Gender" and "Batch Number". This will be broken up into 3 Coulumns. DateOfBirth, Gender, and BatchNumber. Batch number can be ignored if the first 2 are extracted.
Failed image code removed by Moderator
Excel Extract of the above screen shot
[TABLE="width: 939"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ronsberger Nigeria Ltd.
[/TD]
[TD][/TD]
[TD]Issued by NHIS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]OY/0250 Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[/TR]
[TR]
[TD]*02792267* SANUSI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0 Principal SANUSI[/TD]
[TD]TEMILADE[/TD]
[TD]02/11/1980 M E/240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 Spouse SANUSI[/TD]
[TD]OLUWADAMILOLA 19/03/1992 F E/240[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*02792269* OKOLO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0 Principal OKOLO[/TD]
[TD]EMMANUEL 23/10/1980 M E/240[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 Child1 OKOLO[/TD]
[TD]CHUKWUEMEKA 13/06/2015 M E/240[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 Child2 OKOLO[/TD]
[TD]UZOAMAKA 03/05/2013 F E/240[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*02792277* AWOLOLA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0 Principal AWOLOLA[/TD]
[TD]AJIBOLA[/TD]
[TD]10/08/1977 M E/240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 Spouse AWOLOLA[/TD]
[TD]ABIDEMI[/TD]
[TD]05/08/1982 F E/240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 Child1 AWOLOLA[/TD]
[TD]MORAYOOLUWA 20/06/2012 F E/240[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*02792285* AGBABIAJE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0 Principal AGBABIAJE[/TD]
[TD]TOLULOPE[/TD]
[TD]26/07/1985 M E/240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*02792353* ODUGBEMI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0 Principal ODUGBEMI[/TD]
[TD]RAFIU[/TD]
[TD]13/02/1981 M E/240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 Spouse ODUGBEMI[/TD]
[TD]TOYIN[/TD]
[TD]04/08/1990 F E/240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 Child1 ODUGBEMI[/TD]
[TD]OLAMIDE[/TD]
[TD]25/07/2012 F E/240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 Child2 ODUGBEMI[/TD]
[TD]EMMANUEL 10/10/2006 M E/240[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*02839471* ADEJIMI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0 Principal ADEJIMI[/TD]
[TD]ADEKUNLE 28/06/1978 M E/344[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 Spouse ADEJIMI[/TD]
[TD]OLUBUNMI 14/07/1979 F E/344[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 Child1 ADEJIMI[/TD]
[TD]OLUWADABIRA 08/07/2007 M E/344[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 Child2 ADEJIMI[/TD]
[TD]DAMILOLA 26/04/2009 M E/344[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sample Output with Explanation
[TABLE="width: 1609"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]SN (Serial Number - Autogenerated)
[/TD]
[TD]CLIENT_NUMBER (HEADNUMBER without the '*' + UNIT NUMBER AS IN DOCUMENT) - PLEASE TAKE NOTE OF LEADING ZEROS
[/TD]
[TD]SURNAME (FAMILY NAME AS IN DOCCUMENT)
[/TD]
[TD]FIRST_NAME (AS IN DOCUMENT)[/TD]
[TD]DATE_OF_BIRTH (AS IN DOCUMENT)[/TD]
[TD]GENDA (AS IN DOCUMENT)[/TD]
[TD]FAMILY_MEMBERSHIP (AS INDICATED IN DOCUMENT) - Options are: Principal, Spouse and Child[/TD]
[TD]HOSPITAL_NAME (COPY HEADER HOSPITAL NAME FOR ALL CLIENTS FOUND UNDER IT)[/TD]
[TD]HOSPITAL_ID (COPY CORRESPONDING HEADER HOSPITAL ID FOR ALL CLIENTS FOUND UNDER IT)[/TD]
[/TR]
[TR]
[TD="colspan: 6"]See Examples Below[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]027922670
[/TD]
[TD]SANUSI[/TD]
[TD]TEMILADE[/TD]
[TD]02/11/1980[/TD]
[TD]M[/TD]
[TD]Principal[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]027922671
[/TD]
[TD]SANUSI[/TD]
[TD]OLUWADAMILOLA[/TD]
[TD]19/03/1992[/TD]
[TD]F[/TD]
[TD]Spouse[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]027922690[/TD]
[TD]OKOLO[/TD]
[TD]EMMANUEL[/TD]
[TD]23/10/1980[/TD]
[TD]M[/TD]
[TD]Principal[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]027922691[/TD]
[TD]OKOLO[/TD]
[TD]CHUKWUEMEKA[/TD]
[TD]13/06/2015[/TD]
[TD]M[/TD]
[TD]Child[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]027922692
[/TD]
[TD]OKOLO[/TD]
[TD]UZOAMAKA[/TD]
[TD]03/05/2013[/TD]
[TD]F[/TD]
[TD]Child[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]027922770
[/TD]
[TD]AWOLOLA[/TD]
[TD]AJIBOLA[/TD]
[TD]10/08/1977[/TD]
[TD]M[/TD]
[TD]Principal[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]027922771
[/TD]
[TD]AWOLOLA[/TD]
[TD]ABIDEMI[/TD]
[TD]05/08/1982[/TD]
[TD]F[/TD]
[TD]Spouse[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]027922772
[/TD]
[TD]AWOLOLA[/TD]
[TD]MORAYOOLUWA[/TD]
[TD]20/06/2012[/TD]
[TD]F[/TD]
[TD]Child[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
</tbody>[/TABLE]
Kindly help me on this.
Thank you in advance.
I need help with an assignment.
In my place of work, we manage patients (entitled) for healthcare. We get the list of patients from the insurance agency as PDF file and they are not ready to give it in any other format. At the moment, we operate manually and therefore, to identify a patient, we have to search the using the find tool. This has become very cumbersome and we thought of doing something a little semi automated by creating a small database for patients as they are released every month. This will help us keep proper record of activities so we decided to use Excel to manage the list and later move to Access or any other DBMS.
We got a commercial PDF to Excel converter which gives about 90% accuracy in conversion and I think I can manage with that. My challenge now is that:
1. The list is formatted in Master-Detail (Header-List) Format.
2. We need it formatted in Individual Record format
3. It took 3 weeks to get the first trial completed manually having 3 people working on it. By then A new list (comprising of about 50,000 patients) would have been released as new one comes in every month.
4. Therefore, I need a macro to help me scan through the converted Excel document, extract the relevant data and rearrange them in the required format (on the same sheet or on a new sheet (preferably)).
Kindly help me please. My job depends on this.
Please see some relevant samples below.
A screen shot of a sample data section with some explanations
This is a cross section of the data. It shows how data is formated. There are more up and more down. Rows between lists are not equal in all cases. There are Headers and Footers in between as exported from the PDF document.
In this example:
1. Row 26645 is a header (and there are footers too) so it will be removed and not in the output
2. Row 26645 will be splitted as follows:
i. Hospital No is unique to each hospital. I will form a new column
ii. Hospital Name Will form a new Column. *** It can be ignored because I have the list of hospitals and can merge that.
3. Rows 26647 to 26649 carries a Family Record. The next family
4. Row 26647 contains Family ID number and Family Name. Only the Family ID is required here without the asterisks. This number will be used for every family member
5. Column A contains UnidPersonId in the family and Family Name. 0=Principal, 1 to N for others as indicated. So A new Column called Client_No will contain FamilyNumber+PatientNO. (eg. for 26648, ClientNo will be 027922670), then Another Column for FamilyName: (Sanusi in this case)
6. Column B is "First Name"
7. Column C contains "Date of Birth", "Gender" and "Batch Number". This will be broken up into 3 Coulumns. DateOfBirth, Gender, and BatchNumber. Batch number can be ignored if the first 2 are extracted.
Failed image code removed by Moderator
Excel Extract of the above screen shot
[TABLE="width: 939"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ronsberger Nigeria Ltd.
[/TD]
[TD][/TD]
[TD]Issued by NHIS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]OY/0250 Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[/TR]
[TR]
[TD]*02792267* SANUSI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0 Principal SANUSI[/TD]
[TD]TEMILADE[/TD]
[TD]02/11/1980 M E/240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 Spouse SANUSI[/TD]
[TD]OLUWADAMILOLA 19/03/1992 F E/240[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*02792269* OKOLO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0 Principal OKOLO[/TD]
[TD]EMMANUEL 23/10/1980 M E/240[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 Child1 OKOLO[/TD]
[TD]CHUKWUEMEKA 13/06/2015 M E/240[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 Child2 OKOLO[/TD]
[TD]UZOAMAKA 03/05/2013 F E/240[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*02792277* AWOLOLA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0 Principal AWOLOLA[/TD]
[TD]AJIBOLA[/TD]
[TD]10/08/1977 M E/240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 Spouse AWOLOLA[/TD]
[TD]ABIDEMI[/TD]
[TD]05/08/1982 F E/240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 Child1 AWOLOLA[/TD]
[TD]MORAYOOLUWA 20/06/2012 F E/240[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*02792285* AGBABIAJE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0 Principal AGBABIAJE[/TD]
[TD]TOLULOPE[/TD]
[TD]26/07/1985 M E/240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*02792353* ODUGBEMI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0 Principal ODUGBEMI[/TD]
[TD]RAFIU[/TD]
[TD]13/02/1981 M E/240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 Spouse ODUGBEMI[/TD]
[TD]TOYIN[/TD]
[TD]04/08/1990 F E/240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 Child1 ODUGBEMI[/TD]
[TD]OLAMIDE[/TD]
[TD]25/07/2012 F E/240[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 Child2 ODUGBEMI[/TD]
[TD]EMMANUEL 10/10/2006 M E/240[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*02839471* ADEJIMI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0 Principal ADEJIMI[/TD]
[TD]ADEKUNLE 28/06/1978 M E/344[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 Spouse ADEJIMI[/TD]
[TD]OLUBUNMI 14/07/1979 F E/344[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 Child1 ADEJIMI[/TD]
[TD]OLUWADABIRA 08/07/2007 M E/344[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 Child2 ADEJIMI[/TD]
[TD]DAMILOLA 26/04/2009 M E/344[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sample Output with Explanation
[TABLE="width: 1609"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]SN (Serial Number - Autogenerated)
[/TD]
[TD]CLIENT_NUMBER (HEADNUMBER without the '*' + UNIT NUMBER AS IN DOCUMENT) - PLEASE TAKE NOTE OF LEADING ZEROS
[/TD]
[TD]SURNAME (FAMILY NAME AS IN DOCCUMENT)
[/TD]
[TD]FIRST_NAME (AS IN DOCUMENT)[/TD]
[TD]DATE_OF_BIRTH (AS IN DOCUMENT)[/TD]
[TD]GENDA (AS IN DOCUMENT)[/TD]
[TD]FAMILY_MEMBERSHIP (AS INDICATED IN DOCUMENT) - Options are: Principal, Spouse and Child[/TD]
[TD]HOSPITAL_NAME (COPY HEADER HOSPITAL NAME FOR ALL CLIENTS FOUND UNDER IT)[/TD]
[TD]HOSPITAL_ID (COPY CORRESPONDING HEADER HOSPITAL ID FOR ALL CLIENTS FOUND UNDER IT)[/TD]
[/TR]
[TR]
[TD="colspan: 6"]See Examples Below[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]027922670
[/TD]
[TD]SANUSI[/TD]
[TD]TEMILADE[/TD]
[TD]02/11/1980[/TD]
[TD]M[/TD]
[TD]Principal[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]027922671
[/TD]
[TD]SANUSI[/TD]
[TD]OLUWADAMILOLA[/TD]
[TD]19/03/1992[/TD]
[TD]F[/TD]
[TD]Spouse[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]027922690[/TD]
[TD]OKOLO[/TD]
[TD]EMMANUEL[/TD]
[TD]23/10/1980[/TD]
[TD]M[/TD]
[TD]Principal[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]027922691[/TD]
[TD]OKOLO[/TD]
[TD]CHUKWUEMEKA[/TD]
[TD]13/06/2015[/TD]
[TD]M[/TD]
[TD]Child[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]027922692
[/TD]
[TD]OKOLO[/TD]
[TD]UZOAMAKA[/TD]
[TD]03/05/2013[/TD]
[TD]F[/TD]
[TD]Child[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]027922770
[/TD]
[TD]AWOLOLA[/TD]
[TD]AJIBOLA[/TD]
[TD]10/08/1977[/TD]
[TD]M[/TD]
[TD]Principal[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]027922771
[/TD]
[TD]AWOLOLA[/TD]
[TD]ABIDEMI[/TD]
[TD]05/08/1982[/TD]
[TD]F[/TD]
[TD]Spouse[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]027922772
[/TD]
[TD]AWOLOLA[/TD]
[TD]MORAYOOLUWA[/TD]
[TD]20/06/2012[/TD]
[TD]F[/TD]
[TD]Child[/TD]
[TD]Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State[/TD]
[TD]OY/0250[/TD]
[/TR]
</tbody>[/TABLE]
Kindly help me on this.
Thank you in advance.
Last edited by a moderator:
