To Automate the Sequencing / Lotting of Data

Hi All,

I would be very much obliged if anyone could help with my query. I work for a motor auction company and one of my most time consuming jobs is lotting / sequencing the order of vehicles to which they will run in the auction.

I am provided with a list of vehicles giving the reg number, mode, derivative and colour.

I try to spread out each vehicle model evenly throughout the auction (total number of vehicles) e.g. Focus - 25, Mondeo - 7, Fiesta 11, Galaxy - 7

1 FOCUS
2 FIESTA
3 FOCUS
4 GALAXY
5 FOCUS
6 FIESTA
7 FOCUS
8 MONDEO
9 FOCUS
10 FIESTA
11 FOCUS
12 GALAXY
13 FOCUS
14 MONDEO
15 FOCUS
16 FIESTA
17 FOCUS
18 GALAXY
19 FOCUS
20 FIESTA
21 FOCUS
22 MONDEO
23 FOCUS
24 GALAXY
25 FOCUS
26 FIESTA
27 MONDEO
28 FOCUS
29 FIESTA
30 FOCUS
31 MONDEO
32 FOCUS
33 GALAXY
34 FOCUS
35 FIESTA
36 FOCUS
37 MONDEO
38 FOCUS
39 FIESTA
40 FOCUS
41 GALAXY
42 FOCUS
43 MONDEO
44 FOCUS
45 FIESTA
46 FOCUS
47 GALAXY
48 FOCUS
49 FIESTA
50 FOCUS

What I then do is sort by the model (column B) then the lot number (column A) to have each model grouped together in Lot order. e.g.

2 FIESTA
6 FIESTA
10 FIESTA
16 FIESTA
20 FIESTA
26 FIESTA
29 FIESTA
35 FIESTA
39 FIESTA
45 FIESTA
49 FIESTA
1 FOCUS
3 FOCUS etc etc

I then repeat this procedure but this time in order spread out the derivative within each model grouping e.g.:

MONDEO SPORT
MONDEO SPORT
MONDEO TITANIUM
MONDEO TITANIUM
MONDEO ZETEC
MONDEO ZETEC
MONDEO ZETEC

Results:

8 MONDEO ZETEC
14 MONDEO SPORT
22 MONDEO TITANIUM
27 MONDEO ZETEC
31 MONDEO TITANIUM
37 MONDEO SPORT
43 MONDEO ZETEC


Once all of the models and derivatives are spread out I then manually review the number of colours witihin the allocation, then cut and paste the vehicles details from my auction allocation with registration number etc, next to each lot position e.g.

1 FOCUS ZETEC ABC123 FOCUS ZETEC BLACK
2 FIESTA ZETEC ABC124 FIESTA ZETEC RED
3 FOCUS ECONETIC ABC125 FOCUS ECONETIC BLACK
4 GALAXY ZETEC ABC126 GALAXY ZETEC BLUE
5 FOCUS ZETEC ABC127 FOCUS ZETEC RED
6 FIESTA FINESSE ABC128 FIESTA FINESSE BLACK
7 FOCUS ST ABC129 FOCUS ST BLUE
8 MONDEO ZETEC ABC130 MONDEO ZETEC RED
9 FOCUS ZETEC ABC131 FOCUS ZETEC BLACK
10 FIESTA TITANIUM ABC132 FIESTA TITANIUM SILVER
11 FOCUS ECONETIC ABC133 FOCUS ECONETIC RED
12 GALAXY TITANIUM ABC134 GALAXY TITANIUM BLACK
13 FOCUS ZETEC ABC135 FOCUS ZETEC BLUE
14 MONDEO SPORT ABC136 MONDEO SPORT BLACK
15 FOCUS TITANIUM ABC137 FOCUS TITANIUM RED
16 FIESTA ZETEC ABC138 FIESTA ZETEC BLUE
17 FOCUS ZETEC ABC139 FOCUS ZETEC BLACK
18 GALAXY ZETEC ABC140 GALAXY ZETEC RED
19 FOCUS ST ABC141 FOCUS ST BLACK
20 FIESTA FINESSE
21 FOCUS ZETEC ETC ETC

