Automatically updating a reduced number sorting from low to high

[khank]

In my attached sample worksheet, you’ll see in Column A an list of 44 numbers.

I’m trying to “automate” my calculations because of daily entries…

In Column D I’m using =IF(A1="","",IF(COUNTIF($A$1:A1,A1)=1,ROW(),"")) to eliminate any and all duplicates and in Column E are the results using =IF(ROW(A1)>COUNT($B$1:$B$44),"",INDEX($A$1:$A$44,MATCH(SMALL($D$1:$D$44,ROW(C1)),$D$1:$D$44,0)))…

You’ll notice I’m getting a “#NUM!” at the bottom of Column E…what am I missing in my formula to get that?

And in Column G – I’m eliminating all numbers that fell outside my criteria…and in “Column I” are my results…is there a formula I could use to “automatically” sort from low to high…like in my example in Column K?

[benishiryo]

hi khank. you have lots of formulas in there, so i was too lazy to look at everything. but if you need to improve the template, do share with us what you need to do. there's probably a shorter way. but just to answer your questions:

you #NUM! error is due to your SMALL formula that shows:
SMALL($D$1:$D$44,ROW(C40))
since D1:D44 only has 39 numbers, there's no 40th smallest number

And in column G, you can use the same SMALL formula to help you rank. but is I40 value of 76 to be inside too? saw that you excluded it. i put it in my range anyway:
=SMALL($I$1:$I$44,ROWS($K$1:K1))

[khank]

I understand about there aren't any numbers after the 39th number - what I've failed to understand is how can I eliminate "#NUM" showing in my current formula if my numbers don't go to 44?

Some days I may have 44 - some days maybe 15 numbers - so on and so forth.

[martindwilson]

your counting the wrong column ,you need to count the column you are getting the small from ie d:D
=IF(ROW(A1)>COUNT($D$1:$D$44),"",INDEX($A$1:$A$44,MATCH(SMALL($D$1:$D$44,ROW(C1)),$D$1:$D$44,0)))

[khank]

Thanks to both of you... sometimes you simply can't see the answer even if its staring you straight in your eyes...again thanks.