Generate unique drop down list based on Row entries

[dancran]

Hey everyone,

I have close to a 100X100 table (very rough estimation) and viewing different parts of the spreadsheet can require a lot of scrolling.

I want to create a drop down list based on the entries of Row 6 (starting from Column G). The problem is that when I do this, the drop down lists all the columns that have blanks and duplicate entries. I want a way to remove the duplicates from the drop down list. From what I have gathered it is not possible to simply just say "remove duplicates" to a drop down list, so I have to first create a second list that has everything without duplicates.

My thought is to use the Advanced Filter (Using Excel 2007) and paste a second, unique list in Sheet2. Unfortunately, I can't figure out what I need to enter into the different text fields in order to do this.

Once I have a unique list, I plan on making the drop down based off of that list. I want the unique list to include 1 blank cell as well to choose from (weird I know, but necessary because if nothing is entered, I have a Macro that will display everything but if there is an entry it will only display the data that is equal to the row 6 entry) Essentially, I want this Macro to have some kind of data security to it by only allowing the user to choose from the available entries in Row 6 after Column G.

Another capability I was hoping to have was that the drop down by dynamic. I.E. - if I add a new entry to Row 6 after Column G, it will add it to the unique list and thus the drop down without having to re-do everything.

Let me know if I need to reword / clarify anything to make more sense. I appreciate any help the community could come with.

Dan

(the second list could be listed vertically instead of horizontally if that is any easier)
Sample:

Current:
A . . . . G _____H ____ I ____ J _____ K _____L ____ M .........
.
.
.
5
6 ...... One ___(Blank) _Two _(Blank) __Three ___One __Two .....
7

New List:
___A _____B ______C ______D ...........
1 One ___ Two ____Three __Blank
2

 

[GlennUK]

Is this any use to you?

Excel Workbook
	E	F	G	H	I	J	K	L	M
6			one	two		three	four		five
7
8
9	New list		one	two	three	four	five
Sheet

 

[dancran]

Yes, this looks like it has the potential to do what I want, although I am confused as to how I would implement it.

1. How would I get the new list to be written out in Sheet 2?

2. How would I put text in with the formula for the cells?

Sorry if they are basic/easy questions, but I am still trying to get used to everything Excel can do.

 

[dancran]

Upon further experimenting I got your formula to work.

However, it is not quite what I am looking for.

- If I copy the formula over for all columns, it will correctly remove the blanks but it will not remove duplicates of text.
- I would like this functionality to be dynamic. I think it will need to be a VBA program that runs when Row 6 changes or the worksheet changes. There will be many future revisions to this spreadsheet, so making it work without having to update/copy formulas is a very necessary aspect.

I need the VBA program or macro to populate a drop down with only the unique (no duplicates) values throughout Row 6. Once a value is selected the spreadsheet is already programmed to only display the columns that are equal to the value entered.

Any other ideas / help on how to accomplish this?

Dan

 

[GlennUK]

If you are only going to use the list to generate a Data Validation in-cell drop-down you could put the list directly there:

Sub Macro2()
'
' Macro2 Macro
'
    strlist = ""
    For Each c In Range("B1:J1")
        If c.Value <> "" And InStr(1, strlist, c.Value) = 0 Then
            strlist = strlist & IIf(strlist <> "", ",", "") & c.Value
        End If
    Next
    With Range("B2").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=strlist
    End With
End Sub

... if you are happy with that approach you could put the logic in a Worksheet_Change event.

 

[dancran]

For efficiency, is there a way to only enter loop if a value in row 6 in changed? I would prefer not looping through every column every time the worksheet changes.

I am not very familiar with VBA so I am not sure how to add a simple logic case like that.

I did run the code you provided and it worked perfectly except with one nit picky issue. Is there a way to include 1 blank entry in the drop down selection? Preferably the first slot. Reason being, when nothing is entered it will unhide every column and show the whole spreadsheet. Not familiar enough with VBA to be able to see what I can edit in order to make it count a blank entry the same as a text entry.

Thanks for your help Glenn, this forum has been an excellent resource over the last couple weeks.

 

[GlennUK]

Right-click the sheet tab, and choose View Code, and paste this there:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 6 Then
        strlist = " "
        For Each c In Range("B6:J6")
            If c.Value <> "" And InStr(1, strlist, c.Value) = 0 Then
                strlist = strlist & "," & c.Value
            End If
        Next
        With Range("B7").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=strlist
        End With
    End If
End Sub

... it will only run when cells in row 6 are changed.

As for having a blank entry in the drop-down ... why? The user simply has to press the Delete key to blank the cell.

 

[dancran]

Well, as I mentioned I already have a macro that will show all columns if a blank is entered. Thus I would prefer having it as a selection for the user of the spreadsheet rather than them having to know that they have to hit delete to create the entry. This spreadsheet will be passed around and keeping the ease of use high is, as always, something to strive for.

If it is not an easy fix to make the validation list contain one blank then that is fine, I will try to figure out a workaround.

 

[GlennUK]

Use a range for your validation list in that case, with the first cell as blank. You could fill the range from the loop I've given you.

 

[dancran]

One last question. How can I get this code to run for rows rather than columns now?

I tried this, but the data validation it created was just a blank drop down with no values. Does VBA not loop rows like it does columns?

If Target.Column = A Then
        strlist2 = " "
        For Each q In Range("A1:A10")
            If q.Value <> "" And InStr(1, strlist2, q.Value) = 0 Then
                strlist2 = strlist2 & "," & q.Value
            End If
        Next
        With Range("B1").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=strlist2
        End With
    End If