Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Get Unique Values In A List Of Duplicates

I am confident the answer is no, but is there any formula / combination of formulae that will seek out unique values in a list of duplicates (just like filtering unique records only)?

I have a list of many duplicating sales people and I want to just create a column on another sheet that automatically sorts out the unique values (then I will sum their sales numbers with the SUMIF formula).

(By the way, the reason I just don't use the filter function is because the list always changes so I would need to keep applying it.)

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Unique List With No Duplicates
I was wondering if there is a formula or a vba code to return each number once from a list.
For example I’ve got a list with app. 500 rows and maybe 80 different numbers in there, so one number could be 50 times in the list, another number maybe just once. Is there a formula, which returns me each number once?
Can’t think of any, or do I have to use a vba code be get the information?

View Replies!   View Related
Create Unique List From List With Duplicates
I have a list of data which looks like this

Column B

Jones, Bob
Jones, Bob
Jones, Bob
Smith, Mike
Smith, Mike
Smith, Mike
Calai, Dave
Calai, Dave
Calai, Dave

What I want to do is take the entire list and create a distinct list that I can use in a named range then use a validation list to have a drop down within a different tab.

View Replies!   View Related
Identify Duplicates: Get A Unique List
I have a list of objects:


I know how to get a unique list. How do I identify those that are in the list more than once and how many times it is in the list?

View Replies!   View Related
Create Unique List From Column With Duplicates
I want to end up with a list of unique items (nothing repeated) from a variable length list starting in cell B5. That unique list is then placed in cell D3.

I am using the code below (found on Ozgrid) which almost works. But it seems to be dependent on what’s in column A (which I wish to ignore) for it to work.

How can I modify the code so that the results are based on column B only?

Dim Rng As Range
Dim fRng As Range
Dim lRow As Long
Dim Dest As Range

lRow = Range("B" & Rows.Count).End(xlUp).Row
Set Rng = Range("A5:B" & lRow)
Set Dest = [D3]

With Rng
. AutoFilter Field:=1, Criteria1:="1*"
Set fRng = .Offset(1, 1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
With fRng
.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Dest, Unique:=True

End With
End With

View Replies!   View Related
Count Of Unique Values With Duplicates
Good Morning Peps (Oh thats for users in Western Europe, got to get this right!)

I have these 12 columns (Well there is 12 Columns but this forum's HTML is not showing the last two lol, would much easier if Office Web Components was able to run on Firefox): ...

View Replies!   View Related
Macro To Find And Delete All Duplicates And Keep Only Unique Values
Looking for a macro to find and delete all duplicates and keep only unique values from a column. For example column AS has

All red items need to be removed and keep only green items. Would also like the entire row the duplicates are in to be deleted.

View Replies!   View Related
Create List Of Unique Values
In column A I have values starting at row 4, ranging up to row 1004.

in coulumn Q (starting at row 3) I'd like a list of unique entries from column A

I know this line is my problem

If Range("A" & I) Range("Q4:Q30") Then
but i'm not sure how to compare against all values in a range

Sub FilterSymbol()
Dim I As Integer
Dim X As Integer
X = Range("O2")
I = 4

If Range("A" & I) Range("Q4:Q30") Then
Range("Q" & I) = Range("A" & I)
I = I + 1
Else: I = I + 1
End If
Loop Until I >= X
End Sub

View Replies!   View Related
Populate List With Unique Values
I used this forum to find code to convert a matrix to a list. But I need to repeat then change one column - "Product".

The "Prodcut" vlaue needs to repeat unitl it changes.

I have attached a file with the code in module 1, and samples of where I am, and where I need to be. I think I am close!

View Replies!   View Related
List Unique Values Above And Below Reference
Event Manager Status
1call A yellow
2sleep B yellow
3run C red
4jog D pink
5eat E pink
6call E yellow
7sleep F red

I need to output 4 unique Status before the event sleep. When I say unique it is compared to its previous status. So that would be [yellow,pink,red,yellow]. Notice that I could repeat yellow but not a sequential yellow. I cant use previous formula on listing unique values because I have reference point which is sleep, that i need to lookup from a huge pile of data, and i need a counter to move up. I tried vlookup with countif but i still have to define the max range. The data is huge and my menial skills are limited to excel.

View Replies!   View Related
List Unique Values From A Named Range
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.

View Replies!   View Related
Unique Values In A List That Appear At Least 3 Times (2007)






I'm looking to tweak this formula to count the number of unique values that occur at least 3 times.

So, imagine that A1:A50 contain 50 last names from the phone book, 27 of which are unique according to the formula above.

Now, further imagine that only 5 of these unique 27 last names appear at least 3 times.

I'm looking for the formula that returns 5.

And (like always), for simplicity and sharing reasons, I'm hoping to find a single formula that occupies a single cell, and does not rely on additional columns of intermediate information.

View Replies!   View Related
Finding 10 Smallest Unique Values In A List
I have been using the SMALL function to find 10 smallest values in a list, however, some of the values are the same, is there a way of only having the 10 smallest unique values within a list?

View Replies!   View Related
Create Unique List & Summarize All Associated Values
I have a list of sales events that ran at various venues, together with how many people attended and how much revenue was brought in by sales. This data is arranged by week. One venue will never have more than one event per week, but different venues may have sales events on the same week.

My table looks like:

In reality this list could be thousands of rows long.

“Event week” is the calendar week of the event. “Weeks open” shows for each event how many weeks that venue has been open (may or may not be useful!).

What I need to do is to be able to pick a number of weeks (preferably changeable by entering into a cell) and have a list produced of each venue that has run for at least that many weeks, and the sum of people and sales in those first X weeks. My idea of output, if the user input “2” as the number of weeks would therefore look something like:

Note that
1)only venues that have run at least 2 meetings are shown in the output
2)even if venue has run over 2 meetings, only people and sales figures from the first 2 are summed up and displayed.

In this case. Later on I need to produce a frequency chart/histogram grouping the results into categories, but I know how to do this as long as I can get to the step before – but wanted to mention it in case there are shortcuts.

Have been battling with this with pivots, database functions, array formulae etc. for a long time.

Attached is the spreadsheet with sample data. The red table is the part I am stuck on. The solution has to work in Excel 2003.

View Replies!   View Related
Fastest Way To Compile List Of Unique Values
I'm trying to count the number of unique values from a list of over 8,000 records -- and it's very likely that nearly all of the records are unique. At the moment, I am keeping track of an array where I store every unique value I find, expanding the array and adding values as I go, and at the end return the size of the array to tell me how many unique values there are in the range. My method works, but it is very slow, even when I turn on manual calculation.

Here is what I have so far:

Function CountUniqueCases(inputRange As String)
Dim i, j As Integer

Dim cCaseID As Range
Set cCaseID = Worksheets("Results").Range(inputRange)

Dim uniqueCases() As String
Redim uniqueCases(1)
uniqueCases(1) = cCaseID.Cells(1).Value

Is there a faster way to do this? I was hoping there was an array search function built in to VBA, but apparently not. My first attempt at running this function returns about 7904 out of ~8000 as being unique.

View Replies!   View Related
List Unique Values Of Multiple Columns
I need a UDF to make live easier. I have a huge excel file and I need a final function (I suppose a UDF) that lists all values from a range spanning over multiple columns and rows. I would not really like a macro, since it does not update when certain values are changed.

It's part of a bigger functionality, but I need this one, really bad, since it spans over 6000 rows as the search range, and 7000 as the search values. In short:
I got to list all the unique values. They are in specific cells on worksheet 1 (A1:C5 and A25:C31) and should be listed on worksheet2 starting in field A2. As 'simple' as that. Nothing more, nothing less, just list all unique values of a certain range, which I specify. If it can not be done, by selecting the two fields separately, it's no problem at all to list all values in eg A1:C6000. Do note that some of the cells will be blank, and I don't need blanks. I only need the actual data sets. I added a sample excel file, that shows a representation of the data. I can not upload the actual file, since it's way too big (60 MB). So a sample file should give you the idea.

Three sheets:
Data 1: some value lists multiple columns
Data 2: some value lists, again multiple columns
Desired result: the result as it should be listed.
example.xls. zip

View Replies!   View Related
Importing Values From Sheets To Create Unique List
I need to be able to pull information from 5 columns on 5 different pages, and pull all of the unique names out of it. From there, I need to create a list of all of these names. More often than not, this could only be three names, but I still would like this to be automatic because eventually it could be 50 or 60, and manually editing this list would get tedious. Lastly, is it possible to have a list create cells? For example: I have information on row 4, this one will start on row 6. My next group of information starts on row 14. That leaves 7 rows for information. I don't want to leave a large gap at the top of the page, so I'd like to be able to create cells if the list I'm trying to create has more information on it. Seems plausible in my mind, but I'm still new to excel.

View Replies!   View Related
How To Count The Number Of Unique Values In A List, Based On Another Value
I need to count the unique values in a list based on another value. i.e.

AB ___ Jones
AB ___ Smith
BC ___ Henry
AB ___ Jones
BC ___ Brown
BC ___ Henry
AB ___ Tomkins

For all the AB's in column 1, I need a formula that will give me all the unique names from column 2, in this case 3. I've tried adapting:


but haven't been able to get it to work.

View Replies!   View Related
How To Remove Only Unique And Keep Duplicates
I have been trying for a day now to figure out how to remove the unique cells and keep the duplicates only. I have tried a macro but it does not seem to work. Has any one run into this before. I have attached a sample what I would like to do is remove all non-duplicates from column A. The only macro I could find was this:

View Replies!   View Related
Count Unique Duplicates In Two Columns
I need a one formula solution to compare two columns for duplicates and count the number of matched pairs. Need to ingore blanks. Can have matched numbers or letters. Asterik denotes a blank (empty) cell. Also only consider the first matched pair if there are more than one matched pairs.

Col A Col B
b a
* b
c c
d y
x f
f z
f z

In this case I only want to consider a:a, b:b, c:c and f:f, resulting in the count of 4.

View Replies!   View Related
How Do You Create A Unique Random Number W/o Duplicates
I'm needing to generate a unique random value for a database with 3546 cases. The unique random values cannot be duplicates of each other. I tried the =RANDLOTTO function that I learned of in an old post on this board, but that results in "#NAME?" appearing in the first cell. I tried to install the Add-Ins (both the Analysis ToolPak and the Analysis ToolPak - VBA, but nothing seems to happen. Is there another way to generate these numbers?

View Replies!   View Related
Count Duplicates As Unique Record, Sum Amount
I have a spreadsheet will a large amount of invoice numbers, some of which are multiple occurrences of the same number. I need to count the duplicates as one unique record and sum but I need to sum the total $ amount of each amount attached to each occurrence.

Please look at the sample to see what I mean.

W234678 has three amounts that are added to give a total amount for that number and it is added to the count as one record.

I had this code kindly borrowed from someone else which helped me find the duplicates but it is not meeting my needs.

Dim rCell As Range, rRng As Range, vKey, lrow As Long

Set rRng = Range("F2:F199")

With CreateObject("Scripting.dictionary")

.comparemode = vbTextCompare

View Replies!   View Related
Remove Duplicates :: Advanced Filter, Unique Records Only
I'm having a problem deleting duplicates from list in excel. I’ve attached a sample. I’ve tried the following:

1-Advanced Filter, Unique Records Only

2-Remove Duplicates function in Excel 07.

3-Pivot Table

4-Colour Conditional Formatting, sorting by colour

5-B2=IF(A2=A3,”Dup”,”Not-Dup”). The entire column returns “Not-Dup”

6-I’ve tried to resolve using the fix shg & teylyn suggested to Hillto in this thread, but am unable to get the ‘Numeric’ Keypad to appear in the ‘Find’ Function.


View Replies!   View Related
Unique List Would Grab All Unique Entries
I have a data table that repeats as follows:


From the data above I need to make a new unqie list that would grab all unique entries.

I am trying to use the following guide:

Get stuck on "Listing A:

View Replies!   View Related
# Of Unique Dates Per Unique List Entry
Each product is represented by a serial number (column A).
The can be sorted on column A from smallest to largest prior to calculating results if that helps.

The repair list contains 1 entry per spare part used, so the same serial number may occur several times.

Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).