As you can imagine when I have over 200 vehicles to lot the calculation of working out where each vehicle will sit, along with how the derivative will sit within each model group, to then cut and paste each vehicle into the assigned order one by one, whilst manually accounting for the colour of each vehicle/the most common colour within the entire vehicle allocation, is painfully time consuming. Please note that I try and also separate the colours of the vehicles as they are manually assigned so no two cars with the same colour follow on from each other unless mathematically impossible.

In conclusion I have 3 variables, Model, Derivative and colou to spread out.

I currently use a spreadsheet that will spread out the data but it can only handle one variable. This spreadsheet was created by a colleague who left before being able to finish the project.

I would be sincerely grateful if anyone could provide any advise if excel has the capability of automating this task as I am desperately trying to find ways in which to speed up my work and increase efficiency in an attempt to avoid unemployment.

May I thank you in advance for taking the time to read this thread. Happy New Year.

Hi Richard,

Please find attached an excel spreadsheet (Skoda Catalogue Lotting 2013) which shows a breakdown of the data I get and how I work with it in order to achieve the finished result.

Worksheet 1 - Is the list of vehicle data (in a basic format) with reg number, model and colour.
Worksheet 2 - Shows a summary of the data
Worksheet 3 - Provides a colour breakdown, highlighting the most prevalent
Worksheet 4 - Illustrates how the models are initially divided/spreadout into the total entry. To perform this I use the secong spreadsheet attached (lotting v4 Skoda 2013)

Lotting v4 Skoda 2013

https://www.yousendit.com/dl?phi_act...07340200000000[/COLOR]

is a spreadsheet that a colleague initially started working on but unfortunately left before being able to take it any further.
On the 'Input' worksheet I populate column B and C with TBA (to be advised) as there have to be populated with something. Column C is then populated by how many od each model there is to be lotted. On the next worksheet 'Ref' you have to update column C with each model name. Note that both 'Input' and 'Ref' will only read one word on the cell thereofre you could not have Skoda Fabia as it would just read 'Skoda'.
The 'Ref' worksheet shows the total number of each model.
Worksheet 4 'Visual' provides an over view of where each model sits. If there are two models that sit next to each other the worksheet will highlight the vehicles in Red.
Worksheet 5 'Results' provides the finished running order which is then copied and pasted into a new spreadsheet, for this example it has been copied into Skoda Catalogue Lotting 2013 Worksheet 4 Separate Model.

Once the models have been sepearted (Skoda Catalogue Lotting 2013) the spreadhseet is sorted by Model and Lot number and then the derivative is divided equally using the same principal as above where a list of the derivatives is created with TBA coloumns either side and pasted into the Lotting v4 Skoda 2013 spreadsheet, not forgetting to update the 'Ref' worksheet.

Once the list has been created the vehicle entry from worksheet 1 'Simplified List' is copied next to the running order and each vehicle is manually cut and pasted next to its designated lot - Skoda Catalogue Lotting 2013 Worksheet 6 Allocate Car to Lot. It is at this stage you have to be mindful of the most common colours.

This whole task can take from 1 hour to 3 hours. The principals of the 'Lotting v4 Skoda 2013' spreadsheet provides a basis where the information can be sorted automatically, however it can only account for one variable. What I need Excel to do is to be able to separate the Models and derivatives then calculate each individual model can go based on its colour, thus allocating a lot number to each individual Registration.

I appreciate that this may not all be done in excel, but I would be much obliged if anyone could help as I desperately need to speed up this aspect of my work.

Many thanks to you all.

Hi Richard,

If you ignore the Lotting V4 Skoda spreadhseet that may make things clearer. This is the spreadsheet that was developed by my colleague and is solely used at present as a means to automate the division of whatever data is added into the 'Input' Worksheet.

Skoda Catalogue Lotting 2013 Spreadsheet - Worksheet 1: Simplified List - This is the basic data I start with.

The 'Model Breakdown' and 'Colour Breakdown' worksheets are simply there to visiually indicate how the number of vehicles, derivatives and colours are counted/summarised.

The '1 Separate Model' spreadhseet demonstrates what the running order of the models looks like when divided by the total auction entry of 93 cars. E.g. If there were 50 Fabia out of a 100 car entry, then there would be a Fabia Model evey other lot number.

This procedure is then repeated in order to evenly distribute the derivative of each car within the Model grouping. Therefore out of our 50 Fabia cars, if there were 25 Elegance derivatives, then there would be an Elegnace derivative evey other lot number within the grouping of Fabias.

