Good to hear you got it work for you (because I'm not sure how much more help
I could have offered you).


"Shu of AZ" wrote:

Thanks tons JMB, I got it to work perfectly and just had it display where I
wanted it to. Thanks again, you're always a great help.

"JMB" wrote:

Probably a better way, but my mind is struggling a bit tonight to find
something shorter, so.....

If your data is in A1:E3 enter this *array* formula is entered in H1 (using
Cntrl+Shift+Enter), then copy across until you get #NUM error.

=SMALL(IF(FREQUENCY(SMALL($A$1:$E$3,ROW(INDIRECT(" 1:15"))),SMALL($A$1:$E$3,ROW(INDIRECT("1:15"))))0 ,SMALL($A$1:$E$3,ROW(INDIRECT("1:15"))),""),COLUMN ()-COLUMN($H1)+1)

change range and cell references where needed. If the size of your range
varies (ie has more or less than 15 cells), change INDIRECT("1:15") to
INDIRECT("1:"&COUNT($A$1:$E$1)).

If you'd rather the cells be "" instead of #NUM then try:

=IF(COLUMNS($H1:H1)<=SUMPRODUCT(($A$1:$E$3<"")/COUNTIF($A$1:$E$3,$A$1:$E$3&"")),SMALL(IF(FREQUENC Y(SMALL($A$1:$E$3,ROW(INDIRECT("1:15"))),SMALL($A$ 1:$E$3,ROW(INDIRECT("1:15"))))0,SMALL($A$1:$E$3,R OW(INDIRECT("1:15"))),""),COLUMN()-COLUMN($H$1)+1),"")


If your data can have blank cells in it, then try:
=IF(COLUMNS($H1:H1)<=SUMPRODUCT(($A$1:$E$3<"")/COUNTIF($A$1:$E$3,$A$1:$E$3&"")),SMALL(IF(FREQUENC Y(SMALL($A$1:$E$3,ROW(INDIRECT("1:"&COUNT($A$1:$E$ 3)))),SMALL($A$1:$E$3,ROW(INDIRECT("1:"&COUNT($A$1 :$E$3)))))0,SMALL($A$1:$E$3,ROW(INDIRECT("1:"&COU NT($A$1:$E$3)))),""),COLUMN()-COLUMN($H$1)+1),"")



"Shu of AZ" wrote:

These are numbers in cells 5 columns wide and 3 rows down

7 4 2 11 3
7 4 6 11 3
4 7 9 3 11

In another row of cells, I would like to display the smallest to the largest
numbers in the entire range, i.e. without showing the duplicates

2 3 4 6 7 11

Is there a way to filter this?
Thanks