The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.

Sample list:
Serial........Repair date

View Replies!   View Related
Count All Unique Values (text) + Definition Of UNIQUE :D
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 ?

View Replies!   View Related
Create Unique List From Matrix & List Frequency Of Each Occurrence
I've searched the forum and believe this thread to be the closest to what im looking for, but its doesnt completely apply: matrix to list conversion. I have an attachment to support my questions (see attached). I have a matrix of words (strings) that contain repeating and non-repeating contents. The matrix also has spaces which are of no value. Essentially, the VBA needs to ' analyze' this matrix and create a consolidated frequency list (as shown in the file).

The matrices are HUGE and therefore some of the clumsy VBA i am using is turning out to be a bit inefficient. The file also contains formulas and such that I cannot use AutoFilter nor can I use Insert or Delete the VBA shouldnt use those either.

View Replies!   View Related
Macro To Paste Unique Entries Of A List Into Another List
I have a dynamic list of names from B2:B500. I want to write a macro that finds all the unique entries from that list and pastes it to AD3:AD501 everytime the macro is run. What would be the code for this procedure.

View Replies!   View Related
Duplicates In List
I need to check a list of names to see if there are duplicate entries.
At the bottom of the list I would like to count the number of duplicates.
In the list I want conditional formatting to highlight cells where the duplicate appears.
Using Excel 2007.

