Need help to create formula to Filter Unique

[JV0710]

Hi

Please help . . .

In the example shown I am trying to use a formula in column D to create a list of unique colours that apear in column A - As can be seen I am getting it right to show the unique colurs but I would the result to be like the info in column F ( Like filter unique ).

The formula I am using in the cells in column D is :

=IF(AND(COUNTIF(A4:$A$25,A3)=0,COUNTIF($D$2:D2,A3)=0),A3,IF(AND(COUNTIF(A4:$A$25,A3)>=0,COUNTIF($D$2:D2,A3)=0),A3,IF(AND(COUNTIF(A4:$A$25,A3)>0,COUNTIF($D$2:D2,A3)>0)," ",IF(AND(COUNTIF(A4:$A$25,A3)=0,COUNTIF($D$2:D2,A3)>0)," "))))

I am getting circular reference when I try to change the formula:

Thanks.

Unique values in a list.xls
	A	B	C	D	E	F
1
2
3	Red			Red		Red
4	green			green		green
5	red					blue
6	red					yellow
7	blue			blue		black
8	blue					white
9	red
10	yellow			yellow
11	black			black
12	white			white
13	red
14
15
Sheet1

 

[Richard Schollar]

Here you go with one possible way:

Book1
	A	B	C	D
1	Red	Red	Red
2	green	green	green
3	red	blue	blue
4	red	yellow	yellow
5	blue	black	black
6	blue	white	white
7	red	#N/A
8	yellow	#N/A
9	black	#N/A
10	white	#N/A
11	red	#N/A
Sheet1

Formula in B2 and below is:

=INDEX($A$1:$A$11,MATCH(0,COUNTIF(B$1:B1,$A$1:$A$11),0))

it is an array formula and so must be entered with Ctrl+Shift+Enter.

Formula in C2 (which is an alternative so that #N/A doesn't appear) is:

=IF(ISNA(INDEX($A$1:$A$11,MATCH(0,COUNTIF(C$1:C1,$A$1:$A$11),0))),"",INDEX($A$1:$A$11,MATCH(0,COUNTIF(C$1:C1,$A$1:$A$11),0)))

likewise an array formula.

Note that the two formulas in colB and colC are entirely independent ie you don't need colB to use colC.

 

[JV0710]

Thank You Richard

This is Great

 

[Puuuuma]

I have a follow-up question to this problem.

First, what I'm trying to do: I create a weekly pivot table that summarizes the quantity sold and the average price per pound, so it is a weighted average. The totals provided by the pivot table are average prices, but not a weighted average, so the number is not actually that usefull. For example, if we sold 20 lbs at $5/lb and 10 lbs at $10/lb it shows the average price of $7.50, but I want it to show $6.67.

Second, my partial solution: We have multiple categories that I would like to subtotal, the categories have varying numbers of items in them and we don't use every category every week. So I am currently adding rows at the end of my data and using sumifs to find the weighted average.

Is it possible to have the list of unique values continue at the bottom of the list it is summarizing, or is there a better way to get the pivot table to give me the information I want? Oh, and I do someother manipulating of the data in a macro, so if you can help me with the VBA code, that would be super great.

Thanks in advance!!