I need help on an assignment. Its a list of patients meant for healthcare purposes. This list is sent to us in PDF format and we just search to find any entitled patient on the list. Now we have to convert to excel and rearrange for better management. My task now is to handle the rearrangement using macro since rearranging all manualy takes weeks and we receive the list on monthly basis.
I have never done this before and I am lost as to how to handle it. Please help.
Below is the format of the list. It is arranged in Header - List format but we need it in a different format as shown in the Sample Output format.
Sample List (with Little Explanation
1. Rows like 26645 are headers (there are footers also) and will be automatically removed
2. Hospital No (First part of Row 26646) is Unique to each Hospital). I will form a new field
3. Hospital Name Will form a new field or will be ignored
4. Row 26647 carries Family ID and Family Name. Asterisks will be removed to get the Number
5. Row 26648 Shows details of Family Head (Proncipal). Unique Id will be FamilyId + 0. That is 027926670. It forms a Unique Field
6. Principal/Spouse/Child is the Membership in the Family - hence it forms a field
7. Family Name forms a Field
8. Field B is First Name
9. Field C contains Date of Birth and Gender. This will be broken down to separate fields. Last Part of the string can be ignored (it is Batch Number)
Embedded Broken Image removed
Sample from the Original Excel Sheet
[TABLE="width: 939"]
<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]
</tbody>[/TABLE]
Sample Expected Output
[TABLE="width: 1609"]
<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]
My major problem is the rearrangement of this data.
If required, I can upload a sample workbook if there is an avenue for uploading file, or I can put it in a place and share the link.
Thanks in advance.
I have never done this before and I am lost as to how to handle it. Please help.
Below is the format of the list. It is arranged in Header - List format but we need it in a different format as shown in the Sample Output format.
Sample List (with Little Explanation
1. Rows like 26645 are headers (there are footers also) and will be automatically removed
2. Hospital No (First part of Row 26646) is Unique to each Hospital). I will form a new field
3. Hospital Name Will form a new field or will be ignored
4. Row 26647 carries Family ID and Family Name. Asterisks will be removed to get the Number
5. Row 26648 Shows details of Family Head (Proncipal). Unique Id will be FamilyId + 0. That is 027926670. It forms a Unique Field
6. Principal/Spouse/Child is the Membership in the Family - hence it forms a field
7. Family Name forms a Field
8. Field B is First Name
9. Field C contains Date of Birth and Gender. This will be broken down to separate fields. Last Part of the string can be ignored (it is Batch Number)
Embedded Broken Image removed
Sample from the Original Excel Sheet
[TABLE="width: 939"]
<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]
</tbody>[/TABLE]
Sample Expected Output
[TABLE="width: 1609"]
<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]
My major problem is the rearrangement of this data.
If required, I can upload a sample workbook if there is an avenue for uploading file, or I can put it in a place and share the link.
Thanks in advance.
Last edited by a moderator: