Formula Question - 2nd Inquiry

S

Shu of AZ

{=SMALL(IF(FREQUENCY(SMALL($E$6:$F$10,ROW(INDIRECT("1:6"))),SMALL($E$6:$F$10,ROW(INDIRECT("1:6"))))>0,SMALL($E$6:$F$10,ROW(INDIRECT("1:6"))),""),COLUMN()-COLUMN($AC$60)+1)}
The above formula displays 6 cell's values and eliminates any duplicates.
 
L

Lori

I think you may be able to simplify things a little.
Assuming "Array" is (E60:F10,G22), enter:

AC60: =MIN(Array)
AD60: =SMALL(Array,COUNT(Array)-RANK(AC60,Array)+2)

(or whatever your range to fill is) and fill right for the unique values.
["Array" could also refer to multiple sheets e.g. Sheet1:Sheet3!E:G]
 
S

Shu of AZ

=SMALL(E6:F11,D18,COUNT(E6:F11,D18)-RANK(AC60,E6:F11,D18)+2) results in a
too many arguments ( this is the correct array Im using )

Placed in AD60
 
S

Shu of AZ

Made attempts with this with no positive outcome. I do not need a report, I
need to populate cells. I may be not understanding this completely..
 
L

Lori

Hi Shu, you need the extra brackets for multiple ranges:

=SMALL((E6:F11,D18),COUNT(E6:F11,D18)-RANK(AC60,(E6:F11,D18))+2)

but my preference is to use a named range instead. (You can do this by
selecting the range and typing "Array" in the Name Box which is just to the
left of the Formula Bar)

Shu of AZ said:
=SMALL(E6:F11,D18,COUNT(E6:F11,D18)-RANK(AC60,E6:F11,D18)+2) results in a
too many arguments ( this is the correct array Im using )

Placed in AD60

Lori said:
I think you may be able to simplify things a little.
Assuming "Array" is (E60:F10,G22), enter:

AC60: =MIN(Array)
AD60: =SMALL(Array,COUNT(Array)-RANK(AC60,Array)+2)

(or whatever your range to fill is) and fill right for the unique values.
["Array" could also refer to multiple sheets e.g. Sheet1:Sheet3!E:G]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top