Copy excel entries into new work sheet and rearrange the values

[KokoroAyo]

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.

 

[KokoroAyo]

Sample Expected Output (reformatted). Thank you Peter_SSs for your kind guidance.
Excel 2007

	A	B	C	D	E	F	G	H	I
SN	CLIENTNO	FAMILYNAME	FIRST_NAME	BIRTHDATE	SEX	FAMILY_MEMBERSHIP	HOSPITALID	HOSPITAL_NAME
SANUSI	TEMILADE	02/11/1980	M	Principal	OY/0250	Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
SANUSI	OLUWADAMILOLA	19/03/1992	F	Spouse	OY/0250	Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
OKOLO	EMMANUEL	23/10/1980	M	Principal	OY/0250	Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
OKOLO	CHUKWUEMEKA	13/06/2015	M	Child	OY/0250	Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
OKOLO	UZOAMAKA	03/05/2013	F	Child	OY/0250	Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
AWOLOLA	AJIBOLA	10/08/1977	M	Principal	OY/0250	Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
AWOLOLA	ABIDEMI	05/08/1982	F	Spouse	OY/0250	Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
AWOLOLA	MORAYOOLUWA	20/06/2012	F	Child	OY/0250	Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]027922670[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]027922671[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]027922690[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]027922691[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]027922692[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]027922770[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]027922771[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]027922772[/TD]

</tbody>

OUTPUT SAMPLE​

And this is the Original data (as converted from the PDF document)
Excel 2007

	A	B	C
Ronsberger Nigeria Ltd.	Issued by NHIS
OY/0250 Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
*02792267* SANUSI
0 Principal SANUSI	TEMILADE	02/11/1980 M E/240
1 Spouse SANUSI	OLUWADAMILOLA	19/03/1992 F E/240
*02792269* OKOLO
0 Principal OKOLO	EMMANUEL	23/10/1980 M E/240
2 Child1 OKOLO	CHUKWUEMEKA	13/06/2015 M E/240
3 Child2 OKOLO	UZOAMAKA	03/05/2013 F E/240
*02792277* AWOLOLA
0 Principal AWOLOLA	AJIBOLA	10/08/1977 M E/240
1 Spouse AWOLOLA	ABIDEMI	05/08/1982 F E/240
2 Child1 AWOLOLA	MORAYOOLUWA	20/06/2012 F E/240

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]26644[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26645[/TD]

[TD="align: right"][/TD]

[TD="align: center"]26646[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]26647[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26648[/TD]

[TD="align: center"]26649[/TD]

[TD="align: center"]26650[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26651[/TD]

[TD="align: center"]26652[/TD]

[TD="align: center"]26653[/TD]

[TD="align: center"]26654[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26655[/TD]

[TD="align: center"]26656[/TD]

[TD="align: center"]26657[/TD]

</tbody>

Original​

 

[MickG]

Based on your original data, Try this for results on sheet2 starting "A1".
NB:- I have assumed that the Data (starting point) :- " *02792267* SANUSI" is in cell "A3".
[TABLE="width: 158"]
<colgroup><col width="211" style="width: 158pt; mso-width-source: userset; mso-width-alt: 7509;"> <tbody>[TR]
[TD="class: xl63, width: 211, bgcolor: yellow"][/TD]
[/TR]
</tbody>[/TABLE]

[COLOR="Navy"]Sub[/COLOR] MG10Sep17
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, Hd1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Hd2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, R [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nSp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range("A3", Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count + 1, 1 To 9)
 ray(1, 1) = "Ser Num": ray(1, 2) = "CLIENT_NUMBER": ray(1, 3) = "Surname": ray(1, 4) = "First Name": ray(1, 5) = "DATE_OF_BIRTH": ray(1, 6) = "GENDA": ray(1, 7) = "FAMILY_MEMBERSHIP": ray(1, 8) = "HOSPITAL_NAME": ray(1, 9) = "HOSPITAL_ID"
   Sp = Split(Range("B2"), " ")
     Hd1 = Sp(0): Hd2 = Mid(Range("B2"), Len(Sp(0)) + 2)
       c = 1
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sp = Split(Dn.Value, " ")
    [COLOR="Navy"]If[/COLOR] Not .Exists(Sp(UBound(Sp))) [COLOR="Navy"]Then[/COLOR]
        .Add Sp(UBound(Sp)), Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Sp(UBound(Sp))) = Union(.Item(Sp(UBound(Sp))), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] .Item(K)
        Sp = Split(R, " ")
            [COLOR="Navy"]If[/COLOR] InStr(R, "*") <> 0 [COLOR="Navy"]Then[/COLOR]
                Num = Replace(Sp(0), "*", "")
            [COLOR="Navy"]Else[/COLOR]
               nSp = Split(R.Offset(, 1).Value & " " & R.Offset(, 2).Value, " ")
                c = c + 1
                ray(c, 1) = c - 1
                ray(c, 2) = Num & Val(Sp(0))
                ray(c, 3) = K
                ray(c, 4) = nSp(0)
                ray(c, 5) = CDate(nSp(1))
                ray(c, 6) = nSp(2)
                ray(c, 7) = Sp(1)
                ray(c, 8) = Hd2
                ray(c, 9) = Hd1
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet9").Range("A1").Resize(c, 9)
    .Value = ray
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MickG]

Just a Note :-
Change the line below (at bottom of code) to "Sheet2" from "sheet9".

With  Sheets("Sheet2").Range("A1").Resize(c, 9)

 

[KokoroAyo]

Thank you very much Mick for your quick response.

However, I could not get it to run as it returns "Runtime Error '9'. Subscript out of range.

And the above error refers to Line 7:
Sp = Split(Range("B2"), " ")Thank you once again.

 

[MickG]

Try this example file:-
https://app.box.com/s/t850b18y1iuo4jt9fd3aevr64d8izrwu

 

[KokoroAyo]

Please one more thing. I have tried to tweak the code to loop through the full list without success. It has been difficult getting the loop right. You help is highly appreciated.

Please take a look at the full workbook from where I extracted the sample here:
https://www.4shared.com/office/7El_xePWca/ENROLEE_LIST.html

Thank you once again.

 

[KokoroAyo]

Please one more thing. I have tried to tweak the code to loop through the full list without success. It has been difficult getting the loop right. You help is highly appreciated.

Please take a look at the full workbook from where I extracted the sample here:
https://www.4shared.com/office/7El_xePWca/ENROLEE_LIST.html

Thank you once again.

Thank you Mick. The code worked like charm. I am really grateful for this.

 

[MickG]

You're welcome
If you still want me to look at your file please post file use Box.com.

 

[KokoroAyo]

You're welcome
If you still want me to look at your file please post file use Box.com.

Thank you for your support so far.

Please see the link to the file here:
https://app.box.com/s/70kavj40ppke5ebtgb8ata1w9wula2z2