Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Create a List or Range of Unique Values in Excel

Hi everyone. I have a very ambitious project that I am working on. There are many stages to it and it may well melt my CPU! However, I must first make a few tests and for these, some advice would be of great value, if you can help.

First of all, I need to be able to index all of the words in a large selection of txt files. Each unique word needs to be placed in a cell in a big long column. I expect there will be tens of thousands of words in all of the text files so I mean a REALLY long column. I also need to count the number of incidences of each word over all the txt files and have this "count" as a separate column.

The next step will be to remove words that are superfluous like "the, it, and ... etc" and words that are so rare that they do not provide any useful information. This, hopefully, will leave me with a list of common keywords from the txt files.

This will then need to be put into alphabetical order!

Presently, the txt files are in pdf form so I have a little bit of work ahead of me before I can begin. Any advice on how to index words in the way described above would be most valuable.

I am realising more and more, that a good bit of skill with macro-programming will go a long way if I am using Excel for stuff like this. Can anyone suggest a good book that might help?

Many thanks.



I need to make a list of unique combinations from a series of variables

I have 5 variables, A, B, C, D, E

These can hold 5 unique values each (A1, A2, A3, A4, A5, B1, B2, B3 etc etc)

from these variables I want to make a list of all unique cominations that these variables and values can generate, like this


How do I do this?

Thank you in advane

Hi guys,
I need some formula that would return a list of unique values from one column to a column on another sheet. Pls advice how that could be done technically?
It should be done with a formula, so if there is a new value it would be translated to the list in another sheet automatically.

Thanks a lot!


I would like to create a unique list of names from a report. I can do it using the Advanced Filter option but I cannot get it to automatically update if I add a new name.

Is there a way in Excel so that the unique listed is automatically updated?




Please can you help me assign a sequential number to each new value in an unsorted list, and where duplicates occur, assign the same number as the duplicate E.g.

A 1
B 2
C 3
D 4
A 1
C 3
E 5

Thank you

Hi All. Been looking around but can't find a nice simple solution

I have dates in cells A1 : A10

I need to build a formula to (drag down) and produce a list of unique dates, sorted

Thinking this will be an INDEX / MATCH using SMALL and the ROW() as the k value, but can't get anything to work?



I am OK at Excel but I have never really attempted at writing VB code. My expertise is really PHP and a bit of Java so bear with me... anyway to the question:

I have a spreadsheet of thousands of rows of websites with their IPs. What I need to do is calculate the number of unique SubNets within that column (i.e. 154.124.60 is one subnet regardless of there being multiple 154.124.60.x).

Essentially the only way I can figure out to do it is to split each cell to remove the last part of the ip then to manually remove duplicates using the function in excel. This will give me how many unique sub nets there are.

Then I want to find out how many of each appear so I would need to do a count function to see how many times each one of these appear in the column of IPs.

The problem with this is that I will be doing this every couple of weeks and so it will take a hell of a lot of time to do this manually.

I was wondering if there is an automated way?

Has anyone tried something similar before who wouldn't mind enlightening me, or alternatively point me in the direction of a few tutorials that would help me achieve this through VB/Macros?

I know from other forums that people don't like OPs not trying to do this on their own first but I have no knowledge of VB and I do not really have the time to study it (its a work project) so I do apologise not having a crack at this first.

Any help will be greatly appreciated.

In a workbook I have about a hundred worksheets. I want to extract from each sheet the data in Column C to a Master sheet. I need to extract only the unique items from the generated list. Is this possible?

Any help would be appreciated.

I need to set data validation from a list and I need the list of values to dynamically display the unique values from a list in a range. So if the list had:

I need the dropdown to propose:

I know how to do a dynamic range name, it is the unique value bit that I cannot do. I would prefer a single formula that can be entered in the data validation dialog as opposed to creating multiple ranges and then pointing the list to the last one.


Is it possible to count the number of unique values in a list that is filtered without using a helpcolumn? I guess I need to use the subtotals in some way.

Hi all,
I have 2 comboboxes on a User form and I want the second one to be a dynamic and unique list based on the value selected in the first combobox. Both 'Column A' and 'Column B' are dynamnic named ranges as data is always added to these columns.

Column A Column B
a ______ x
a ______ x
a _______y
a _______y
b _______z
b _______z
b _______z
c _______w
c _______v

