# How Do I Get The Unique Values From A Range?

Jan 11, 2006

I'm trying to build a results sheet for some work that we do.

1 column has the month in which the work was done. ie.

01/01/04
01/01/04
01/02/04
01/02/04
01/04/04
01/09/04
01/09/04
01/09/04

etc... In the example above, as you can see, some months have multiple jobs
done, others only 1. Also there are some months when no work was done at all.
The dates are always the 1st of the month, so no problems in looking at
specific dates, just the month values.

So if I wished to show the last 4 month's when work was done, what function
would I need to use to get Excel to produce a list like this...

01/01/04
01/02/04
01/04/04
01/09/04

## Unique Values From Range Into Array

Jan 12, 2012

I have a spreadsheet that within Range(E2:H800) there are a number of different text/string values and blanks.

Using a Macro, how can I quickly fill a variable with each of the unique (non-blank) variables?

## Identify Unique Values In Range

Aug 27, 2012

What kind of formula I need to identify if in a given range there are only unique values?

The range (containing 12 cells) may contain only two text values: "Actual" or "Forecast".

If all values are equal to "Actual" I would like the formula to return "A".

If all values are equal to "Forecast" I would like the formula to return "F".

If there are both "Actual" and "Forecast" values found in the range I would like the formula to return "A/F".

## Unique Values From Selected Range

Jan 15, 2008

I use the following code to extract a unique list of values and paste the list to a specified range:

Public Sub extract_unique()
Dim a As Range, v As Range, w(), i As Long, r As Range
Set a = Selection
Redim w(1 To a.Count, 1 To 1)
With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
For Each v In a
If Not IsEmpty(v) And Not .exists(v.Value) Then
i = i + 1: w(i, 1) = v.PrefixCharacter & v.Value
The code gets interrupted at this line:

If Not r Is Nothing Then

with the following error: "Code execution has been interrupted."

When I click debug, the line above is highlighted. If I click the sideways triangle to resume code execution it finishes normally. What can I do to stop my code from being interrupted?

## Extract Unique Values From Range

Jun 16, 2008

I have data of different accident year. Now I need to identify different Accident Year and use them in Macro to do certain things. For example in the file attached I am using them in Msgbox. Now to acheive that I first extracted unique values using advanced filter on Column C and later use the different value to POP up the Msgbox. (This is achieved). Now I want to skip this part of doing Advance filter to extract unique value. Is there a way I can know unique value of data through the code itself and use them in program. here is what I am doing currently: I have delted the most of the data cause I am concerned with coulumn containing acc_year only.

Sub test()
), Unique:=True
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("C3").Select
Do While ActiveCell.Value <> ""
MsgBox ("The Year is " & ActiveCell.Value)
a = ActiveCell.Row
Range("C" & a + 1).Select
Loop
End Sub

## List Unique Values From A Named Range

Dec 4, 2007

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.

## Return Unique Values From Columns/Range

Oct 24, 2006

I need to know how to read all the criteria1 brought by the autofilter (in the dropdown box) from one column one by one and then print the table. Is that possible? I've read that it's hard but never read how to do that.

## Populate Userform Combobox With Unique Values From A Range

Jul 26, 2012

I found this thread which deal with populating unique values in my listbox.

What these line of code mean (the ones in red).

Code:
Private Sub UserForm_Initialize()

Dim v, e
With Sheets("maintenance").Range("c2:c500")
v = .Value
End With
With CreateObject("scripting.dictionary")

[Code] ......

Note : I simple tried to check the value of v by giving msgbox (v), just below the line v = .Value.

But I got run-time error 13 : Type mismatch error.

## Return Cell Address Of Value In A Range Contains Unique Values?

Dec 12, 2012

Here is a sample of database,

A
1
Jan-12

2
Feb-12

3
Mar-12

4
Apr-12

5
May-12

6
Jun-12

7
Jul-12

8
Aug-12

9
Sep-12

10
Oct-12

11
Nov-12

12
Dec-12

If i lookup a value in the range A1:A12, say Sep-12, i need to get the cell address instead of the value of the row. but i know how to get cell address using CELL function. but i need to get cell address when i lookup the value.because lookup value will be dynamic.

## Counting Unique Values Within Date Range Formula Error?

Aug 21, 2013

I have an issues with a formulae I have written to measure two factors;

A; How many different countries appear within a certain date range. B; What countries are they.

I tried a unique list, but could not get it to work within a range? I dont particuarly care how the data is pulled through as long as I do it. At the moment I have become stuck in 'Unique Ranges' and have n given up?

