How can I filter duplicates from a worksheet column out of a data validation list drop-down box?

[TePunaBloke]

Hi folks,

I'm running Excel 2000 with a legacy spreadsheet which has a single worksheet. Each row contains the details of a given task performed by a particular farm vehicle. The row includes the day. month and year that each job was performed (in separate columns), along with a brief job description of the job and the number of hours taken to complete the job.

One additional column contains the year and month that the job was performed, expressed as a padded 6-digit value in the format: yyyymm. Several rows may contain the same value (e.g. jobs performed in October 2024 all have a column value of: 202410) and this is perfectly fine. The number of tasks varies per month and I never know how many tasks are performed in a month.

I want to create a drop-down list which references a column subset range (e.g. Cells: C12:C100) - but crucially I want the drop-down validation list NOT to contain duplicate entries for all the tasks in the worksheet.

For example: if the worksheet contains 2 tasks for August 2024, 3 tasks for September 2024 and 4 tasks for October 25, I want the drop-down list to contain just 3 values:-

• 202408
• 202409
• 202410

...and not:-

• 202408
• 202408
• 202409
• 202409
• 202409
• 202410
• 202410
• 202410
• 202410

Is this possible to do with the application of a formula in the "Source:" field of the Data Validation list?

If there is no formula-based solution, I would consider a VBA alternative if necessary.

I'd love to move to a more recent version of Excel, but the system being used is an old Windows 7 box which contains a fair amount of legacy software that is still in use. This limits my options somewhat.

Any and all suggestions and advice are very much appreciated.

My thanks in advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

I can't remember for sure what functions were available in your version, but give this a try.
I have assumed that those yyyymm values are actual numbers and not text.

Set up helper columns like I have in D:E (these columns can then be hidden) and use the Data Validation shown

24 11 05.xlsm
	A	B	C	D	E
12			202408	202408	$D$12:$D$14
13			202408	202409
14			202409	202410
15			202409
16			202409
17			202410
18			202410
19			202410
20			202410
21
DV List

Cell Formulas
Range	Formula
E12	E12	=ADDRESS(ROW(D12),COLUMN(D12))&":"&ADDRESS(ROW(D12)+COUNT(D12:D20)-1,COLUMN(D12))
D12:D20	D12	=IFERROR(INDEX(C$12:C$20,AGGREGATE(15,6,(ROW(C$12:C$20)-ROW(C$12)+1)/NOT(ISNUMBER(MATCH(C$12:C$20,D$11:D11,0))),ROWS(D$12:D12))),"")

Cells with Data Validation
Cell	Allow	Criteria
A12:A16	List	=INDIRECT($E$12)

When using a DV cell:

 

[TePunaBloke]

Thank you for your prompt reply Peter. I'm getting a #NAME? return which I think is down to the: AGGREGATE() function call...looks like this function was not available in Excel 2000. All other functions used in the formula appear to be supported in Excel 2000.

Based on: the Microsoft Description of the AGGREGATE() function it appears that the AGGREGATE(15,6,...) function call with these parameters refers to the SMALL() function with an: Ignore error values option.

I tried using:

=IFERROR(INDEX(C$12:C$20,SMALL((ROW(C$12:C$20)-ROW(C$12)+1)/NOT(ISNUMBER(MATCH(C$12:C$20,D$11:D11,0))),ROWS(D$12:D12))),"")

...but still got the: #NAME? issue.

here's a cut-down version of the result.

 

[Peter_SSs]

I'm getting a #NAME? return which I think is down to the: AGGREGATE()

Oops, yes, AGGREGATE did not come along until version 2010. IFERROR may be a problem too.

Try this instead. This is an array formula so must be confirmed with Ctrl+Shift+Enter, noy just Enter. We shouldn't need to worry about the errors at the bottom since the column could be hidden anyway.

24 11 05.xlsm
	A	B	C	D	E
11
12			202408	202408	$D$12:$D$14
13			202408	202409
14			202409	202410
15			202409	#N/A
16			202409	#N/A
17			202410	#N/A
18			202410	#N/A
19			202410	#N/A
20			202410	#N/A
21
DV List

Cell Formulas
Range	Formula
E12	E12	=ADDRESS(ROW(D12),COLUMN(D12))&":"&ADDRESS(ROW(D12)+COUNT(D12:D20)-1,COLUMN(D12))
D12:D20	D12	=INDEX(C12:$C$20,MATCH(0,--ISNUMBER(MATCH(C12:$C$20,D$11:D11,0)),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A12:A16	List	=INDIRECT($E$12)

 

[TePunaBloke]

Thank you Peter - That worked perfectly! very much appreciated.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.