ComboBox 1 already contains a dropdown of the unique values of columnA (in this case a,b,c). But I would like VBA to populate the second combobox based on the first combobox. For example if the user selects 'a' in combobox 1 then they should get 'x' and 'y' in combobox 2.

Does anyone know any code to do this?

Hello All,

I tried to create a 'one-click' code generator in VBA, and I kind of succeeded in this. The code it generates lookes like this: TEST1-12FEBTEST411LO1406

The problem though lies in de last four digits, its a random generated number thats not really random. When a bunch of codes is generated the last four digits turn up more than once, I attached a the file as example.

The VBA code to this contains a loop which I guess is incorrect, but Iam not able to fix it.

Maybe someone could help me out.

Thanks in advance.


In MS Excel 2010 How do I display only the unique values from a column in a drop-down list box in another cell and have it update the drop down list whenever the source list changes?
For example, if column A contains "chocolate, vanilla, chocolate, strawberry, vanilla, chocolate, strawberry, pecan", I want to have a drop down box in another cell which contains only the values "chocolate, vanilla, strawberry, pecan". I also need this to update automatically, so if I add a new entry to the source list (raspberry) then as soon as it (or any other unique value) is added it would appear on the drop down list.
I created the "unique records only" list from the master list using the Filter menu "Advanced" and then "Copy to" option. I then created the "Drop Down List" using data validation restricting the values to the range with the unique values in it. But the unique values list does not update automatically unless I do the filter again, and then I have to re-assign the drop down list to read the unique list again.

I have the need to create a list of possible combinations of text. Here is what I have set up.

Column A - 2 Text Values
Column B - 6 Text Values
Column C - Yes or No
Column D - Yes, No, Default -- List of values limited to Default when Column C is yes
Column E - Yes, No, Default -- List of values limited to Default when Column C is yes
Column F - 5 Text Values -- List of values limited to Value 1 when Column C is Yes
Column G - 19 Text Values -- List of values limited when Column D is Yes AND Column E is Yes

At this point it doesn't really matter what the rules are as long as I can see how to make the rules work. Also, instead of outputting the entire value with concatenation, I'd like to just pick on the first 2 letters of that value.

The idea here is to create unique product codes for all possible combinations of product features based on rules.

I hope this makes sense, and that someone can point me in the right direction.

I've built a MASSIVE Excel macro for one of our ICP-MS instruments. The very last part of the app is a reporting function, and the last piece of THAT is to search a column that has LIMS record numbers in it and create a comma-separated list of the distinct LIMS numbers. The problem is that when I run the following line of code against the following values, it returns the following values.

Cell Value B13 205463 B14 205463 B15 205463 B16 205463 B17 205463 B18 205463 B19 206121 B20 206121 B21 206121 B22 206121 B23 206121

Cell Value AA6 205463 AA7 205463 AA8 206121
when I run the following code


Sub TryAgain() 
    Range("B13:B23").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AA6"), Unique:=True 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

I am at a LOSS! No matter what I do, the code returns two instances of whatever the first value is that it finds more than one instance of. If I switch the 206121 values with the 205463 values, and put the 206121 values at the top, the code returns 206121, 206121 and 205462. Can ANYONE tell me what I'm doing wrong? I've cleared the cells and manually entered values to make sure there is not a stray space or other character that IS making two values distinct that appear to be identical...completely baffled on this one. BTW, if I replace the first value (cell B13) with a null, the function returns nothing. If instead I replace the SECOND value with qa null, I get


(The second cell down is null)

ANY suggestions?

I am trying to find a way to create a list of unique items from a named range. I have found a few solutions using filters for ranges that are contained in the one column but not named ranges that contain multiple rows and multiple columns.

Is there any way to copy all of the unique items from the named range to another location? Lets call the named range Table1 and it spans from B2 to J100.

Thanks in advance!!

Hi all,

I'm trying to populate a listbox with all the unique items in a column. By unique I mean that if there is a duplicate that item will only appear once in the listbox. The range is quite large (10,000 rows), but there will only be about 10 different items in the range.

Just incase anyone has come across my full problem before, I'll go on to describe it (there's no point in reinventing the wheel!), but I'm not looking for someone to write all my code for me, just help with populating the listbox at the moment!

Once the listbox is populated, the user will then be able to multiselect items from it, and this will then extract only rows that contain one of the selected items from the listbox in the specified column to a new sheet