The formulae issue is in the tab "Problem_Formulae", the dat in the sheet 1, and the date ranges in sheet 2. Also, just to make life a bit more challenging pivot tables are a no go .

## Counting Unique Text And Numeric Values With Date Range

Oct 3, 2011

I am finding many posting on this topic with unique numeric values and have not come across one in regards to a text value. The essence of the formula is looking through a list for unique email addresses and now I need to up it to a date range and eventually a store # range

I am using the following array formula to establish an overall count:
=SUM(IF(FREQUENCY(IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""), IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""))>0,1))
Ctrl+Shift+Enter

The date column is E:E.
The store number column is G:G

## List And Sort Unique Values In Multi-column Range

Jan 26, 2012

In A1 of Sheet1!, I need a formula that lists and sorts all the 'unique' values from range

ECWP!\$O\$6:\$Q\$1505

## Extract Unique Values From Column B Between Date Range Textbox1 And Textbox2

Jul 3, 2007

Sheet1 has following two columns

----A----------B
01-07-2007---10
01-07-2007---20
02-07-2007---30
02-07-2007---40
02-07-2007---40
03-07-2007---60
03-07-2007---70
03-07-2007---70
03-07-2007---90
04-07-2007---100
04-07-2007---110

Sheet2 has following three cotrols

Textbox1=02-07-2007
Textbox2=03-07-2007
combobox=?

I want to Extract Unique values from column B between date range textbox1 and textbox2 and finally how to add these unique records in combox1

## Count Unique Values / Text Within Range Of Cells That Contain Duplicates / Blanks And Errors

Jun 25, 2014

How I can create a simple formula to count unique values/text within a range of cells that contain duplicates, blanks and errors?

For e.g., in Column A (row 1 - 10):
Proj-001
Proj-001
Proj-002

Proj-004
#N/A
#N/A

Proj-007
Proj-002

## Count All Unique Values (text) + Definition Of UNIQUE :D

Aug 24, 2009

If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?

## Paste Unique Values (paste In Active Cell Copied Range)

Jun 4, 2009

i try to paste in active cell copied range.

I mean that i do follow:
- i select range of cell - mostly range of column f.e. A2:A500
- i click/select on any free cell (f.e. B1)
- then i run macro

i expected it paste unique values (text or number)

this dont work

i dont know how defined the range

## Macro To Give Count Of Unique Values After Comparing Comma Separated Values?

Mar 2, 2014

I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".

The sample sheet has been attached for reference.

## Excel 2010 :: Produce Unique List Of Key Values With Second Values Concatenated Together

Sep 15, 2013

I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)

The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.

Sample data Required Output
A | B Z
1| 10 | a 10,a,b,c
2| 10 | b 11,a
3| 10 | c 12,a,b
4| 11 | a
5| 12 | a
6| 12 | b

My system is Windows 8 Excel 2010.

## Formula For Obtaining List Of Unique Values But Not Including Values Which Contain IC

Aug 14, 2014

1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).

I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)

2. To include all unique string values except those starting with the letters "IC"

3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.

Here is the formula I currently use which serves #1 (above):

[Code] .....

Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.

Example of desired results:

Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |

## Count Unique Values In Column Based On Values In Other Columns

Mar 14, 2014

I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.

My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!\$I\$3:\$I\$66<\$E\$1,IF(Data!\$A\$3:\$A\$66=\$H\$1,
IF(Data!\$C\$3:\$C\$66=A4,ROW(Data!\$I\$3:\$I\$66)))),ROW(Data!\$I\$3:\$I\$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!\$I\$3:\$I\$66<\$E\$1,IF(Data!\$A\$3:\$A\$66=\$I\$1,
IF(Data!\$C\$3:\$C\$66=A4,ROW(Data!\$I\$3:\$I\$66)))),ROW(Data!\$I\$3:\$I\$66)),1))}

This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).

The results in the pink highlighted cells (Summary column D) should be:

Names starting with A - 3
All others - 2

I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.

## Look Across Values In 12 Columns, Return The Number Of Unique Values

Apr 17, 2009

I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.

What would be the best way to do so?

Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.

I have attached an example file that represents how the data is stored and the output I would like (Unique Count).

## Unique Values From List Of Many Duplicate Values?

Jan 31, 2014

how to get this with formula:

Column A - - Column B
a - - - - - - - a
a - - - - - - - b
b - - - - - - - c
c
c

## Count Unique Values, For Specific Values

Sep 13, 2006

I have an array that I'm using - and it works perfectly: =SUM(If((FREQUENCY(If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""),If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""))>0),1))

Now, what I would like to do, is see how many of these unique numbers relate to another number in column D
See attachment.

