Filtering Problem

[ewize1]

Hi,

Here is my problem:

1. Column A contains a list of n types of fruits, for each type there may exist more than 1 instance. n is variable.

2. How to count the numuber of distinct types in the list and store in cell B1?

3. How to extract the list using formula and store into a cell C1, C2, ..., Cn?

 

[SIXTH SENSE]

hi!
use advance filter. then tick unique values and copy to another location and specify the location. you can then put
a countA on this column to count all the unique values.

 

[Aladin Akyurek]

Repeating what is already somewhere at this site...

Book3
	A	B	C	D
1	Fruit	Distinct	D-List	0
2	Kivi	4	Kivi	1
3	Orange	4	Orange	2
4	Apple		Apple	3
5	Apple		Pear
6	Pear			4
7	Orange
8
Sheet1

B2:

=SUMPRODUCT((A2:A7<>"")/COUNTIF(A2:A7,A2:A7))

B3 (using the data from column D)

=LOOKUP(9.99999999999999E+307,D:D)

also gives a distinct count.

C2:

=IF(ROW()-ROW($C$2)+1<=$B$2,INDEX($A$2:$A$7,MATCH(ROW()-ROW($C$2)+1,$D$2:$D$7,0)),"")

D1 must house a 0.

D2:

=IF(ISNA(MATCH(A2,$A$1:A1,0)),LOOKUP(9.99999999999999E+307,$D$1:D1)+1,"")

 

[ewize1]

Sixth Sense,

thanks for your reply. That is one way of getting around the problem. The reason why I want to use a formula instead of advance filter to extract the list is because I want to use the formula in Validation for the user to select.

Aladin,

Thank you too. Could you kindly explain your solution?

What is the role of 9.99999999999999E+307 in the LOOKUP function?

Is there a way to extract the distinct list and put them in one cell? or rather extract the list into a format or anything so that it can be used in Validation.

Thank you.

 

[Aladin Akyurek]

...Could you kindly explain your solution?

What is the role of 9.99999999999999E+307 in the LOOKUP function?

There is a lot on this big number at this site. I thrust you can find the appropriate threads via search.

Is there a way to extract the distinct list and put them in one cell? ...

If the list is not too big, try:

=SUBSTITUTE(TRIM(MCONCAT(UNIQUEVALUES(A2:A7,1)," "))," ",",")

which uses functions available from the morefunc.xll add-in.