Get Unique Values In A List Of Duplicates

Feb 28, 2007

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.)

ADVERTISEMENT

Make Unique List From List Without Duplicates But Hold Cell And Pull Corresponding Dates

Apr 18, 2013

I have my dummy data, and I have (what I think) is how I want the data to be shown. My friend uses Google Sheets, but I prefer Excel. I am trying to convert the code because I am a stickler for excel. Typically I can convert codes some easily, but this is way beyond me.

For Column A: I want to create a list on sheet 'Setup!' based on ids!D2:D="yes". If that list has duplicate entries, I would like only the first entry to show up, but for the next entries I would like the cell to be blank. (this is important for the next step) For Column C: I want to have the corresponding dates go with the name entry. For Column D: I want to have the notes go with the corresponding date entry. (I believe I can manipulate Column C's code to do Column D myself).

I am also going to upload a data sheet, and an expected results sheet.

unique list.xlsx

View 1 Replies View Related

Unique List With No Duplicates

Jan 11, 2007

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 3 Replies View Related

Create Unique List From List With Duplicates

Nov 28, 2007

I have a list of data which looks like this

Column B

Name
---------
Jones, Bob
Jones, Bob
Jones, Bob
Smith, Mike
Smith, Mike
Smith, Mike
Calai, Dave
Calai, Dave
Calai, Dave
Etc...

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 9 Replies View Related

Identify Duplicates: Get A Unique List

Jan 12, 2010

I have a list of objects:

A
B
C
D
X
D
A
F
G
H

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 2 Replies View Related

Create Unique List From Column With Duplicates

Feb 27, 2008

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 7 Replies View Related

Count Of Unique Values With Duplicates

Nov 29, 2007

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 4 Replies View Related

Macro To Find And Delete All Duplicates And Keep Only Unique Values

Sep 22, 2009

Looking for a macro to find and delete all duplicates and keep only unique values from a column. For example column AS has
ACLU0403598
ACLU0403598
ACLU0403598
ACLU0406600
ACLU5165518
ACLU0406581
ACLU0406581

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 6 Replies View Related

Vertical Lookup To Remove Duplicates And Return Unique Values Horizontally?

Mar 3, 2014

In column CT between rows 11:210 is the vertical data that includes duplicates. I am looking to create a formula that can lookup each value between CT11:CT210 and return only the unique values horizontally starting in cell CW9 and onwards.

Example: CT11:CT14 looks like
5x20
6x4
5x20
5x8
and so on and so forth..

I would then like this formula to return the above data (which goes all the way to CT210) in this format starting in cell CW9 - 5x20 then CX9 - 6x4 and finally CY9 - 5x8.

View 4 Replies View Related

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

View 3 Replies View Related

Obtaining List Of Unique Values From List Based On Some Criteria?

May 28, 2014

The function below should return the value in a cell IF the string in the cell two cells to the left of it is "Nemo" Omitting the part highlighted in blue, my function returns a list of unique values...but when it's included, nothing is returned at all. Perhaps, there is an error in the syntax that I'm overlooking?

Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String

[Code]....

View 2 Replies View Related

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.

View 7 Replies View Related

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 |

View 14 Replies View Related

Adding Duplicate Values And Extracting New List Without Duplicates

May 23, 2013

I have a name in Column A, and a number that goes with it in Column B. Some of the names on Column A are duplicates. I want to extract a new list that will add the values in Column B of all matching names on Column A, so that my list has only unique names and a total of the numbers in Column B.

A:A B:B
SPP-001 2
SPP-002 3
SPP-006 4
SPP-001 5
SPP-008 9
SPP-006 3

Result that I'm looking for"

C:C D:D
SPP-001 6
SPP-002 3
SPP-006 7
SPP-008 9

View 6 Replies View Related

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

View 8 Replies View Related

List Of Unique Values In One Cell?

Dec 27, 2013

I have a list of all transactions that happened during the day at different locations On another sheet I want to list all the locations and in another column to populate a list of unique transaction codes for each location from the first sheet (all unique values for each location in one cell). I am attaching a spreadsheet in case I am not clear enough (I need to create a Summary). This attachment is really a simplification of a file I work with. The real one has many columns and multiple subtotals, I would not be able to incorporate a method that involve filters

View 5 Replies View Related

How To Produce List Of Unique Values

Jan 21, 2012

I was trying to produce a list of unique values from a much larger list. I'm using this array function -

{=IFERROR(INDEX(Sheet2!$B$2:$B$100,MATCH(0,COUNTIF($B$1:B1,Sheet2!$B$2:$B$100))), "")}

- which is marvellous and gives me exactly what I want. However my test book only has this dragged down 10 rows and already it takes 20-30 seconds to calculate. The real workbook is likely to stretch to several hundred rows, and to make matters that little more complex where I have Sheet2!$B$2:$B$100 this will relate to a completely separate book on a company network.

Is there any more efficient method of creating this list or will I just have to bear with the calculation speed?

View 4 Replies View Related

Create List Of Unique Values

Jul 23, 2007

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

Do
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 9 Replies View Related

Populate List With Unique Values

Jan 5, 2008

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 9 Replies View Related

List Unique Values Above And Below Reference

Jan 26, 2008

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 2 Replies View Related

Make A List Of Unique Values Using 3 Different Columns?

Feb 26, 2014

See attached file with a smaller sample of the 1667 row table of data I am working with. What I am looking to do is make a list on another sheet of each every line with a county and have the corresponding Township and range with it. So if I have a cell and I select Audubon county, I would like a list to populate that has the 12 lines of Audubon county in column A, and the Township in column B and the Range in column C.

Excel Rate Example.xlsx

View 5 Replies View Related

Unique Values In A List That Appear At Least 3 Times (2007)

Jan 16, 2010

=COUNT(

1/FREQUENCY(

MATCH($A$1:$A$50,$A$1:$A$50,0)

),

ROW($A$1:$A$50)-ROW(OFFSET($A$1:$A$50,0,0,1,1))

+1))

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 6 Replies View Related