View Replies!   View Related
Look Across Values In 12 Columns, Return The Number Of Unique Values
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).

View Replies!   View Related
Identifying Duplicates In List
Here is the deal I have 4 columns. Each line gives you the following information:
The Id is the record number, Code_Name is a code for each Fox in the study, date and area is a sub area in a bigger grid. Basically I have an area divided by squares and every time a marked fox enters in one of my squares a new line in the data is created. What I won’t to know is if a fox when in my area of study will return to the same squares or not.












What I’m trying to achieve is a 5th column were ill get a logical value of TRUE or FALSE if, for each fox in the next available record a fox went back to the same square or not.
So if you check for the fox RRR1 I have 2 records one in 2 of January in area 1A1 and a second in 7 of January in the same area. For the Fox BBB1 you will see that she was always in different areas and for CCC1 she only came back to one square.

The problem is I have over 400 fox’s and 12000 records and I’m trying to get a way of doing it automatically.

View Replies!   View Related
Identify Duplicates In A List
I need to identify duplicates in a list and have the foilowing formula:

=IF( COUNTIF(range1,A2)>1,"Duplicate","")

This works ok but i have a further condition which i dont know how to factor into the formula. I think i could write some vba to determine the dupes but i was hoping to avoid this as im sure it will take me an hour or so. Duplicates are identified at the moment as being identical numbers in column "amount", i now need to specify duplicates as being identical numbers in this range where there is at least one row with no pay date filled in in col "paydate"

