returning distinct values from a table

Is this something like what you had in mind?

Are you OK to use helper columns, that can be hidden?

Hi,

Do you want the results concatenated into a single cell, comma-separated (which will require VBA), or are you happy with each result being in its own cell?

Regards

Try this, then. You can always hide row J. Both equations are ARRAY formulas and MUST be set by using CTRL + SHIFT + ENTER and NOT just enter...

You can also do it without a helper column if you really insist (though I see no real reason to avoid Glenn's set-up), if you first go to Name Manager and define:

Name: Range1
Refers to: =$B$2:$H$4

(Or whatever happens to be the range in question.)

Name: Arry1
Refers to: =ROW(INDIRECT("1:"&COLUMNS(Range1)*ROWS(Range1)))

The array formula** is then:

=IFERROR(SMALL(IF(FREQUENCY(IF(Range1<>"",Range1),Range1),N(OFFSET(INDEX(Range1,1,1),INT((Arry1-1)/COLUMNS(Range1)),MOD(Arry1-1,COLUMNS(Range1)),,))),ROWS($1:1)),"")

Copy down until you start to get blanks.

Note that this solution assumes that the values in the range are all numerical, just as in the example you gave.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

I obviously don't "get" what you are after.

I added some helper columns which can be hidden.
The number of formulas done must be greater then the number of values in your range

Maybe I figured out what you want. Does this come close to what you want?

I copied the data down for 1000 rows. See attached. If I chane a number anywhere in the data table, the results take about 1 second to update. Try it...

Here is my attempt. I have filled all 7000 cells and in column all 7000 values are retrieved then in column K the unique values are obtained. Column L then arranges the values in column K in order.

File removed because of difficulty opening.

@Glenn Kennedy

Unless I am missing something, it appears that your workbook is only processing the first 31 rows of the range and column J is retrieving 1000 of the possible 7000 values.

I think that it is just a matter of the ranges not being correct.

If you are open to a VBA solution, please find the attached sheet to see if this works as per your requirement. (Used Glenn's sheet for data)
Click on the Green Button on the sheet to get the desired output in column K.


Edit: I attached the wrong sheet earlier. The correct sheet has been attached now.

Ndm. Oops. You may be right ... if this hasn't been solved by the morning, I'll take another look.

Apologies. I was being lazy using this IFERROR set-up, not to mention the fact that it's terribly inefficient.

Much better is to enter in e.g. J1:

=SUMPRODUCT((Range1<>"")/COUNTIF(Range1,Range1&""))

The array formula** then becomes (I've made a small modification as well):

=IF(ROWS($1:1)>$J$1,"",SMALL(IF(FREQUENCY(IF(Range1<>"",0+Range1),0+Range1),N(OFFSET(INDEX(Range1,1,1),INT((Arry1-1)/COLUMNS(Range1)),MOD(Arry1-1,COLUMNS(Range1)),,))),ROWS($1:1)))

but this time will only calculate the large, resource-heavy array part if the relative row number of the cell in which the formula is placed is less than or equal to the number of expected returns. Otherwise, the IF clause will force this part to remain uncalculated.

(In the previous version, the array was calculating in every cell to which you copied the formula, thus putting unnecessary demand on resource.)

Try this version and let me know if that improves things much.

Regards

see the attached file