SCOPE
Conducted within my role as Operations Analyst @ Veolia
The sales and transport teams had been
The database that was employed used Microsoft Excel, however it did not present records in a logical manner;
Each record was on a separate sheet within the document
Records only followed a loose structure of organisation, with positioning and contents varying
With over 350+ records, it was cumbersome to manually search and select the desired data
Calculations were conducted on each sheet independently, meaning that these could not be amended centrally - also increasing likelihood of error
Unattractive design and confusing layout of data
Hosted on a local network shared server, meaning multiple users could not operate it simultaneously, and the document was slow to open and operate
The business was beginning the process of becoming completely reliant on cloud storage for daily operation, with the fileserver being relegated to a local backup only
OLD SYSTEM PICTURE HERE
ANALYSIS
NAME
KG/ 1000
SQFT
SQFT
SQMTR
SHED COUNT
SHED # (1-30)
CONTACT
PHONE
MOBILE
ADDR1
ADDR2
ADDR3
ADDR4
ADDR5
NOTES1
NOTES2
NOTES3
NOTES4
NOTES5
METHODOLOGY
Used VBA script to extract a list of all sheet names and print in new spreadsheet = site name
Sub ListSheetNamesInNewWorkbook()
Dim objNewWorkbook As Workbook
Dim objNewWorksheet As Worksheet
Set objNewWorkbook = Excel.Application.Workbooks.Add
Set objNewWorksheet = objNewWorkbook.Sheets(1)
For i = 1 To ThisWorkbook.Sheets.Count
objNewWorksheet.Cells(i, 1) = i
objNewWorksheet.Cells(i, 2) = ThisWorkbook.Sheets(i).Name
Next i
With objNewWorksheet
.Rows(1).Insert
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Font.Bold = True
.Cells(1, 2) = "NAME"
.Cells(1, 2).Font.Bold = True
.Columns("A:B").AutoFit
End With
End Sub
Next begun count of number of each shed number on every sheet
=SUMPRODUCT(COUNTIF(INDIRECT(SHEETNAME!A1:Z10000),SHED ##) and referenced the sheet name to search for specific shed number ##
Dragged across with search cell reference changing on each column
Returned count value
Used conditional formatting to visualise data and identify gaps / duplicates - manually assessed these incidences – e.g. row 24 had “SHED 3” twice, corrected this number to 4 shed count
=LOOKUP(2,1/(C2:AF2<>0),$C$1:$AF$1) to look backwards and identify last (first encountered) value greater than 0 and identify corresponding column heading from first row (shed number)
The above printed e.g. "SHED 11"
=NUMBERVALUE(RIGHT(AK2,2)) To extract from text
“11”
Some #N/A values, so
=IF(ISNUMBER(AL2),AL2,"")
Copied number of sheds into database
Used conditional formatting to block off extra sheds (0-30 sheds allowed)
--- INFO EXTRACTION ---
Address info always in cells A2, A3, A4, A5
Extracted each line from sheet using INDIRECT, checked ISTEXT
Copied into ADDR1 ADDR2 ADDR3 ADDR4
Contact name extracted from usual cells, validated using ISTEXT
Also copied contact information using the above technique, however the cell reference for these varied and so multiple instances were run in parallel (e.g. E1, F1) and ISNUMBER validation was used to remove "Mobile" and "Telephone" headings from results.
As Telephone was always recorded on the 3rd row, and Mobile on 2nd row, I simply varied the column letter to locate the contact details. This method was also used to extract contact names ISTEXT
This method was also used to extract lines of notes below the data, which typically began on row 21.
Here I used the formula on 5 rows beginning at row 21 to ensure nothing was missed
For the numerical data – SQFT SQMTR KG/1000 (spread depth) – I noticed that they also occurred at varying heights in typically the same column – C, E, and G respectively. Therefore I ran my incremental row data retrieval formulae sheet with the correct row selected, and TRUE FALSE validation for each variable ISNUMBER and a range of acceptable values (i.e) Spread depth would be acceptable 0 < KG/1000 < 200 - this avoided accidental entry of an incorrect value.
Finally I needed to retrieve the shed sizes from rows 12 / 13, with varying columns according to shed number. I did all Shed 1 sizes first, keeping column B constant and changing the selected row. I filtered for IFNUMBER values only and collated returned values from each row into a smaller table and converted into a numerical value. From here I was able to create a formula that ignored 0 values and allowed the results from each row of the specified column to be combined into one list (column X below) which I could easily copy over into the new database, whilst also identifying any gaps in data retrieval. There were a small handful of sheds which did not return a shed size, and so the data for these were collected manually.
ROW 12
STEP 1 =INDIRECT("'"&B5&"'!b12")
STEP 2 =IF(C5>0,ISNUMBER(C5),"")
STEP 3 =IF(D5=TRUE,C5,"")
STEP 4 =NUMBERVALUE(STEP 3)
STEP 5 =IF(ROW10=0,IF(ROW14=0,IF(ROW13=0,IF(ROW12=0,"",ROW12),ROW13),ROW14),ROW10)
STEP 5 created a single list which was easily copied into the new database
CONCLUSION & FEEDBACK
By far the most difficult aspect of this project was the data collection, validation and organisation. This was due to the non-uniform nature of the data set, and the variance with each record.
Once this data had been collected, it was a relatively straightforward process to design the new calculator and link it to the newly built database via VLOOKUP, with the site names (vertical search queries) on the left-most column
For ease of use, conditional formatting and data validation were used extensively. The final calculator has only one input required of the end user - the site name.
Additional sites may be added to the database easily and conditional formatting was also used here to guide the user to input a complete set of data for the new entry
A brief training session was given to each user, however as the spreadsheet and all formulae are protected (excluding user input areas) I knew it was best to share the sheet with the users and allow them to become familiar with it
Certain cells are fully protected (headings, the whole calculator sheet excl. site name input). The database may be added to by select individuals, and the notes, contact, and address details may be updated by anyone.
Overall, the feedback received was that the new calculator was very intuitive, more aesthetically pleasing, and contained all requirements of the system.