Data Processing - Update / extract data in a spreadsheet
$250-750 AUD
En curso
Publicado hace casi 8 años
$250-750 AUD
Pagado a la entrega
We wish to clean up some data in order to transition to a new system. Currently each contact (row) has a number of qualifications (university degrees) and associations (memberships) associated with them.
The qualifications often have a date of attainment and the place of study detailed alongside. The task is to split out the qualification (degree) the date (year) and place (location) into separate cells on the row.
There are 5000 rows to review and correct. Full data file will be supplied to the Freelancer awarded the work.
Considerations
GENERAL
Do not change the first three columns (firstname, surname, ID)
Any information in the cells that is not a degree/date/place should be placed in final columns (notes)
Degrees
Many contacts have multiple degrees.
Degrees are abbreviated
Abbreviations should be consistent and where they are not, they should be fixed. Consult the file [[login to view URL]] for correct abbreviation.
For example:
Steven
Eliopoulos
1eeda554-2750-bc99-cef4-556f8bbbead9
BE (Aero) 1983
Clifford
Benjamin
50011de5-6188-3841-b5a8-556f8be2eef2
BE (Aeronautical) 1949
Should be
Steven
Eliopoulos
1eeda554-2750-bc99-cef4-556f8bbbead9
BE (Aeronautical)
1983
Clifford
Benjamin
50011de5-6188-3841-b5a8-556f8be2eef2
BE (Aeronautical)
1949
If the qualification or abbreviation cannot be found in the file then it is possible it is an association or a degree from a foreign university.
Please detail and email back for clarification
Degrees sometimes have (Hons) or Hons or (H1) or (Hons1) or (Hons 1) or similar after them. Any variation of Hon should be converted to (Hons) – including the brackets and appear after the degree abbreviation. Same applied to (Dist)
Some have addition spaces ‘ ‘ between text. Extra spaces need to be removed.
Some have fullstops ‘.’ Eg B.A., these should be removed so degree appears as BA
Dates
Dates are not always complete – every date should be four numerals (YYYY)
Eg ’86 needs to be updated to 1986
Some dates are in brackets. These should be removed
Some dates there is a ?? after remove this
Some dates may appear over two years, eg 1955/56... remove the later date – so in this example it will be changed to 1955
Some qualifications have no date. The cell should be left blank
Location
Many locations are missing. Where there is no location the cell should be left blank
The locations may refer to the university (eg ANU) or the city (eg London). Some locations are abbreviated and others are not.
These need to be consistent and should be the non abbreviated version. Consult the file [[login to view URL]]
Any brackets should be removed
Files
The source data file is [login to view URL]
The required format is [login to view URL]
The degree abbreviation files [login to view URL]
The city/location files [login to view URL]