removing blanks and duplicates in drop down list

[zebrasam]

I have column A and B.

Column A has the name of a different animal in each cell.

In column B I need a drop down list in each cell. I would like to type the name of the species in the cells in column B corresponding to the name of the animal on the same row in column A.
This drop down list will grow in size as I work my way down the spreadsheet continually adding new species in the cells of column B. This needs to be done as I work my way down the spreadsheet and cannot be predetermined beforehand. (note: This is a hypothetical example to which my situation corresponds exactly)

I managed to get this working but the problem is that as the drop down list grows blanks and duplicates are added to the drop down list.

Sam

 

[cyrilbrd]

Hi Sam,
Considering that your data validation as a range (where the taxa is located) is located per example in ColumnD.
You may use this vba to remove all blanks:

Private Sub Worksheet_Activate()
    Dim r As Range, txt As String
    For Each r In Range("d1", Range("d" & Rows.Count).End(xlUp))
        If Not IsEmpty(r) Then txt = txt & "," & r.Value
    Next
    With Range("b2")
        .Value = Empty
        With .Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=Mid$(txt, 2)
        End With
        .Select
    End With
End Sub

this would be valid for the data validation cell located in B2...
would that work or did I miss something?

 

[T. Valko]

I have column A and B.

Column A has the name of a different animal in each cell.

In column B I need a drop down list in each cell. I would like to type the name of the species in the cells in column B corresponding to the name of the animal on the same row in column A.
This drop down list will grow in size as I work my way down the spreadsheet continually adding new species in the cells of column B. This needs to be done as I work my way down the spreadsheet and cannot be predetermined beforehand. (note: This is a hypothetical example to which my situation corresponds exactly)

I managed to get this working but the problem is that as the drop down list grows blanks and duplicates are added to the drop down list.

Sam

Are you saying that you already have a list that contains empty cells and duplicates that is the source for your drop down lists?

If so, you'd need to create another list that contains just the unique items from the other list. Are the empty cells scattered about within the list or are they all at the bottom?

Can you post the current list and show us what you want it to look like?

 

[zebrasam]

Each cell in column B has a drop down menu. This drop down menu shows duplicates and blanks.
I know very little about excel, but the A column will have hundreds of animals and I need to tag each animal with its family without using a predetermined list. Imagine it was a test where the student had to correctly input the family of the animal but did not want to have duplicates or blanks in the drop down list. Thanks.


Sheet1

*	A	B
coyote	Canidae
Javan rhinoceros	Rhinocerotidae
jackals	Canidae
foxes	Canidae
Cougar	Felinae
kangaroo	*
Cheetah	Felinae
tiger seal	*
Sumatran rhinoceros	Rhinocerotidae
wolves	Canidae

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:153.6px;"><col style="width:106.4px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

</tbody>

Data Validation in Spreadsheet

Cell Allow Datas Input 1 Input 2 B1 List * =$B$1:$B$10 * B2 List * =$B$1:$B$10 * B3 List * =$B$1:$B$10 * B4 List * =$B$1:$B$10 * B5 List * =$B$1:$B$10 * B6 List * =$B$1:$B$10 * B7 List * =$B$1:$B$10 * B8 List * =$B$1:$B$10 * B9 List * =$B$1:$B$10 * B10 List * =$B$1:$B$10 * <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[T. Valko]

Each cell in column B has a drop down menu. This drop down menu shows duplicates and blanks.
I know very little about excel, but the A column will have hundreds of animals and I need to tag each animal with its family without using a predetermined list. Imagine it was a test where the student had to correctly input the family of the animal but did not want to have duplicates or blanks in the drop down list. Thanks.


Sheet1

*	A	B
coyote	Canidae
Javan rhinoceros	Rhinocerotidae
jackals	Canidae
foxes	Canidae
Cougar	Felinae
kangaroo	*
Cheetah	Felinae
tiger seal	*
Sumatran rhinoceros	Rhinocerotidae
wolves	Canidae

<TBODY>
[TD="bgcolor: #cacaca"]1
[/TD]

[TD="bgcolor: #cacaca"]2
[/TD]

[TD="bgcolor: #cacaca"]3
[/TD]

[TD="bgcolor: #cacaca"]4
[/TD]

[TD="bgcolor: #cacaca"]5
[/TD]

[TD="bgcolor: #cacaca"]6
[/TD]

[TD="bgcolor: #cacaca"]7
[/TD]

[TD="bgcolor: #cacaca"]8
[/TD]

[TD="bgcolor: #cacaca"]9
[/TD]

[TD="bgcolor: #cacaca"]10
[/TD]

</TBODY>

Data Validation in Spreadsheet

Cell Allow Datas Input 1 Input 2 B1 List * =$B$1:$B$10 * B2 List * =$B$1:$B$10 * B3 List * =$B$1:$B$10 * B4 List * =$B$1:$B$10 * B5 List * =$B$1:$B$10 * B6 List * =$B$1:$B$10 * B7 List * =$B$1:$B$10 * B8 List * =$B$1:$B$10 * B9 List * =$B$1:$B$10 * B10 List * =$B$1:$B$10 * <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

Ok, I'm not following you on this.

You say cells in column B contain drop down lists and you want to remove the empty cells and duplicates from the drop down list. So what/where is the source for the drop down list? Or, do you want to extract the unique entries from column B to make another list?

 

[zebrasam]

Hi Biff,
The source is the B column itself. I'm not sure how to do it otherwise. You see I am using this so I can auto-complete previous entries I put in column B. So for example. When I start working on row 1. I type in "Canidae". This is at the same time added to the empty drop down list so I now have one entry in the drop down list. I then proceed to the second row and type in "Rhinocerotidae". This will now be added to the drop down list. Now here is what I want: When I get to the third row I would normally need to type in "Canidae" again but instead I can just pick it from the drop down list which has 2 entries so far. So I don't need to retype it. As I work my way down from row to row the drop down list will grow and grow. The problem is all the duplicates and blanks that are appearing in the drop down list.

 

[zebrasam]

Bill,

I also posted this here before I received your latest reply. I not sure if that is proper netiquette especially as you are helping me?

removing blanks and duplicates in drop down list (with attached workbook)

 

[T. Valko]

Hi Biff,
The source is the B column itself. I'm not sure how to do it otherwise. You see I am using this so I can auto-complete previous entries I put in column B. So for example. When I start working on row 1. I type in "Canidae". This is at the same time added to the empty drop down list so I now have one entry in the drop down list. I then proceed to the second row and type in "Rhinocerotidae". This will now be added to the drop down list. Now here is what I want: When I get to the third row I would normally need to type in "Canidae" again but instead I can just pick it from the drop down list which has 2 entries so far. So I don't need to retype it. As I work my way down from row to row the drop down list will grow and grow. The problem is all the duplicates and blanks that are appearing in the drop down list.

Ok, I understand now. You'll have to extract the unique entries from column B to make another list and use this other list as the source for the drop down.

What version of Excel are you using?

 

[zebrasam]

Hi this has been solved on the other forum albeit using some sort of code. I'm not sure if there is a disadvantage to using code? I use excel 2010. If you have a solution not using code that could be useful for me but I don't want to take up your time either. Let me know.

 

[T. Valko]

Hi this has been solved on the other forum albeit using some sort of code. I'm not sure if there is a disadvantage to using code? I use excel 2010. If you have a solution not using code that could be useful for me but I don't want to take up your time either. Let me know.

I can show you a formula method but it's a bit more involved getting it setup.

Interested?