# Filtering Problem

#### ewize1

##### Board Regular
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?

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.

Repeating what is already somewhere at this site...
Book3
ABCD
1FruitDistinctD-List0
2Kivi4Kivi1
3Orange4Orange2
4AppleApple3
5ApplePear
6Pear 4
7Orange
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,"")

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.

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.

ewize1 said:
...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.

Replies
5
Views
144
Replies
4
Views
130
Replies
3
Views
204
Replies
2
Views
345
Replies
0
Views
219

1,203,742
Messages
6,057,108
Members
444,905
Latest member
Iamtryingman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back