'3 Allocate Car to Lot' worksheet provides a visual of the manual task, this is where 'Worksheet 1: Simplified List' is pasted next to the calculated running order. Each individual car is then cut and pasted from the right side to a designated lot number. So Lot 1 is a Fabia1, therefore I go to the list on the right hand side, find a Fabia and then cut and paste it next to Lot 1. Lot 2 is an Octavia Elegance, therefore I go to the list on the right hand side, find an Octavia Elegance and then cut and paste it next to Lot 2. Each time I do this I am aware of the previous colour and look for a different colour for the next lot, to avoid having two cars of the same colour next to each other.

The final Worksheet 'Result' shows the finished lotting order once all the individual cars have been cut and pasted next to a lot number. If you refer back to worksheet 3 'Colour Breakdown' you will see that Grey and Silver are the most common colours. I have therefore highlighted them on the finished running order to demonstrate how I have been mindful of the prevalent colours when cutting and pasting.

I hope this makes things a little clearer.....many thanks for your co-operation and patience.

Hi,

So is the essential task
1. Given the Simplified List data & the full description from column H on the 'Allocate Car to Lot' sheet you want to end up with the Results sheet? - CORRECT

2. The order on the Results sheet is driven by the need to have the cars spaced by model as per the order on the '1.Separate Model' sheet, and then the derivative of each model is spaced out within the model allocation. But in which case why for instance are the 6 Ambiente derivatives of of the Octavia located at lot numbers 6, 16, 43, 55, 62 & 76 and not at say 6, 24, 39, 57, 74, & 90 which would be a more even distribution. - I CREATED THIS EXAMPLE BASED ON AN AUCTION THAT WAS ALREADY LOTTED AND WORKED BACKWARDS. THESE WOULD HAVE BEEN SPACED OUT EQUALLY BUT THEY WOULD HAVE BEEN MOVED MANUALLY TO AVOID COLOURS CLASHING. BUT FOR THE PURPOSE OF THIS EXERCISE TAKE IT THAT ALL DERIVATIVES IN EACH MODEL GROUPING ARE EQUALLY SPACED.

3. The Model Breakdown and Colour breakdown are simply incidental summaries of the basic data. - CORRECT, WHEN MANUALLY CUTTING AND PASTING THE CARS NEXT TO THEIR CALCULATED LOT, IS GOOD TO BE AWARE OF THE MOST COMMON COLOURS TO AVOID PUTTING CARS OF THE SAME COLOUR TOGETHER

Questions
4. In the '3 Allocate Car to Lot' sheet, are the four cars in C1:E4 there because they were originally in columns G:I and you have already cut and pasted them? - CORRECT

5. What determines why you have selected these four? i.e. what thought process do you go through to identify them. - AT THIS POINT THE COLOUR IS THE MAIN FACTOR. IF THERE IS A HIGH PERCENTAGE OF ONE COLOUR I WILL TRY AND DISTRIBUTE THESE AS EQUAL AS I CAN BASED ON THE HOW THE MODELS HAVE BEEN SPREAD OUT TO AVOID ENDING UP WITH CARS OF THE SAME COLOUR TOWARDS THE END OF THE LOTTING PROCESS.

6. Do whatever appears in columns C:E, (in this case these 4 cars. always appear first in the Results sheet? - PRETTY MUCH YES. THE RESULT SHEET PROVIDES AND EXAMPLE OF HOW THE LOTTING LOOKS ONCE CARS HAVE BEEN ALLOCATED. IT IS AT THIS POINT FURTHER MANUAL MANIPULATION IS REQUIRED TO ENSURE THAT COLOURS ARE SPACED WHICH DOES SOMETIMES COMPROMISE THE DERIVATIVE, HENCE THIS IS PROBABLY WHY THE AMBIENTE DERIVATIVES ARE NOT AS WELL SPACED AS THEY HAVE BEEN CHANGED AROUND TO AVOID COLOURS CLASHING.

It is very much appreciated that you are taking the time and effort to make relevant and vaulable enquiries to assist your understanding. Sorry for the late reply I am just snowed under, hence my desperation to investigate the possibilities of automating this task. If it helps I do have some technical documentation that our IT department collated but the project was cancelled due to cost.

Have a good day. ;o)