You are here:

Excel/Aligning address data from outside sources

Advertisement


Question
QUESTION: Hi,

I have a two part question, I was unsure on whether I should split it into two questions or not.

The first part is that I am trying to get a list of wholesalers that are licensed from the EUDRA GMP, within WDA (the registrations I want) they have one cell that has all address information for all of the sites of a wholesaler, AAH would be a good example reg ID of UK WDA(H) 11545 (All addresses and sites for this wholesaler is in the 3rd column for 17 sites), I would like this information split up for each address to be extracted and associated with the ID,

the second part is how to get the address lines to align properly, as some have more fields filled then others, I'd like postcodes to be in their own columns and the town or city to be before it in their respective columns (I would like to apply this to other data that I import which the address fields don't align with the headers)

the data I wish to convert into a more usable excel file is from
http://eudragmdp.ema.europa.eu/inspections/view/wda/searchWDA.xhtml

setting the country field to EEA (for all) and exporting via the button in the header bar of the table


Thank you very much for your time
Kind regards

ANSWER: Hello Farzad,

I need some clarifications:
1. Are you trying to use a macro or are you copying to excel manual.
2. Is there any specific logic / pattern in addresses - Eg. address, street, country etc.
3. It will be good if you could share the format of your requirements with example.

Thanks,
Gulshan.

---------- FOLLOW-UP ----------

QUESTION: Hi,

The document is exported in excel format, Columns as follows
Authorisation Number|Authorisation Holder|Site Details|Authorisation Date|Last Updated Date

I would like the 3rd column to be split
Company name | Address line [1,2,3, etc] | City | Postcode | Site ID | Company name

if there are multiple Sites it repeats this patern in column 3 and separates them with ----,

example:



[Column A] UK WDA(H) 8247   

[Column B] BMI HEALTHCARE LTD T/A BMI Hospital Services

[Column C] - (Note all in one cell for this Wholesaler)   
BMI BATH CLINIC, CLAVERTON DOWN ROAD, COMBE DOWN, BATH, BA2 7BR, 119147-BMI BATH CLINIC
--------------------------------------------------
BMI HEALTHCARE LTD, CHAUCER HOSPITAL, NACKINGTON ROAD, CANTERBURY, CT4 7AR, 10748866-BMI HEALTHCARE LTD
--------------------------------------------------
BMI HEALTHCARE LTD - MOUNT ALVERNIA HOSPITAL, PHARMACY DEPARTMENT, 46 HARVEY ROAD, GUILDFORD, GU1 3LX, 7244585-BMI HEALTHCARE LTD - MOUNT ALVERNIA HOSPITAL
--------------------------------------------------
BMI HEALTHCARE LTD, VERSAILLES COURT, 3 PARIS GARDEN, LONDON, SE1 8ND, 6402675-BMI HEALTHCARE LTD
--------------------------------------------------
BMI ROSS HALL HOSPITAL (PRIVATE), 221 CROOKSTON ROAD, GLASGOW, STRATHCLYDE, G52 3NQ, 119151-BMI ROSS HALL HOSPITAL (PRIVATE)
--------------------------------------------------
PRIORY HOSPITAL, PRIORY ROAD, EDGBASTON, BIRMINGHAM, B5 7UG, 119248-PRIORY HOSPITAL
--------------------------------------------------
BMI THE CLEMENTINE CHURCHILL HOSPITAL, Sudbury Hill, HARROW, MIDDLESEX, HA1 3RX, 119157-BMI THE CLEMENTINE CHURCHILL HOSPITAL
--------------------------------------------------
BMI HEALTHCARE LTD, THE ALEXANDRA HOSPITAL, MILL LANE, CHEADLE, SK8 2PX, 11162199-BMI HEALTHCARE LTD

[Column D] 2016-01-07   
[Column E] 2016-01-21 09:19 GMT


All of this is in one row and repeated for each Wholesaler

I would like each site to have its own row and the address to have its own columns,

Company name | Address line 1|Address line 2 | Address line 3 | City | Postcode | Site ID | Company name

Authorisation number to repeat for the site and Authorisation holder to repeat for all of its sites

Many thanks
Kind regards

Answer
Hello Farzad,

I checked the site and tried generating a sample export as well. The problem is that when the file is generated in excel, we don't get the "----------" as separator.
In absence of it, alternatively need a logic for each address component whereby there should be a fixed number of commas for each address, eg. door no, street, city, state, zip etc. but I'm not able to identify such a pattern here.

Are you aware of any such pattern in the above data?

If not, then I'm afraid I won't be able to help on this issue.

Wish I could help better,
Gulshan.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.