I have a spreadsheet (see example below), where I need to calculate the average days for each type of vehichle. I would like to generate a unique number for each type of description for eg 1.3 JTD Jet 5dr , 1400 LDV Champ Etc in the column next to the the column showing the ageing. I would then need to have a column to calulate the average days as well as a column to calculate the number of vehicles per the unique number i.e the total of the 1400 LDV STD, 1.3 JTD M/Jet 5DR etc.
I would like to set up VBA code that will automate adding the extra columns starting from column U to W and to do the necessary calculations.
The extra columns that need to be added are:
1) Unique No-this is based on the description. For each unique description a number must be automatically generated (Column U) for eg all the 1400 LDV Std's must be given a number for eg 2
2) Average Days (Column V) Need formula to calculate this based on unique number-The average days must be calculated for each type of vehicle based on the unique number
3) Number of Vehicles (Column W) Need formulate to calculate the number of vehicles per unique number for eg all the unique numbers with a 1 must be calculted , all the 2's must be totalled etc.
Description Basic costs DFA Total Age
1.3 JTD M/JET 5DR 125,000 417 125,417 72
1400 LDV CHAMP 60,000 177 60,177 315
1400 LDV STD 55,000 467 55,467 49
1400 LDV STD 57,000 756 57,756 49
1400 LDV STD 57,000 768 57,768 49
1400 LDV STD 57,000 651 57,651 57
1400 LDV STD 57,000 493 57,493 57
1400 LDV STD 57,000 446 57,446 57
1400 LDV STD 57,000 463 57,463 64
1400 LDV STD 57,000 1,838 58,838 64
1400 LDV STD 57,000 458 57,458 64
1400 LDV STD 57,000 3,688 60,688 73
1400 LDV STD 57,000 458 57,458 94
1400 LDV STD 57,000 451 57,451 94
1400 LDV STD 57,000 0 57,000 97
1400 LDV STD 57,000 433 57,433 101
1400 LDV STD 57,000 433 57,433 108
1400 LDV STD 57,000 413 57,413 115
1400 LDV STD 57,000 433 57,433 115
1400 LDV STD 57,000 6,392 63,392 156
1400 LDV STD 57,000 421 57,421 185
1400 LDV STD 57,000 3,296 60,296 199
2.4 2X4 S/C LWB SE 125,000 583 125,583 64
2.4 2X4 S/C LWB SE 125,000 0 125,000 70
2.4 4X2 D/CAB SE 170,000 1,005 171,005 49
2.4 4X2 D/CAB SE 180,000 1,006 181,006 57
2.4 4X2 D/CAB SE 185,000 991 185,991 73
2.4 4X2 D/CAB SE 170,000 1,009 171,009 73
2.4 4X2 D/CAB SE 165,000 1,048 166,048 73
2.4 4X2 D/CAB SE 165,000 998 165,998 73
2.4 4X2 D/CAB SE 165,000 978 165,978 115
2.4 4X2 D/CAB SE 165,000 949 165,949 115
2.4 4X2 D/CAB SE 165,000 894 165,894 115
2.4 4X2 D/CAB SE 165,000 1,165 166,165 115
3.0 4X2 LWB SE 165,000 624 165,624 258
4.2TD4X4 PICK-UP + HEAT 165,000 21,450 186,450 85
ALFA 159 1.9JTS PROGRESS 165,000 1,100 166,100 108
ALMERA 1.6 COMFORT MT 165,000 821 165,821 176
ALMERA 1.6 COMFORT MT 165,000 899 165,899 220
ALMERA 1.6 LUXURY 165,000 9,778 174,778 48
ALMERA 1.6 LUXURY 165,000 804 165,804 182
ALMERA 1.6 LUXURY 165,000 332 165,332 184
ALMERA 1.6 LUXURY 165,000 445 165,445 204
ALMERA 1.6 LUXURY A/T 165,000 664 165,664 457
Yours assistance with the above will be most appreciated.
Howard
I would like to set up VBA code that will automate adding the extra columns starting from column U to W and to do the necessary calculations.
The extra columns that need to be added are:
1) Unique No-this is based on the description. For each unique description a number must be automatically generated (Column U) for eg all the 1400 LDV Std's must be given a number for eg 2
2) Average Days (Column V) Need formula to calculate this based on unique number-The average days must be calculated for each type of vehicle based on the unique number
3) Number of Vehicles (Column W) Need formulate to calculate the number of vehicles per unique number for eg all the unique numbers with a 1 must be calculted , all the 2's must be totalled etc.
Description Basic costs DFA Total Age
1.3 JTD M/JET 5DR 125,000 417 125,417 72
1400 LDV CHAMP 60,000 177 60,177 315
1400 LDV STD 55,000 467 55,467 49
1400 LDV STD 57,000 756 57,756 49
1400 LDV STD 57,000 768 57,768 49
1400 LDV STD 57,000 651 57,651 57
1400 LDV STD 57,000 493 57,493 57
1400 LDV STD 57,000 446 57,446 57
1400 LDV STD 57,000 463 57,463 64
1400 LDV STD 57,000 1,838 58,838 64
1400 LDV STD 57,000 458 57,458 64
1400 LDV STD 57,000 3,688 60,688 73
1400 LDV STD 57,000 458 57,458 94
1400 LDV STD 57,000 451 57,451 94
1400 LDV STD 57,000 0 57,000 97
1400 LDV STD 57,000 433 57,433 101
1400 LDV STD 57,000 433 57,433 108
1400 LDV STD 57,000 413 57,413 115
1400 LDV STD 57,000 433 57,433 115
1400 LDV STD 57,000 6,392 63,392 156
1400 LDV STD 57,000 421 57,421 185
1400 LDV STD 57,000 3,296 60,296 199
2.4 2X4 S/C LWB SE 125,000 583 125,583 64
2.4 2X4 S/C LWB SE 125,000 0 125,000 70
2.4 4X2 D/CAB SE 170,000 1,005 171,005 49
2.4 4X2 D/CAB SE 180,000 1,006 181,006 57
2.4 4X2 D/CAB SE 185,000 991 185,991 73
2.4 4X2 D/CAB SE 170,000 1,009 171,009 73
2.4 4X2 D/CAB SE 165,000 1,048 166,048 73
2.4 4X2 D/CAB SE 165,000 998 165,998 73
2.4 4X2 D/CAB SE 165,000 978 165,978 115
2.4 4X2 D/CAB SE 165,000 949 165,949 115
2.4 4X2 D/CAB SE 165,000 894 165,894 115
2.4 4X2 D/CAB SE 165,000 1,165 166,165 115
3.0 4X2 LWB SE 165,000 624 165,624 258
4.2TD4X4 PICK-UP + HEAT 165,000 21,450 186,450 85
ALFA 159 1.9JTS PROGRESS 165,000 1,100 166,100 108
ALMERA 1.6 COMFORT MT 165,000 821 165,821 176
ALMERA 1.6 COMFORT MT 165,000 899 165,899 220
ALMERA 1.6 LUXURY 165,000 9,778 174,778 48
ALMERA 1.6 LUXURY 165,000 804 165,804 182
ALMERA 1.6 LUXURY 165,000 332 165,332 184
ALMERA 1.6 LUXURY 165,000 445 165,445 204
ALMERA 1.6 LUXURY A/T 165,000 664 165,664 457
Yours assistance with the above will be most appreciated.
Howard
