Generatin Unique Numbers to Calculate Average Days

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,665
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Hi howard

Have you tried a Pivot Table? A Pivot Table will generate automatically a summary table for your models, where you can specify averages, sums and counts for each attribute of each model.

HTH
PGC
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,665
Office Version
  1. 2019
Platform
  1. Windows
Will try using Pivot Tables. Never thought about going this route.

Howard
 

Watch MrExcel Video

Forum statistics

Threads
1,114,385
Messages
5,547,650
Members
410,805
Latest member
Ginoji
Top