Grouping rows together by checking if strings exists in a the rows and adding a blank row to seperate the group

A

asolanki

Board Regular
Joined
Jan 22, 2003
Messages
80
Hi Guys

I just cant get my head round this one, and wanted to see if you guys could help me please and provide some guidance

I have a extract of data - thats come from a PDF to Text conversion, so it is a data dump into Column A of an Excel sheet
The data is Business Addresses for clinics per county in kenya

The format is pretty good, as its usually in a certain order - and these items are most of the time present
Name
Address 1
Address 2
PO Box, County
Tel:

but here is where im getting stuck - these 3 below are not always there, but if they are they are in this order
Mobile:
Email:
Website:

So initially i thought i would write code to just "Find" the word "Website" and this would tell me that this 'Record' is at the end, but i noticed the pattern wasnt as good as i thought it was to do this as i needed to check if all the elements existed or not - so that bit of code i wrote wasn't going to work.

Is there a way i can write some code that would start at the top of Column A and traverse down and then insert a new blank row to signal the new record starting after it checks if the elements above are present or not.

This is a start to seperate the data , as the next step is i have to grab these 'Records' and then put them in the relevant columns in another sheet ( name, address, PO Box, Tel, Mobile, Email , Website), but unless i can do the above i dont think thats going to be possible as the data is all in one column --- unless it can be done at the same time as the above request and split into its columns as it parses for the records splits?

The data example below may help solidify what im trying to do ( i hope)
As you can see it segmented into Clinics and Counties too ( there 47 county splits and about 12 different Clinic splits - i think approx over 3000+ Full Records that i need to split) which i need to split into seperate sheets per Clinic name ( but i will post this seperately to try automate this - i was just going to cut and paste manually and then run this marcro on each sheet)

Please let me know if anyone can help, if you need more info please shout ( i cant seem to post attachments - so heres a snippet of the data as an example)
You help here is much appreciated, and I Thank you in advance for your time and advice.

Ash

DENTAL CLINICS - --------------------------------------- This is the CLINIC SPLIT - theres one more below to show this as an example
NAIROBI COUNTY - --------------------------------------- This is the County SPLIT
Abdallah & Associates
Barclays Plaza, Lower Ground Floor
Loita Street
P. 0. Box 20458, Nairobi 00200
Tel: 020-2211637, 2251602
Mobile: 0727-971610, 0715-777457
Email:
info@abdallahandassociatesdental.com
Website: www.abdallahassociatesdental.com
Alexa Dental Clinic --------------------------------this is where it would add the Blank row
Prof. Nelson Awori Centre, 3rd Floor Wing B,
Suite B 1, Ralph Bunche Road
P.O. Box 2680, Nairobi 00202
Tel: 0702-182135, 0788-458661
E-mail: dentalalexa@gmail.com
Alif Medical Centre Ltd.--------------------------------this is where it would add the Blank row above
Bakaal Plaza, Ground Floor
5th Street Off 2nd Avenue Eastleigh
Opp. Makah Guest House (Samad)
P.O Box 69086, Nairobi
Mobile: 0721-752861, 0722-146585
Astradental Clinic--------------------------------this is where it would add the Blank row above
Mpaka Road, Mpaka Plaza, 1st Floor
Tel: 020-2088043
Mobile: 0703-500819, 0732-561558
E-mail: admin@astradental.co.ke
Website: astradental.co.ke
Baraka Dental Clinic--------------------------------this is where it would add the Blank row above
Afya Co-op House 4th Floor
Tom Mboya Street, Opp Khoja Mosque
Tel: 0720-397330, 0723-774266
Baraka Smile Clinic--------------------------------this is where it would add the Blank row above
Deep Blue House 3rd Floor, Room 67
Kahawa Wendani
P.O. Box 42496, Nairobi 00100
Tel: 0726-405187
Barakat Dental Services-----------------------this is where it would add the Blank row above
2nd Avenue Eastleigh, 12th Street
P.O. Box 6440, Nairobi
Tel: 020-526968
Mobile: 0721-133389, 0728-982280,
0721-287111
Best Smiles Dental Services-----------------------this is where it would add the Blank row above
Argwings Kodhek Road 4th Floor Rm 406
Hurligham Medicare Plaza
P.O Box 29755, Nairobi 00202
Tel: 0722-899798, 0725-519276
Bethany Dental & Maxillofacial Centre-----------------------this is where it would add the Blank row above
Starwood Apartments , 2nd Floor A6
Arwings Kodhek Road
P.O. Box 31095, Nairobi 00600
Tel: 0722-677575, 0724703678
Betimes Dental Services
Uniafric House, 3rd Floor Room 327
P.O. Box 7960, Nairobi 00300
Tel: 020-2216729,
Better Dental Care Clinic
Suraji Building, 1st Floor
Opposite Jamhuri School
P.O. Box 42932, Nairobi 00100
Tel: 020-3742839
Kenya Medical Directory
PRIVATE CLINICS - ---------------------------------- This is the CLINIC SPLIT
Bimal Yajnik & Associates
M.P Shah Hospital, Suite S6
P.O. Box 46885, Nairobi 00100
Tel: 020-3743707
Mobile: 0721-602333, 0733-612962
Brixton Medicare Services
5th/6th Street 2nd Avenue, Eastleigh
P.O. Box 5157, Nairobi 00200
Tel: 020-764206
Church Army Dental Clinic
Church Army Centre, Jogoo Road
Opposite St. Stephen ACK Church
P.O. Box 25586, Nairobi 00100
Tel: 020-558253, 558596, 8052666
Mobile: 0722-763405
E-mail: info@churcharmydentalclinic.com
City Dental
Kimathi House, 4th Floor, Kimathi Street
P.O. Box 38761, Nairobi
Tel: 020-2227663, 2227664
Mobile: 0726-986671
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Workaround until you get an easier solution:

If the data starts in A1, enter in

B1:

=IF(OR(COUNTIF(A1,"Tel:*")>0,COUNTIF(A1,"Mobile:*")>0,COUNTIF(A1,"E-mail:*")>0,COUNTIF(A1,"Website:*")>0),A1&"ß",A1)

C1:

=IF(AND(COUNTIF(B2,"*ß")>0,COUNTIF(B1,"*ß")>0),SUBSTITUTE(B1,"ß",""),B1)

and copy down both formulas.

Now copy column C and paste it into a blank Word document. Select the table, go to Table/Converting/ and Convert Table to text with Paragraph mark as delimiter. Then go to Edit/Replace and replace all „ß” with „^13” (without quotes). Finally, copy the whole text you got and paste it back into a chosen Excel column.
Let me know if you do not get the result illustrated in the attached spreadsheet.
Excel Workbook
ABCD
1Abdallah & AssociatesAbdallah & AssociatesAbdallah & AssociatesAbdallah & Associates
2Barclays Plaza, Lower Ground FloorBarclays Plaza, Lower Ground FloorBarclays Plaza, Lower Ground FloorBarclays Plaza, Lower Ground Floor
3Loita StreetLoita StreetLoita StreetLoita Street
4P. 0. Box 20458, Nairobi 00200P. 0. Box 20458, Nairobi 00200P. 0. Box 20458, Nairobi 00200P. 0. Box 20458, Nairobi 00200
5Tel: 020-2211637, 2251602Tel: 020-2211637, 2251602Tel: 020-2211637, 2251602Tel: 020-2211637, 2251602
6Mobile: 0727-971610, 0715-777457Mobile: 0727-971610, 0715-777457Mobile: 0727-971610, 0715-777457Mobile: 0727-971610, 0715-777457
7E-mail: info@abdallahandassociatesdental.comE-mail: info@abdallahandassociatesdental.comE-mail: info@abdallahandassociatesdental.comE-mail: info@abdallahandassociatesdental.com
8Website: www.abdallahassociatesdental.comWebsite: www.abdallahassociatesdental.comWebsite: www.abdallahassociatesdental.comWebsite: www.abdallahassociatesdental.com
9Alexa Dental ClinicAlexa Dental ClinicAlexa Dental Clinic
10Prof. Nelson Awori Centre, 3rd Floor Wing B,Prof. Nelson Awori Centre, 3rd Floor Wing B,Prof. Nelson Awori Centre, 3rd Floor Wing B,Alexa Dental Clinic
11Suite B 1, Ralph Bunche RoadSuite B 1, Ralph Bunche RoadSuite B 1, Ralph Bunche RoadProf. Nelson Awori Centre, 3rd Floor Wing B,
12P.O. Box 2680, Nairobi 00202P.O. Box 2680, Nairobi 00202P.O. Box 2680, Nairobi 00202Suite B 1, Ralph Bunche Road
13Tel: 0702-182135, 0788-458661Tel: 0702-182135, 0788-458661Tel: 0702-182135, 0788-458661P.O. Box 2680, Nairobi 00202
14E-mail: dentalalexa@gmail.comE-mail: dentalalexa@gmail.comE-mail: dentalalexa@gmail.comTel: 0702-182135, 0788-458661
15Alif Medical Centre Ltd.Alif Medical Centre Ltd.Alif Medical Centre Ltd.E-mail: dentalalexa@gmail.com
16Bakaal Plaza, Ground FloorBakaal Plaza, Ground FloorBakaal Plaza, Ground Floor
175th Street Off 2nd Avenue Eastleigh5th Street Off 2nd Avenue Eastleigh5th Street Off 2nd Avenue EastleighAlif Medical Centre Ltd.
Sheet
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Find First occurance of a CHAR and then check rows until occurance stops - Then execute code
Replies
5
Views
455
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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