0

So this question has lots of ways of generating a list of unique entries in a spreadsheet. And I have used this reference to generate many a unique list solutions for other questions.

These solutions rely on row numbers to pick the position of the first occurrence of an item from an original list. At least that is my understanding. My question is it possible to put the list of unique entries directly into a defined name without actually displaying the list?

Example data

Species 
combination         | Grade
Douglas fir-Larch   | SS
Douglas fir-Larch   | No. 1
Douglas fir-Larch   | No. 2
Hem-Fir             | SS
Hem-Fir             | No. 1
Hem-Fir             | No. 2
Spruce-Pine-Fir     | SS
Spruce-Pine-Fir     | No. 1
Spruce-Pine-Fir     | No. 2
Northern species    | SS
Northern species    | No. 1
Northern species    | No. 2

Now I would like to generate a unique list in the defined name Species and defined name Grade. The idea is I can use the defined names either for a data validation drop down list or in a formula:

=INDEX(Species,3)
or
=INDEX(Grade,2)

Data Validation

and I would get a result like:

Spruce-Pine-Fir
or
No. 1
  • I am trying to achieve this without the use of VBA or Helper cells.
  • The species list can be sorted if it makes life easier. The grade list should be in the order shown above.
  • The source list (ie table above) will be on a separate worksheet (not workbook...still toying with that idea).

The formula I was looking at using but wont work as a defined name formula.

=INDEX($A$6:$A$17,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$6:$A$17),0,0),0))

Define Name

Community
  • 1
  • 1
Forward Ed
  • 9,484
  • 3
  • 22
  • 52

1 Answers1

1

1) I will point out that for data validation lists Excell will automatically limit to unique values, so for that you can just refer to the whole column / range of Species

2) You might be able to achieve what you need using UNIQUEVALUES() function from the MOREFUNC ADDON (see below)

MOREFUNC ADDON

  • Morefunc Addon is a free library of 66 additional excel worksheet functions.
  • HERE is some information (by original author; website has since been removed / abandoned, hence the WaybackMachine version)
  • here is the last working download link I found
  • here is a good installation walk-through video
user3616725
  • 3,485
  • 1
  • 18
  • 27
  • Interesting. I have tried to track this add-on down in the past, and there is something in the back of my head that says it has issues with 64 bit versions...which may be ok as I have 32 bit office but 64 bit windows 10 – Forward Ed Jun 14 '16 at 13:04
  • So based on the Uniquevalues function that you linked in the answer, it appears to be a CSE formula based on the { } around the function. This bring up the question, Can CSE functions be used defined names? I was unaware of point 1) in your answer. That is really good to know. – Forward Ed Jun 14 '16 at 13:13
  • 1
    Regarding point 1). My Excel definitely does not do that. It adds all values, whether they are unique or not. – Gravitate Jun 05 '19 at 10:36