How to find the lowest number in a range, then the next lowest etc.etc.

Hi,

I've put your test data in cells B1:B18

C22 =SMALL($B$1:$B$18,1)
C23 =COUNTIF($B$1:$B$18,C22)

D22 =SMALL($B$1:$B$18,SUM($C$23:C23)+1)
D23 =COUNTIF($B$1:$B$18,D22)

If you drag D22 & D23 across you should get all your unique Values listed.

Regards

Roger

ElmerFud said:

I have a range of numbers going down a column. Example: Column (B4:B273) with numbers going down the column 1,1,1,1,2,4,18,1,1,1,18,22,9,14,18,3,1,1 etc. etc.

What I’m trying do is in a separate row going across, let say row (C335, D335, E335, F335, etc. etc.) is put the lowest number in the above range (B4:B273) in cell C335 which would be the lowest number (1), then the next cell D335 would be the next lower number(2), next cell E335 the number (3), then (4), (9), (14), (18), and so on. It needs to skip the repeated numbers in the range. Max & Min, Small & Large work on the first three or so and that’s it, won’t go any further. Can someone help with this easy formula to get it to work correctly? Thanks~

Click to expand...

One way...

Enter this formula in A335. This will return the count of unique numbers in the range:

=SUM(--(FREQUENCY(B4:B273,B4:B273)>0))

Enter this formula in C335:

=MIN(B4:B273)

Enter this array formula in D335:

=IF(COLUMNS($C335:D335)>$A335,"",MIN(IF($B4:$B273>C335,$B4:$B273)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across until you get blanks.

Thanks so much ^^ that worked perfectly! Now, how can I use those numbers 1,2,3, etc. from the formula you gave me and reference those numbers in each cell to find the Location, Total Amounts and Total Job's to go with each corresponding number 1, 2, 3, etc. only, automatically each time.

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<TABLE style="WIDTH: 691pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=919><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" span=6 width=89><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_2822463 class=xl72 height=17 width=102>Number </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 67pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 width=89>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 67pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 width=89>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 67pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 width=89>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 67pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 width=89> etc. </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 67pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 width=89></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 67pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 width=89></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 width=91></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 width=101></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 width=91></TD></TR><TR style="HEIGHT: 3.75pt; mso-height-source: userset" height=5><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 3.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=5></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=17>Location</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70>Dodson City</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70>Amherst Cnty</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70>Wickland City</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70> etc.</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=17>Total Amounts </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65>$803,748.17 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65>$437,443.31 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65>$303.13 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65></TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=22>Total Job's</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67>170</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67>39</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67> etc.</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67></TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 320pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=426><COLGROUP><COL style="WIDTH: 320pt; mso-width-source: userset; mso-width-alt: 15579" width=426><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 320pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_2822463 class=xl67 height=17 width=426>
(Example is for #1 only! This is what I have in the cells now, which I do manually each time)


=MIN(B4:B274)


</TD></TR><TR style="HEIGHT: 3.75pt; mso-height-source: userset" height=5><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 3.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=5></TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=17>=IFERROR(VLOOKUP("1",Jurisdictions!B2:C25,2,0),"")



</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17>=IF(C296=1,SUMIF(B$4:B$274,"1",O$4:O$274),"")</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 height=22>
=IF(C296=MIN(B4:B274),COUNTIF(B$4:B$274,"1"),"")


</TD></TR></TBODY></TABLE>
(Example is for #2 only! The is what I have in the cells now, which I do manually each time)

<TABLE style="WIDTH: 624pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=832><COLGROUP><COL style="WIDTH: 624pt; mso-width-source: userset; mso-width-alt: 30427" width=832><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 624pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_2822463 class=xl67 height=17 width=832>{=IF(COLUMNS($C296:D296)>A315,"",MIN(IF($B4:$B274>C296,$B4:$B274)))}</TD></TR><TR style="HEIGHT: 3.75pt; mso-height-source: userset" height=5><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 3.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=5></TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=17>=IFERROR(VLOOKUP("2",Jurisdictions!B2:C25,2,0),"")



</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=17>=IF(D296=2,SUMIF(B$4:B$274,"2",O$4:O$274),"")



</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 height=22>=COUNTIF(B$4:B$274,"2")</TD></TR></TBODY></TABLE>

Anybody know the answer to this ^^ please?

Anybody???

ElmerFud said:

Anybody???

Click to expand...

Well, so far all we know is that you have this data:

I have a range of numbers going down a column. Example: Column (B4:B273) with numbers going down the column 1,1,1,1,2,4,18,1,1,1,18,22,9,14,18,3,1,1 etc. etc.

Click to expand...

So, where is all this other data that corresponds to these numbers?

=SUM(--(FREQUENCY(B4:B273,B4:B273)>0))

Click to expand...

Just an alternation with just ENTER, can use

=SUM(SIGN(FREQUENCY(B:B,B:B)))

Based on T.Valko's method, you can do it without CSE formulas,

A335,

=SUM(SIGN(FREQUENCY(B4:B273,B4:B273)))

D335, copy across,

=IF(COLUMNS($D335:D335)+1>$A335,"",SMALL($B$4:$B$273,1+COUNTIF($B$4:$B$273,"<="&C335)))