PROBLEM DESCRIPTION: calculate work hours, split between day and night shift

The Excel user required to determine the number of hours that employees worked, split between day shift and night shift. The parameters for the different shifts :

• day shift : 06:00 to 18:00
  
• night shift : 18:00 to 06:00

The data only contained times only, so a shift like 17:00 to 07:00 runs into the following day. In addition, the solution was required to be finalised as a formula as opposed to VBA

PROPOSED SOLUTION :

A sample spreadsheet was compiled. The night shift was split into 2 sections, namely night1 which is from 00:00 to 06:00 and night2 which is from 18:00 to 24:00. The day shift is a continuous block so no need to split it.

Once this was defined, a further calculation was done to determine whether the shift ran into the following day. After this was done, 12 possible scenarios were identified. Formula was written to calculate the day and night hours separately. This produced a fairly ugly nested IF statement.

As a comparison, the solution was developed in VBA. This in my opinion is clearer, in terms of showing the logic of the calculation. This is activated by the button on the spreadsheet.

link to download solution

PROBLEM DESCRIPTION The Excel user wanted to search for duplicate items in a list of data, in order to consolidate these data and then get rid of the duplicates. The definition of duplicate was to get a match on both the 1st and 2nd columns.

The consolidation involved summing the values in the 3rd column. There was further calculation required for the 4th column data element but this was not defined, so a placeholder was left in the form of a comment. The final step to remove the duplicate item/s (rows) from the list.

PROPOSED SOLUTION :

A couple of variables were declared and initialised. The one defined the last row of the data, which was used twice, both in the main loop as well as the search loop.

The search was a simplistic comparison of the values and where matched, the second value is compared. A flag is set when it is determined that there is a duplicate. The logic of what happens with duplicate is contained within the check of the flag.

Further ideas The search could be replaced by Excel's Range.Find method which removes the need to have the loop for the search. Just add the appropriate error handling and .FindNext to complete this approach.

link to download solution

PROBLEM DESCRIPTION : search for number of matches

For the gambler in you, the request was to examine and compare lotto numbers against a previous years lotto results. The result should show how many times each new lotto number had matched the historical lotto results. The matches of significance were 3 or more numbers of the lotto number matching the old numbers (presumably resulting in some level of winnings).

A spreadsheet of these numbers was provided, with the left side showing the old numbers and the right side showing the new numbers.

PROPOSED SOLUTION :

An array was used to store the new lotto number that would be the "source". The target was the list of old lotto numbers. The last row of data for both the source and target list of lotto numbers was obtained and these were used for the loops. An additional loop was created to cycle through the 6 individual number making up the lotto number.

For every match, a score was incremented and on completion of the inner loop through the 6 individual numbers, a check was done to see if there was a match of 3 or more numbers and the main scores were updated accordingly.

This continued until the end of the loop, and the main scores were then written along the source lotto number. The next source lotto number was then loaded and repeat of the loops performed.

Further ideas The target lotto numbers could also be stored in an array, actually the whole list of old lotto numbers could be stored in an array. This should speed up the searches, possible desirable if you are sitting with a long list of numbers and don't want to refill your coffee and catch some fresh air, while waiting for the program to finish.

link to download solution

PROBLEM DESCRIPTION : update other worksheets from central list

The need to create a central place to maintain a list of people, instead of having to update these people on all the worksheets. It is assumed that the layout of the data remains static on the worksheets and the list is written to the same place.

PROPOSED SOLUTION :

A button was added to the master worksheet. This was linked to the VBA solution. It does a simple loop.

Further ideas The other worksheets may be layed out differently and then you could either define a named range with the location or use the Range.Offset to get the right place to set the values.

link to download solution

PROBLEM DESCRIPTION : record times spent on different activities

The problem was to create a clock card system in Excel, i.e. to record the times when a person starts, gets busy with different activities and when they stop work. This data would then be used to calculate the total hours and split into the different activities.

PROPOSED SOLUTION :

4 buttons were added on a worksheet. These are : " start the clock " production " meeting " stop the clock

A different sub routine was created for each button, to make it a little more extendible for other activities as these may be required.

A reporting worksheet provides the user with a daily report. This has been added with the date as the input value and then calculates the times until the total time has been reached.

PROBLEM DESCRIPTION : string manipulation based on rules

This next problem was to re-format a text string, based on a set of rules. These rules were to break the string and insert a carriage return either after the : " the 5th character " after a comma ',' " after a full stop '.'

PROPOSED SOLUTION :

A couple of variables were defined, including the 2 stop characters. A loop was created to run through the string and check at each place, whether one of the stop characters have been found or we have hit the 5th character. There were 2 stops defined and used in the rule

link to download solution

PROBLEM DESCRIPTION : increment quantity on entering barcode

This is a piece of a bigger solution. The reader wanted to use a barcode scanner to scan items received and Excel would update the quantities of those items, where matched. An extention was added to add the new barcode, should it not be found in the existing list.

PROPOSED SOLUTION :

A button was added on the worksheet to activate the macro. This would take the new input and check whether it could be found in the range of existing barcodes. If it was found, the associated quantity value was incremented by 1. It relies on the cell containing the new input to remain on cell G2. If a user would attach a barcode reader with Excel running and that cell in focus, the input of the reader would be written to that cell.

In the case of a new barcode, this new barcode is added to the list, with the quantity of 1. Subsequently it is not longer a new barcode and is treated as an existing barcode.

link to download solution

In addition, I have put together a small Excel based quoting and invoicing system. I have illustrated its functionality through a workflow diagram as well as making this available. It could be extended to be more capable, however it is more of an illustration of how Excel could be used to automate your sales department activities.