Create a list containing unique items

When creating for instance a sales and ordering app, you often need to handle a a product list. You can easily create a searchable dropdown list for your Molnify app by defining the cells that are going to be the list as a named range. Then, you color a cell containing that named range as a data validation list green. Done. But, sometimes there might be duplicates in that list, which you might not want.

Here is an easy two step way to fix this in Excel.

Step 1;

Start by entering the following in a cell near your column containing product names (or article numbers, or something else you want as uniques in your list):

=IF(C3="";"";IF(COUNTIF($C$2:C2;C3)>0;"";MAX($B$2:B2)+1))

It should look something like this:

This formula will number all uniques ascending. Copy this formula all the way down to the end of your list.

Step 2;

Now, lets create the unique list. Start by typing this in a cell where you want your list to start:

=VLOOKUP(ROWS($Q$3:Q3);$B$3:$C$1002;2;0)

It should look something like this:



This second steps will have VLOOKUP searching for numbers correspoding to the numbers of rows in the list. When there are no more uniques, you will get a error message in your list and then you know you're done (you can of course make this look prettier by wrapping the VLOOKUP in an IFERROR(VLOOKUP-formula;"")-wrapper).

Hope this will be helpful!

// Mattias