## Generate Specific Range Of Unique Random From Main Range

Feb 9, 2014

How to create a sheet to generate Random numbers from 2 specific list of a main list range of 36 Numbers. (List A = Specific 15 numbers, List B = The remaining Numbers from these 36)

Example :

-The Main List Range is from Number 01 to 36
-The Specific random list will be 15 Numbers of these 36

List A-. The Specific 15 Numbers are: 01,02,03,10,11,12,13,20,21,22,23,30,31,32,33
List B-. Remaining Numbers : 04,05,06,07,08,09,14,15,16,17,18,19,24,25,26,27,28,29,34,35,36

So

I need to generate ONE Unique Random Numbers of List A ( 01,02,03,10,11,12,13,20,21,22,23,30,31,32,33)
In Cell A1
In Cell A2
In Cell A3

And from List B, (04,05,06,07,08,09,14,15,16,17,18,19,24,25,26,27,28,29,34,35,36)

I need to do the same thing In
Cell A4
Cell A5
Cell A6

It's possible to have a 6 cells with random but Unique Digits? (not repeated numbers between the 6 cell ??)

## Sum Only Unique Values

Mar 4, 2011

I have number values in G2:G50 with some blanks included - what I'm looking for is a formula that will add all the values together but will only add values that exist more than once one time.

4.24
2832.74
2832.74
8643.73
8643.73

The only things that should get added at 4.24, 2832.74 and 8643.73

## Sum ONLY Unique Values?

Dec 4, 2012

I have an ever growing client list that currently has just over 2500 names which I need to track the total number of clients, which is the easy part, the total number of files, and the most important part is how many unique names are on the list. Some clients will appear a number of times on different dates, and I need to be able to track how many clients appear only once in the list.

10/6

Lincoln , Abraham
10/6

Clinton, Bill
10/6

[code].....

In the above example, the answer I am looking for is 2, because Clinton, Bill and Carter, Jimmy both appear only once. My total clients would be 4, though the number number of "files" worked on would be 6.

## To Sum Unique Values

Feb 6, 2007

I have some data and one of the columns is overtime. The data is set up where each employee has a record for each function they perform. So I may have an employee that opens and funds loans. They will have 2 records and one of the fields in the record is overtime. My issue is, in this example, that the OT will be doubled if I just sum the column. I need to sum the unique values only.

## Unique Values Only

Dec 13, 2006

I have a spreadsheet into which users enter account details. I have another spreadsheet which is updated periodically which contains a valid list of accounts.
Currently I am using the following code which works perfectly to see if the account is valid or not:

If WorksheetFunction. CountIf(coaRange, checkAccount) = 0 Then

where coaRange is the search range on the valid accounts spreadsheet and checkAccount is the string of the current account to check. If I get 0 I know the account isn't valid and I highlight the incorrect cell. All this is in a loop checking each account. What I would now like to improve is the speed at which the checks are peformed. Sometimes there may be 700 rows with account details in but there might only be 10 different accounts.

## Comma Separated Cell Values To Column Of Unique Cell Values

Mar 31, 2014

I have one column of data. there are currently ~10k rows, but this will increase over time. each row has either a single value (example: pepsi) or a comma separated value (google,samsung) with up to 6 commas. instead of the rows looking like this:

pepsi
coca-cola

I want them to look like this in one column (preferably via a formula):

pepsi
samsung
coca-cola

I would like the above output to be a unique, alphabetized list.

## How To Count Only Unique Values

Jul 24, 2014

I'm working with a data set (attached) that tracks financials at both the job and item level (in my customer's data - a unique job can be made up of a single or multiple items).

I've successfully built a formula (in column C of the attached) that counts the total number of items and their associated \$ values that were invoiced in a given month based on my customer's requested item-type categorizations (i.e. "Exact Match", "Similar", "Inferred", "Cost-Plus").

The problem I'm running into is building a formula that counts the first unique instance of the Job ID associated with those items. For example - in cell C33 on the attached - The total number of items invoiced in June that were "Exact Match" was 10. These items were spread across 9 unique jobs - and that's where I'm struggling - to build a formula that only counts the first unique instance of the corresponding Job ID in Column C.

The formula I'm having trouble with is located in Cell D12 on the "Dashboard" tab of the attached.

The formula as I have it is returning a result of 8 - because it's summing the "1"'s in Column I on my "Invoice Data" tab - however it should read 9 (Filter Column Q on the "Invoice Data" tab to "June", Filter Column AQ on the "Invoice Data" tab to "Exact Match", and then sum the unique job IDs in Column C).

Excel_Forum_Example_Dataset_7.24.14.xlsx‎