Copy excel entries into new work sheet and rearrange the values

K

KokoroAyo

New Member
Joined
Sep 8, 2017
Messages
14
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.
 
Last edited by a moderator:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Sample Expected Output (reformatted). Thank you Peter_SSs for your kind guidance.
Excel 2007
ABCDEFGHI
SNCLIENTNOFAMILYNAMEFIRST_NAMEBIRTHDATESEXFAMILY_MEMBERSHIPHOSPITALIDHOSPITAL_NAME
SANUSITEMILADE02/11/1980MPrincipalOY/0250Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
SANUSIOLUWADAMILOLA19/03/1992FSpouseOY/0250Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
OKOLOEMMANUEL23/10/1980MPrincipalOY/0250Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
OKOLOCHUKWUEMEKA13/06/2015MChildOY/0250Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
OKOLOUZOAMAKA03/05/2013FChildOY/0250Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
AWOLOLAAJIBOLA10/08/1977MPrincipalOY/0250Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
AWOLOLAABIDEMI05/08/1982FSpouseOY/0250Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
AWOLOLAMORAYOOLUWA20/06/2012FChildOY/0250Catholic 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
ABC
Ronsberger Nigeria Ltd.Issued by NHIS
OY/0250 Catholic Hospital, Oluyoro - Oluyoro, Ibadan, Oyo State
*02792267* SANUSI
0 Principal SANUSITEMILADE02/11/1980 M E/240
1 Spouse SANUSIOLUWADAMILOLA 19/03/1992 F E/240
*02792269* OKOLO
0 Principal OKOLOEMMANUEL 23/10/1980 M E/240
2 Child1 OKOLOCHUKWUEMEKA 13/06/2015 M E/240
3 Child2 OKOLOUZOAMAKA 03/05/2013 F E/240
*02792277* AWOLOLA
0 Principal AWOLOLAAJIBOLA10/08/1977 M E/240
1 Spouse AWOLOLAABIDEMI05/08/1982 F E/240
2 Child1 AWOLOLAMORAYOOLUWA 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​
 
Upvote 0
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]

Code: 
[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
 
Upvote 0
Just a Note :-
Change the line below (at bottom of code) to "Sheet2" from "sheet9".
Code: 
With  Sheets("Sheet2").Range("A1").Resize(c, 9)
 
Upvote 0
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.
 
Upvote 0
Upvote 0
You're welcome
If you still want me to look at your file please post file use Box.com.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Locked
  • Question Question
Copy excel entries into new work sheet and rearrange the values
Replies
1
Views
611
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,812
Messages
6,181,098
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top