# Finding Smallest Unique Value

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.

## 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?

## Finding Smallest Value Within A Range

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.

## Finding Non Zero Smallest Numbers

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

## Finding Smallest Value In A Column

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.

## Returning Second Smallest Unique Value

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

## Finding Smallest Value In Range Close To Specified Calculated Point

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.

## Address Of Smallest 5 Numbers (return The Value In Column 1 For The Smallest 5 Numbers)

Jan 14, 2010

I am using the following array equation to return the value in column 1 for the smallest 5 numbers. It works for small 1 and 3 but i get a #NUM! for 2 , 4 , and 5. The smallest 5 numbers are:

0
3.196408441
4.491972091
4.491972091
4.491972091

Equation:

## Lookup But First Finding A Unique String?

Jul 3, 2014

mockup.xlsxStarting out with a 9 by 11 grid.

Column A will have times that need to be referenced in a lookup. (Ex Sat 8:00am)

Row 1 will have basball field codes reference with a lookup. (Example Field 4

Cells B2:I11 have distinct codes or possibly nothing. (An example would be: Tier 1 A1-A3)

These codes will move around from time to time. They will always remain distinct within the grid, but could appear in any of the cells from B2:I11.

I have 2 columns I want to populate with values from the grid.

First is Fields. I want the first cell to populate with the baseball field found in row 1 of which the code Tier1 A1-A3 falls under.

For example if this season Tier1 A1-A3 is found in the column under Field 4, then I need to return Field 4 as a result.

Second is the time. I want the time to be populated based on what row the Tier1 A1-A3 code falls in.

For example if Tier1 A1-A3 is in the Sat 8:00am row, then I need that value returned.

In short, I need to search the grid and find a given value. I then need to use a lookup (or other tool) to return the first value in that row. And secondly via lookup return the first value in that column.

edit: added spreadsheet example. First sheet is grid in question, 2nd sheet is a typical schedule.

The first Team (Scared Hitless) is A1, the team they play first is Sands Duel Fuel (A3) They play at 11:00am on Field TR3.

You will see on the grid under TR3 and Sat 11:00 the code Tier 4 A1v3. This code is what I want to search and correlate into times on the schedule rather than doing the data entry by hand.

## Finding Unique Combinations Of 2 Columns

May 30, 2008

I have a spreadsheet with two columns:
The first one will always have a 'W' or a 'E'.
The second one will have a three digit code. Like this:

W 507
W 507
W 507
E 504
E 504
W 505
W 505
W 504
W 504

Is it possible to have output that show how many of each combination there is? Like this(for the example above):
W 504 2
W 505 2
W 507 3
E 504 2

## Finding Unique Values In A Column?

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

## Finding Unique Groups Of Values In Columns?

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

## Finding Unique Names When Comparing Two Lists

Jan 25, 2010

I have two different lists of names. One is about 3000 names long and the other has about 3500 names. I want to identify the 500 people that are not in the first list that are in the second. If there anyway to do this? I was going to put them in A-Z order and then go through and using an IF(A=B,"yes","no") and go through and insert new cell when ever it says no till it says yes BUT the functions start messing up when I insert a new row and it's taking way too long.

## Finding & Displaying Unique Data Within A List

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.

## Finding All Unique Combinations Of List Of Words?

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.

## Count No Of Unique Months For Finding Average

Mar 11, 2014

I am trying to calculate the average headcount for different categories (over a certain period of time, by product, by job type etc) and I want to do it using a formula instead of a pivot table.

I have this set of data with a detailed headcount for every month. And I can find out the total HC for different categories using sumif/sumifs but if want to find out the average for a certain period, I need to split that total to the number of months where I have a HC.

For example, if the HC per month would be:

May 2013 - 12
June 2013 - 10
July 2013 - none
August - 5

in a 4 months period I would have a total HC of 27 and the average would be 9 (27/3 - July shouldn't be counted). How can I get the average in a single formula?

Month
Type
L/H
Prod type
HC

[Code].....

## Finding And Returning Unique Text From Incomplete Table?

May 11, 2014

I am building a table that assigns three roles to chores: Accountable, Helper1, and Helper2. I have a "user interface" tab, where there is a dropdown list where particular chores can be selected. I am looking to have the people in each role for the selected chore returned into the cells. I have attached an example file to clarification. For example, I am looking to have the name (Mike, Shannon, Tom, Blake, Kelsey, Sam) from tab #2 populated into the related cells C3, D3, and E3 on tab#1. I think it's possible with a long imbedded IF function, but I am looking for a more simple solution if possible.

Excel_Help3.xlsm.xlsx

## Finding Unique Entries Among Two Columns Of Alphanumeric Data

Jun 16, 2006

Columns A & B contain several thousand Project Numbers (e.g., P1052, PA844, etc.). Many of the Project Numbers in column B are the same as in column A, but column B also has additional (i.e., newer) Project Numbers scattered throughout. Ideally, I would like to use a built-in function (versus a custom function if possible) that compares all the Project Numbers in both columns and then separately lists those that are unique to column B.

## Finding The Number Of Unique Items In A Range That Contains Empty Cells?

Aug 15, 2014

It is about finding the number of unique items in a range that contains empty cells.I can intuit the form of this:

Formula:

[Code]....

And many of the variations of it when there are no empty cells in the range.

For a range of that contains empty cells I've tried this:

Formula: [Code] .....

And variations of it. It does not work, and I don't know why...............however..........

I ran across this on another [SOLVED] thread and it does work:

Formula: [Code] .....

I would have never considered this plausible. What is the magic behind appending an empty string to the criteria in the COUNTIF function?

It strikes me that this strategy likely has application elsewhere, too.

## VBA - Finding And Counting Unique Dates And Days Of Week Within Date Range

Feb 11, 2013

Within a user entered range of two dates, I would like to identify the individual calendar date(s) and count the number of Mondays which fall within the specified date range.I will eventually be using the same "Monday" code to find the same data for every day of the week within the dates ranges, but I figured I'd start with Mondays and build from there.

For Example: Date range 1/1/2013 - 1/15/2013 (date ranges could potentially encompass a full business quarter) Within the range, list each of the dates as dates. (used for comparative counting purposes elsewhere in the document)Count the number of Mons, Tues, Weds, Thurs, Fris, and Sats within the date range.Based on the example date ranges above; Mons = 2, Tues through Sats = 3 each.

## Finding Unique Values And Creating Shorter Table (sublist) Using Formula

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

## Smallest Number Which Is Not Zero

Dec 21, 2006

How can i easy return the smallest number higer than 0 from a set of numbers?

## Second Smallest Number In A Row?

Jan 25, 2013

I need a formula that will find the second smallest number in a row. The row can contain as many as 200 numbers with many of them being zeros. That makes using the "small" function impossible.

## How To Neglect Smallest Value In A Formula

Feb 26, 2014

I have 2 columns of numerical values (A and B).. I want to make a formula that takes the Sum of A/the Sum of B, neglecting the smallest value of A and it's corresponding B value

For example...

19
20

20
20

15
20

Clearly, 15 is the smallest value, and I want to know how I can take the sum of A/sum of B neglecting the 15

Basically, I want the formula to do (19+20)/(20+20)

The actual scenario has a lot more than 3 values, and I don't want to go through all of them (they change frequently)

I know how to make the simple formula..=SUM(A:A)/SUM(B:B), but I do not know how to make excel recognize, and not include the smallest value..

## Largest And Smallest Sequences

Jan 12, 2009

I am using excel 2007 and I need help with the following if anyone would be kind enough. I have a dynamic array that consists of stream of 1's and 0's. I want to be able to find the largest sequence of 1's and the smallest sequence of 1's. So at a point in time the array mite be 111110001101111000 and I want to be able to dertermine the longest contiguous sequence of 1's. But as I say the array is dynamic and so I need to do that after each input.

## Sum Smallest Value Per Row If Value Exists In Column A

Jul 24, 2009

The whole "story" is explained, in details, inside the attached WB.

## Smallest Value In Range Linked To Another Value.

Mar 17, 2009

D3 = SMALL(H6:H45, COUNTIF(H6:H45,0) +1)

to determine smallest value in range excluding 0 is it possible to link that smallest value to another value like a name?

Eg. H6 = 5 K6 = Ben
H7 = 4 K7 = John
H8 = 9 K8 = Ted
Thefore D3 = John

## Sum Of Remainder Or Smallest Number/s

Jul 17, 2012

I want to count the smallest numbers in a range of 10 cells after omitting the 4 largest. I have been using the formula

=SUM(IF(ISNUMBER(LARGE(E5:N5,{1,2,3,4})),LARGE(E5:N5,{1,2,3,4})),D5),

This works perfectly in that in the desired cell it enters the 4 largest numbers and a specified cell. I then want to be able to total the remainder.

## Averaging Smallest, Non-zero Numbers In A Row

Nov 24, 2006

I'm struggling to get the syntax right for this formula. I've been trying variations on the following formula but can't get it right:

=AVERAGE(SMALL(IF(AP8:AZ80,AP8:AZ8),1)))

Columns AP:AZ:

1.8921.7621.6501.1741.2261.2991.3020.0000.0001.1461.333
Any column in this range could have a zero value.

What I need: The average of the 8 lowest non-zero numbers in range AP:AZ

## LOOKUP Return Smallest Value

Feb 10, 2007

I have a large set of numerical data, with each column having a text heading in the first row.

I want to use a function similar to HLOOKUP that can return the numerical value in a certain row of a column that has a specified heading....AND if there is more than one numerical value that meets the criteria, I want it to return the lowest numerical value.

My numerical data cannot be sorted into ascending order.