Finding 10 Smallest Unique Values In A List

Oct 2, 2008

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 2 Replies View Related

Create Unique List Of Values Across 5 Sheets

Oct 13, 2011

I am trying to create a macro that will create a unique list of the values in the same column across 5 worksheets, "Question1", "Question2", etc, and place the list in a new worksheet, "Summary".

The values are in column A in all of the worksheets, and the number of rows in each worksheet will change each day. I need the unique list to be added to the sixth worksheet, "Summary".

Also, after the list is created on the Summary worksheet, I need a count of each field by worksheet and a total. This is how I currently have the Summary set up:

Part Type|Total|Question1|Question2|Question3|Question4|Question5
Name|=SUM(C2:F2)|=COUNTIF(Question1!$A:$A,A2)|
=COUNTIF(Question2!$A:$A,A2)|=COUNTIF(Question3!$A:$A,A2)|
=COUNTIF(Question4!$A:$A,A2)|=COUNTIF(Question5!$A:$A,A2)

View 6 Replies View Related

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.

View 9 Replies View Related

Create Unique List & Summarize All Associated Values

Jan 30, 2009

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 4 Replies View Related

Fastest Way To Compile List Of Unique Values

Jul 3, 2007

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 5 Replies View Related

List Unique Values Of Multiple Columns

Mar 12, 2008

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 4 Replies View Related

Create Unique List Of Values From Two Ranges With Criteria?

Jun 12, 2014

I have two lists of values in separate columns with condition in adjacent cells, all on the same sheet

List 1 consists of numbers from 1 to 12, column to the right is either blank or Free
List 2 consists of numbers from 13 to 24, column to the right is either blank of Free

List 1 starts in A2, B2 is either blank or Free
List 2 starts in D2, E2 is either blank or Free

In column G, from G2 i want it to list all values from List 1 and List 2 where their status is Free

View 14 Replies View Related

List Unique Values In Excel And Count Occurrences Of Each?

May 25, 2014

I realise this is beyond me and wonder if it is possible to list uniqure values in excel and place a count occurrences of each. I have a hugh list of over 400 alpha code i.e ABCD, ABCD, ABCE, BDCE, BDCF ZXYE etc etc. What I'd like to do is pick out all the unique values so I can see what alpha codes are on the list. But then I would also like to count the occurrences of each value/name so I know how many times they appeared on the list.

Col A
ABCD
ABCD
ABCE
BDCE
BDCF
ZXYE
ZXYE
YEZY
CDEF

Col B is where i would like to list all the unquie vaules with a count e.g.
ABCD 2
ABCE 1
BDCE 1
BDCF 1
ZXYE 2
YEZY 1
CDEF 1

View 14 Replies View Related

Importing Values From Sheets To Create Unique List

Jun 2, 2009

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 14 Replies View Related

Combine Values From Two Columns To Produce Unique Value List

