Excel 2007 : Find lowest number in column seperated by common names in another column

**fentontech** · 04-16-2012, 01:19 PM

I have 3 visible and 1 hidden column,

The first column is the name of the location. There will be multiple entries of one location and multiple locations listed in this column.
The second column is a place holder for a link to view file for the location in column A.
The third column is for entering comments
The forth and hidden column is formatted to generate unique random numbers.

My goal is to achieve the following: For every location in column A(First Column) pick the 5 lowest numbers from Column G(fourth column) as long as the word view is in column B(second Column) and highlight the appropiate cell in column C(third Column).

So in result I should have 5 cells highlighted in column C for every location in column A.

I am including an example of my spread sheet... I am also coloring cells in Column C so you can get an Idea of what I want the end result to be.

random.xlsx

**daddylonglegs** · 04-16-2012, 01:30 PM

In your example some of the highlighted rows don't have "View" in column B, is that right?

assuming you do need "View" in that column try this formula in conditional formatting

=AND(B2="View",G2<=SMALL(IF(A$2:A$100=A2,IF(B$2:B$100="View",G$2:G$100)),5))

see attached example

**fentontech** · 04-16-2012, 02:36 PM

sorry... yes it require view in the formula... I will try this out.

**fentontech** · 04-16-2012, 03:08 PM

This works great unless you have less than 5 locations....

example... if you delete enough rows of one of the locations to only leave two.... you will see it wont highlight either of the two locations you have left behind....

I do notice that if I drop the number of cells to highlight down to one less than the least of amount of locations then it will work....

**daddylonglegs** · 04-16-2012, 06:32 PM

OK, if you have less than 5 rows with "View" for a specific location then this version will highlight all of them

=AND(B2="View",IFERROR(G2<=SMALL(IF(A$2:A$100=A2,IF(B$2:B$100="View",G$2:G$100)),5),1))