Duplicate values in Dropdown boxes

[Padraic Fahy]

Hi All,

I have recently set up an excel template for employees to enter their expenses in a charity I help out.

In total there are 4 categories which need to be filled out in the following order:

• Expense Category
• Department
• Donor
• Project
• Sub-project
• Expense Account

(Not sure how to add a screenshot of template, apologies)

Each selection dictates what comes up in the next list/dropdown box.

I am using the following formula to generate the information in the dropdown boxes: (this example is taken from "Expense Account")

=OFFSET('pivot list data'!$O$1,MATCH(P16,'pivot list data'!$O:$O,0)-1,1,COUNTIF('pivot list data'!$O:$O,P16),1)

- For the formula above, the match is using a concatenation of expense category&Department&donor&project&sub-project

The problem I have is that I am using concatenations all the time to avoid duplicate values being shown in the dropdown box and then pivot tables for each dropdown box (I am creating 6 pivot tables from original data to address the issue) , which is then making the excel file very large as the original data is just being constantly repeated, and does not help as it is a file which will need to be updated regularly.

I hope this makes sense, any advice on what I am missing in my formula would be greatly appreciated, or if it is VBA or something else I need to solve this problem

Thanks in advance!!

 

[AFPathfinder]

So, you are using pivot tables to get your unique value lists from a single table?

If I understand correctly, I think you can avoid doing so much concatenation by using the advanced filter. However, I don't have much experience using the advanced filter outside VBA, but I have heard advanced filter works much better in VBA. Here is a typical use of advanced filter in VBA:

    Dim finalRow As Integer
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row

    Range("B2:B" & finalRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("F1"), Unique:=True

If you don't need to worry about updating those lists, you could just copy the column out of the table, paste the column in a different location, and perform the "Remove Duplicates" function on that column. Then, when you reference the column in your drop-down lists, they should all be unique values.

 

[Padraic Fahy]

Hi AFPathfinder

Thanks for the response.

Yes at the moment I'm pulling pivot tables from the one data table where I am doing all the concatenations, and then each pivot table addresses another part of the expenses etc.

It is a longwinded way and I think you are definitely right creating lists via VBA is the solution.
I will need to set up the data so that it can be easily updated, as when they create new accounts in the system (which is quite often) they will need to be able to see the new accounts in the expense template.

At the moment I am starting to learn how to write VBA, macros etc. I think then the plan would be to set up a macro/VBA which can update the data, and avoid the issues with concatenations etc.

I like the idea of using the advanced filter function in VBA, can the advanced filter then be used along with the offset formula to generate the next dropdown list or would you look at it a different way?

 

[AFPathfinder]

Once the advanced filter runs, then you would have the macro find what the last row is on that new list and create the drop down using the range of the new list. Here is a combined look at it all from the original column in column A, filtered list in column B (with header), and the data validation (drop-down list) in cell C1.

Sub ListValidationCreate()
    Dim finalRow As Integer
    
    ' Find last used row in column A.
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row    ' Get unique list of values from column A.
    Range("B2:B" & finalRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("F1"), Unique:=True
    
    ' Find last used row in column B.
    finalRow = Cells(Rows.Count, 2).End(xlUp).Row
    
    ' Add Validation drop-down list in cell C1 with the data in column B.
    With Range("C1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$B$2:$B$" & finalRow
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Invalid Entry"
        .InputMessage = ""
        .ErrorMessage = "Please select a catagory from the drop-down menu."
        .ShowInput = True
        .ShowError = True
    End With
End Sub

 

[Padraic Fahy]

okay cool this certainly looks like what I want. I have been able to set up a list using VBA on your formula above and it's working well, now just to add the other parts!

Just so that I undertsand what you are then doing, with the way you have set up the VBA, it is finding the unique list in column C as it will have first carried out the filter on column b, and then the next filter in column c is a further filter from column b, so it is effectively reducing down all the time the list of possibilities from these? (You are declaring the results from column A and B as the variable via Dim)

I'm going to look at reworking the data in the template now! Just wondering also what part of visual basic do I write in this code so that it automatically works in the spreadsheet? I have done some research on "public function" etc. is this what I need or is there some other type of code to write in? or is it a case that the variables are the trigger?

Apologies if my questions are a bit frustrating!

 

[AFPathfinder]

Not at all! I don't find many folks that are interested in learning VBA, but it can be a very powerful skill for Excel users. Sorry if this turns into a wall of text. I get a little excited about teaching VBA.

Knowing where to put the macro and how to execute it might be a bit tricky. First, we'll need to think about when this macro will need to be ran. Then, we'll need to look at what workbooks need to be open when it is being run. The ideal workbook to keep it in is a workbook that is used every time, like a revolving logbook. If none of those workbooks are going to be used every time, you'll want to stick it in a PERSONAL.xlsb file. If you don't already have one, let me know and I'll walk you through it.

Next, try to think about how you want the macro to be activated. The most popular option is to attach to a button. If you want the macro to run automatically, we'll need to identify the event that queues the action to take place. If you want to go the button route, the macro should be placed in a Module inside the chosen workbook. Let me know how you want it to work and I'll cover how to get it setup.

The code package itself is considered a subroutine (hence the "Sub" phrase at the beginning). It's usually the starting place for macro learning. Functions are just like subroutines, but they can provide a return value to other subroutines and functions.

The "Dim" statements are used to instruct Excel that you are creating a variable and the following "As Integer" tells Excel what the variable will be holding. The variable name "finalRow" can be named nearly anything, but I try to stick to the convention of naming them so they are as self-explanatory as possible. Variables are super useful in allowing code to be dynamic.

I use the finalRow variable in almost all macros to identify the row number (Integer) of the last row that contains a value in a particular column. In my example, it is written to start at the bottom and look up in case there are blank cells in-between the first and last values in that row. Since Excel 2003 and earlier version workbooks (.xls) had much fewer rows that newer version (.xlsx), I use the "Rows.Count" syntax to account for either file type. The "End(xlUp)" is a keyboard shortcut of Ctrl+↑ which stops at the first cell from the bottom that doesn't have an empty value (last used cell). The ".Row" just returns the row number to the variable.

The AdvancedFilter public function actually uses an array as a variable (I believe, anyway). In my example, it loads all the values from column A into it's array variable and removes out the duplicates before pasting those remaining values into column B. So it's already cut down to the unique values when it's put in column B.