Jun 28, 2013

I have two columns of data and would like to generate a new list showing unique combinations of those values (see attached sheet).

unique list.xls

View 9 Replies View Related

Create Unique Values List While Overwriting Old Data?

Jan 24, 2012

I will copy a report into sheet 1, starting in the same place(A1), on a weekly basis. The headers are: Item, Quantity & Price

Week 1's report shows the following:
Windows 5 $1.00
Doors 6 $1.50
Chairs 7 $2.00

Week 2's report shows the following:
Windows 3 $1.00 (change in quantity)
Tables 7 $5.00 (new item)
Chairs 7 $2.50 (change in price)

I need to have a rolling, unique list on Sheet 2 that will do the following:

- add new line items

- keep old line items while checking to see if the quantity or price has changed.

Therefore, after week 2's report is pasted on sheet 1, Sheet 2 should look like this:

Windows 3 $1.00
Doors 6 $1.50
Chairs 7 $2.50
Tables 7 $5.00

Here's the most important part: There will be formulas in Column D in Sheet 2 that I need to stay with each item. generating the desired result on Sheet 2!

View 9 Replies View Related

Create 1 List Of Unique Values From 12 Columns Of Data

Apr 28, 2014

I have a spreadsheet of 12 columns (A to L), with a list of student names under each down to row 31. Each column represents an after-school club that the child can attend.

However, a child can sign up to multiple clubs, so their name can appear in several places across the spreadsheet.

What I would like to do is have a 13th column which is an overall list of students who have signed up to at least 1 club, i.e. their name appears at least once in columns A to L.

How do I achieve this so that only unique entries are listed and duplicates are removed in this 13th column?

View 7 Replies View Related

How To Count The Number Of Unique Values In A List, Based On Another Value

Jul 27, 2009

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:

=IF(LEN(June!D5:D100),SUMPRODUCT(1/COUNTIF(June!D5:D100,June!D5:D100)))

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

View 9 Replies View Related

Collecting Unique Values Of Multiple Ranges Into One Master List

Dec 20, 2012

I gather two lists of serial numbers (i.e.) from two different sources; one populated in sheet1, A:A (with variable row numbers) and the other in sheet2 B:B (again with varying number of rows)

I am trying to gather a master list of unique serial numbers in sheet3, C:C.

So far I have tried advanced filter, union.range, etc. but couldn't figure work it. I guess some of these columns need to have names assigned to it and have a header.

View 9 Replies View Related

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

View 3 Replies View Related

Two Cells Data Validation List Should Be Populated In Third Cell With Unique Values?

Apr 21, 2014

In cell H5 there is list of dates and in H7 there is codex, based on these two conditions in H11 a data validation list should be populated from the code_sheet using column B. The populated list should be unique entries, as the column B in code_sheet has duplicates.

View 9 Replies View Related

Dropdown List Always Shows Unique Values From Column Based On Lookup Value

Jul 22, 2014

I have a worksheet (say, Sheet1) that I am going to manually import a large data set into on a weekly basis for reporting purposes. One of the columns from this data dump will have the header "Design Id" in the first row, but it may not be in the same column every time. I am trying to figure out how to create dropdown lists on a separate worksheet (say, Sheet2) in the same workbook where the data source always looks up the column containing the header "Design Id" from Sheet1 and then returns only the unique values from that column as options in the list.

View 1 Replies View Related

Formula To Generate Unique List Of Missing Values By Comparing Two Lists?

Mar 28, 2014

Is it possible to have a formula that generate a Unique list (List 1 and List 2) of Missing values in List 2 by comparing two lists? Something like this

List 1 List 2 Missing in List 2
1 1 3
3 4 6
5 5
6 6

View 3 Replies View Related

Creating Unique List Of Values From Column Of Data Across Multiple Sheets No Blanks

Jul 8, 2014

I have 6 worksheets in my file. In sheets 1-5, column A2:A26 list people's names. Some people's names appears on more than one sheet. Not all cells are populated with a value.

ex.
SHEET1
COLUMNA
Bill

[Code]....

My attempt was... =INDEX('Week1:Week5-!$A$2:$A$26,MATCH(0,COUNTIF($A$1:A1,"Week1:Week5"!$A$2:$A$26),0))

where the sheets were Week1-Week5 and the values on each sheet was A2:A26. But I think there's an issue with Excel being able to 3D reference for these types of functions.

View 3 Replies View Related

ADVERTISEMENT