# 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
ADVERTISEMENT
Mar 18, 2013

I'm after a formula to find the smallest unique value in a range

For example..

1

1

2

3

Small(Range,1) = 1

Small(Range,2) = 1

But i need the second row to show '2' as the result.

View 7 Replies
View Related
Oct 6, 2008

Attached is a sample file that has a Data entry sheet in which the user can paste in data. They can test & paste anywhere from 1 to 50 compounds at a time and run the list through 1 to 55 Assays. I want to generate a sheet (Plate & Assay Info) that would first display the list of compounds that were tested as well as the list of Assays that they were run through.

View 13 Replies
View Related
Dec 6, 2011

I have 15 different words that I want to order in as many ways as possible, but only 3 at a time.

So let's say the words are:

cat

dog

elf

clown

monkey

rock

bananas

(etc)

then I want a list that has all of these possible combinations:

cat dog elf

cat dog clown

cat dog monkey

cat dog rock

cat dog bananas

cat elf clown

cat elf monkey

(etc)

until all are listed. I understand there is a huge number, I don't mind having a couple of thousand as long as they are all genuinely unique.

I CAN have elf dog cat, elf cat dog, dog elf cat later on ... it's just the order that needs to be unique not the words in the phrase.

View 9 Replies
View Related
Nov 22, 2013

I have an excel sheet with about 1000 reference numbers in. I would like to know how many of these are unique...they look like this:

UCR104

UCR1042

UCR1043

UCR1048

[Code] ........

View 4 Replies
View Related
Dec 17, 2013

I have a file with immunophenotypic markers and their relationship to certain blood cells.

What I need to do is search the list and pull out the groups of defining markers for each blood cell. Some of the cells have many markers, some only have one but I need to find which combinations of markers identify each cell if possible.

I have attached the file (I think!)

View 14 Replies
View Related
Jun 6, 2014

I currently am working with a worksheet that has over 250,000 data points. It is a spreadsheet for a company in which different work order are organized. A given work order can have several parts (ranging anywhere from one component to 40ish) and thus the height of the cells in which each specific work order is placed differs from work order to work order. For each component, the spreadsheet tells you how many can currently be made based on the materials available. What I would like to do, for each specific work order, is go through the data and simply tell whether or not the work order can be completed (the quantity of each specific component needed is also given in the spreadsheet) and then report out exactly how many components can be made.

Ultimately, this means that a "limiting factor" analysis must be done. For instance, if the work order is asking to create plastic bottles that consist of the bottle, the cap, and the label and it asks for 3 bottles but only 2 caps are able to be made, the limiting factor would be the caps and thus only two bottles could be made and the work order could not be fully completed.

The spreadsheet is set up based on another worker here, I am just trying to come up with a way to analyze it. Cells A1:Axxxxx are the work order numbers and the size of these cells differ because they are merged to fit the number of components they have. The componenets available are then ranged C1:Cxxxxx etc and the quantity needed are also listed in a similar fashion. What sort of structure could I use.

View 7 Replies
View Related
Feb 16, 2008

Find smallest numbers in the range ignoring zeros.

I want it returned as value "1", all others - as value "0".

Example: range A1:J1 contains - 5,0,7,3,6,5,8,0,9,3

desirable result in the range A2:J2 - 0,0,0,1,0,0,0,0,0,1

View 9 Replies
View Related
Aug 21, 2008

In column A, I have a list of names.

In column B, I have a list of values.

I want column C to show me which name has the smallest value from column B.

In other words, if there are 5 names, I want ONE name to give me a value of 10 (for the smallest value), and the rest of the names I want to show zero.

I also have in column D another list of values.

In column E, I want to show first, second and third place amongst the list of values from column D. The rest of the names I want to show zero.

View 17 Replies
View Related
Sep 17, 2009

I'm creating a spreadsheet and have had difficultly creating a function that returns the second smallest unique value.

For example if my data set is:

2, 2, 3, 3, 4, 5, 6

I'd want the function to return "3". I've tried playing around with the SMALL(array, k) function and trying to use a COUNTIF function for "k".

View 6 Replies
View Related
Apr 25, 2013

I have a spreadsheet with some calculations and the part i am interested in is data in R4 : V58

In this R4:R58, S4:S58 and T4:T58 have some rows that have False because of IF calculations that put False in some rows of R and hence in S and Hence in T.

For example, R15="FALSE" so will S15 and T15 be. Col U is based on whether the corresponding row in R is False or a number. If a Rown in R is false, the row in U= 0 else the row in U is the same as in the same row of a different column - Col I

Col R, T and U are numbers. Col S is text

For example:"

R S T U

False False False 0

False False False 0

False False False 0

55 DEF 3 15

46 XYZ 2 67

False False False 0

False False False 0

23 GEF 4 43

I want a table in another worksheet that will sort through this using a formula and print

Col1 Col2 Col3 Col4

55 DEF 3 15

46 XYZ 2 67

23 GEF 4 43

If I can define things like where the table will be placed in the new worksheet etc, that will be a bonus..But It appears I cannot understand how to do this at all..

View 3 Replies
View Related
Mar 8, 2012

Lets pretend that I have a boat load of automatically generated values from a different program.

There's a specific equillibrium point to a certain measurement, but this measurement involves a lot of fluctuation. To get a near accurate result, I must find the maximum value in an entire graph on the Y Axis value(Column B). Ofcourse, this is easily done using the =MAX value

Exactly 450 seconds (X Axis value(Column A)) positively and negatively from this point, something like this happens.

Problems:

A.) I must find the highest points 450 seconds negatively ( X ) to the max of the entire graph.

B.) I must find the lowest point closest to the two points we uncovered, both on the positive ( X -->) side.

C.) The fluctuation of every spike can last to about Â±30 measured points in the graph. (Â±20 seconds) Thus, the maximum point surrounding the +/- 450 seconds (Â±693 x values) needs to be accurately predicted as being around 450 seconds, not 10 seconds more or less.

D.) The measurement is known to something glitch out and suddenly get a spike somewhere along the road, which will then quickly fall back down, which is a false maximum level. How can I prevent excel from taking this as the maximal point?

Remember, this needs to be fully automated into a macro later on, ergo, I cannot manually add the formulas to all the (650 different) sheets.

I have 25 days before the project gets cancelled if this doesn't make it.

View 4 Replies
View Related
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
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
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
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
Sep 5, 2012

I am trying to create a top ten list of my clients, my clients are listed by customer numbers. The data is listed as follows:

Client ID

7164

7164

7164

5781

5781

6392

So my results should be:

Top 1 7164

Top 2 5781

Top 3 6392

I have been trying to use Mode, but that only returns the top value.

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

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