View Replies!   View Related
Compiling Validation List With No Duplicates
I have a sheet called "Usage Data" and in that sheet Column A is called "DTL_LOC" (Warehouse #)

I need to pull all data from this column and create a list with no duplicates. Data is both Text & Numeric.

I need to use this information in a Validation List on my "Inventory" sheet in C1

The list is currently located in L3:L102 on the "Inventory" sheet. However this list may increase or decrease as the company grows.

View Replies!   View Related
List Date Adjacent To Duplicates?
I have a list of Dates in Col. A
Column B contains both numerical and text values.

I need to define a value in column B, and create a list of the dates
that these occured on, on another sheet. Auto filter doesn't work
because there are several different columns. If I try to use it I also
get the values in the other columns.

1/2 8
1/3 4
1/4 Vac
1/5 8
1/6 7
1/7 Vac
1/8 8

Value needed = Vac

Solution 1/4

View Replies!   View Related
Omit Duplicates When Copying List
i need to lookup in a sheet for cells that start for example by "SE=", and copy them to another Sheet creating a list!
But some of this cells started by "SE =" are repeated, like:

SE = cars
SE = cars
SE = cars
SE = dogs
SE = dogs
SE = bike's

and this is what I'll need:

SE = cars
SE = dogs
SE = bike's

View Replies!   View Related
Sequentially Count Duplicates In List
I am trying to work out how I can assign a sequential value to sets of duplicate data within a list. i.e. where I have duplicates in a list I want each of the duplicates (that are in the same set) to have a sequential number assigned and for that to start from 1 each time it discovers a new set of duplicates.


Where a colour is duplicated in the list I want it to have a sequential number assigned. When a new set of duplicates is found the numbering starts from 1 and sequentially increases. So the above data list would resulting in the following output


Have tried variations of the RANK function, but not sure how to get the sequential nu,bering to restart with each unique set of duplicates. I would also like to do this without VBA if possible using the built in functions.

View Replies!   View Related
Refer To Cells & Only List Duplicates Once
I am making an excel document that has a log of repeating data, one column of which includes names. At the bottom of the document, I am trying to make a small box that tallies all of the people within the document and the number of times they placed an order. What is the formula for when you want excel to find a word within the document? Right now I am using =CELL("CONTENTS",C6) etc. So all that does is repeat the word when it is used. But it duplicates the word in the box down below because it appears in the log numerous times.

View Replies!   View Related
Count Unique Values, For Specific Values
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.

View Replies!   View Related
Identifying Duplicates In A List Using Conditional Formatting
I would like to identify duplicates in a list using conditional formatting in Excel 2007.

I have tried choosing to identify duplicates using the formula that I have found on many threads throughout the message board:


This function works up to 15 characters in a cell, but Excel seems to be treating all digits after the first 15 as the same, resulting in a "fuzzy match" where I want an exact match. Many of the values in my list are 18 characters long, in text format to prevent rounding.

I've noticed that Excel treats the 18-characters values the same way when sorting; for example, it treats these two values as the same:


Is there a way to force Excel to examine those last four digits for the purpose of sorting & identifying duplicates?

View Replies!   View Related
Create A List From A Column And Eliminate Duplicates
I have a list of names "H3:H66". I want to create a list starting with "L3" from column H that eliminates any duplicate name from the first list.

View Replies!   View Related
Delete Duplicates Values
2500 or so rows

based on these 3 columns:

[A], [B], [C]

company [A] , Account # [b], Fund # [c]

need to delete duplicates but keep lowest Fund # one.

basically if company and account # are the same then delete but keep the one with the lowest fund #

View Replies!   View Related
Remove Duplicates Values
I receive a customer list with over 1.000 customers. Each customer have it's own customer ID and some customers have more then one dept / case.

I now wonder how I can find duplicates if the customer ID is the same in the customer ID field and remove any duplicates?

View Replies!   View Related
Find The Duplicates Values
I have a long list of checks and I need to find the duplicates, determine how many dupicates there are for a given check (the criteria is if the ENTIRE ROW is dupicated), and highlight HALF of the duplicated rows.

I am able to sort the table however needed.

Here is the logic I was thinking of following..

If Row is Duplicated
Select Case Boolean
Case True
Count how many are duplicated
If count is even then
NumberToDelete = Count*0.5
For i = 1 to NumberToDelete
Highlight one of the duplicated rows in red background
Next i
Next Record (do not highlight anything)
End If
Case False
End Select
End If

View Replies!   View Related
Hiding The Duplicates Values..
I'm trying to hide all but one duplicate value in a list; I have a report that can give multiple results for each record, but to make it cleaner to read I want to hide the duplicate values, such as customer names etc. after they have first appeared. I'm aware this may not be a very clear explanation so have attached an example spreadsheet.

The left-hand list is similar to what I would start with and the right-hand list is what I am looking to achieve. Conditional formatting will format duplicates but will obviously all to all and can't use the remove duplicates function as it will take away the unique values alongside them in the row. Preferably I'm after a solution that works in both '03 and '07 but it's not a disaster if I can only do it in '07.

View Replies!   View Related
Delete Duplicates Except Specified Values
I have a random set of numbers located in one column. This is the only data located on the entire worksheet. I need an Excel macro that will delete all duplicates except for any specified values. For example, I have a set of random numbers in one column and I need to delete all duplicates except for the number 12. All duplicates of the number 12 must remain. The numbers must remain in their original order and not be sorted.

The numbers will always be located in column A, but will not always be the same quantity. One time I may have 10 numbers, and the next I may have 20. The number for which duplicates must remain will also vary. I have written small Excel macros that delete rows, blanks, and duplicates but this is way beyond my capabilities.

View Replies!   View Related
Remove The Duplicates Values
We are having a problem merging our databases. We are using excel to streamline databases to be included in our GiftWorks database of donors. However we are having the problem of many duplicates with multiple addresses, contact info etc. I have come to find that there is no way other than the "bitch work" method of going thru by hand in the GiftWorks database to find duplicates. However we can make a smartlist and export to excel and then perhaps merge the new info with this one. I was just wondering if there is anyway to sort duplicates and merge their information by first name/last name.

View Replies!   View Related
Count Number Of Different Values In Duplicates
Below is a range of data i am working with, i am trying to create a formula that will count the number of different entries in column A for each different entry in column B.
ie how many differnt values are there for "Packing"


View Replies!   View Related
Unique List
I have a list of values in column A. How can I put a list of all the unique values contained in column A in a list format in column C?

I would prefer to use VBA code as to automate the process.

View Replies!   View Related
Unique Keyword List
I have a large keyword list
lets say it's 25,676 keyword phrases all in ColA (Always from A3 downwards).

I delete rows as I'm sorting so the number does very.

The sheet tab is always called "All KWs" (For All Keywords Not surprisingly)

What I would like is a macro that does the following.

Can it look at all the keyword phrases on sheet "All KWs" in ColA (From A3 downwards and make a list on a new sheet of just unique keywords from the list.
This sheet will be called "Unique keywords"

So, can it return on this sheet called "Unique keywords" all the unique keywords from the large list in ColA (From A3 downwards) on the sheet "All KWs".

If it can return only unique words and numbers etc, removing all duplicates.

If it can do this From A3 downwards.....

View Replies!   View Related
List Unique Names
I have a column of names in Column D

I need code to list these names in column G

when I hit a button.

View Replies!   View Related
Count Of Unique List
to calculate the count of unique text values from a list based on condition.

I have 2 columns A & B. In column A, I have Yes or NO text and in column B, I have Client names.

I want to count a list of unique values from column B if it is Yes in column A.

I have to write a formula to do this task and will appreciate any expert help in solving this.

View Replies!   View Related
Copyright © 2005-08, All rights reserved