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.

RATIO OF SHAVINGS: CHIP MIXTURE

DATA INPUT SHEET

Better UI for data entry - able to see a larger range of data for screen size

Conditional formatting with =today() which highlights todays date for easy data entry

Frozen top row for scrolling by week

QUALITY CHECK SHEET

Data entry fields with validation and drop down lists for specific entries

Originally planned to have buttons run scripts which would save this data to a different database sheet and store it there, along with a similar clear script which would clear the current form

Realised that buttons would not work in the iOS sheets app so created a custom menu drop down which could be used when running sheets in the browser in desktop mode

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  // Or DocumentApp or FormApp.

  ui.createMenu('Willems Data')

      .addItem('Save', 'submitData')

      .addItem('Clear', 'clear1')

      .addToUi();

}

function submitData() {

  var ss        = SpreadsheetApp.getActiveSpreadsheet();

  var formSS    = ss.getSheetByName("Data Entry"); //Data entry Sheet

  var datasheet = ss.getSheetByName("Data"); //Data Sheet

  //Input Values

  var values = [[formSS.getRange("F6").getValue(),

                 formSS.getRange("F8").getValue(),

                 formSS.getRange("F10").getValue(),

                 formSS.getRange("F12").getValue(),

                 formSS.getRange("F14").getValue(),

                 formSS.getRange("F16").getValue(),

                 formSS.getRange("F18").getValue(),

                 formSS.getRange("F20").getValue(),

                 formSS.getRange("F22").getValue(),

                 formSS.getRange("F24").getValue(),

                 formSS.getRange("F26").getValue(),

                 formSS.getRange("F28").getValue(),

                 formSS.getRange("F30").getValue()]];

  datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 13).setValues(values);

}

function clear1() {

  var sheet = SpreadsheetApp.getActive().getSheetByName('Data Entry');

  var rangesToClear = ['F6', "F8", "F10", "F12", "F14", "F16","F18","F20","F22","F24","F26","F28","F30"];

  for (var i=0; i<rangesToClear.length; i++) { 

    sheet.getRange(rangesToClear[i]).clearContent();

    var getCar = sheet.getRange("D6").getValue();

    sheet.getRange("F6").setValue(getCar);

  }

}

function onEdit(e) {

  e.source.setActiveSelection(e.range.offset(2, 0));

}