Removing duplicate entries from a data validated drop down list in a three tier Cascading List.

[AirportAndy71]

I've managed to create a spreadsheet for subcontractors (i work in construction) to auto generate document numbers from 18 mandatory metadata fields (Only 8 fields fill the Document number but the others are mandatory to the EDMS system we use (Aconex)).

One section is looking for specific codes to the job and has three child cascading levels after the first is picked and obviously the 2nd field drop-down depends on the parent (1st Field) and the 3rd field drop-down depends on the 2nd field drop-down and the 4th field drop-down depends on the 3rd field drop-down.

I have a hidden sheet with all the valid metadata on it and the data validation formulas i've used to look for these once the field above has been chosen are as follows:

=OFFSET(AFC!$B$1,MATCH(L2,AFC!$B:$B,0)-1,1,COUNTIF(AFC!$B:$B,L2),1)
=OFFSET(AFC!$C$1,MATCH(M2,AFC!$C:$C,0)-1,1,COUNTIF(AFC!$C:$C,M2),1)
=OFFSET(AFC!$D$1,MATCH(N2,AFC!$D:$D,0)-1,1,COUNTIF(AFC!$D:$D,N2),1)

My problem is the cascading sheets have duplicates because each field chosen has multiple choices and each of those choices have again multiple choices and so on down the line.

And in the drop downs for the 2nd, 3rd & 4th in the series show these and id rather they just showed a single instance of each. I cant remove duplicates from the original lists as i need the formulas above to look for multiple entries and show whats related once chosen (Hence the COUNTIF).

Is there a way to have the drop downs not show multiples?

This is a small example of what my hidden data looks like for reference:

AFC	selectList5	selectList6	selectList7
100 Asset Function Group	100 Asset Function Group	100 General Asset	000 N/A
200 Building Structure & Fabric	100 Asset Function Group	118 Navaids	000 N/A
300 Baggage	100 Asset Function Group	118 Navaids	140 Control Panel
400 Safety Security & Fire	100 Asset Function Group	118 Navaids	222 Monitoring Device
500 Information & Communications	100 Asset Function Group	118 Navaids	222 Monitoring Device
600 People Related Transport	100 Asset Function Group	118 Navaids	259 Approach Radar
700 Areas, Railways. Vehicles & Intangibles	100 Asset Function Group	118 Navaids	303 Instrument Landing Sys ILS
800 Services & Utilities	100 Asset Function Group	118 Navaids	307 Instrumented Runway Visual Range IRVR
900 Project Management & Controls	100 Asset Function Group	118 Navaids	325 Application Software
	100 Asset Function Group	118 Navaids	326 Microwave Landing Sys MLS
	100 Asset Function Group	118 Navaids	397 Surface Radar
	100 Asset Function Group	118 Navaids	436 VHF UHF Aerials
	100 Asset Function Group	118 Navaids	445 Windsock-Windsleeve
	100 Asset Function Group	125 Runway Crossing	000 N/A
	100 Asset Function Group	125 Runway Crossing	073 Cable
	100 Asset Function Group	125 Runway Crossing	140 Control Panel
	100 Asset Function Group	125 Runway Crossing	150 Light Fitting Airfield Road
	100 Asset Function Group	125 Runway Crossing	321 Mast
	100 Asset Function Group	125 Runway Crossing	424 Traffic Lights

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

Many thanks in advance..

 

[AirportAndy71]

Just Bumping in case anyone missed this..