Formula for multiple distinct values & numbers of those values?

[ArPharazon]

I may be explaining this badly, which is probably why I haven't been able to come up with anything on Google for this.

I run a report every day for my job that I'd like to use a formula on that shows each distinct text value from a column and then how many instances of each there are.

At least I hope it can be done with a formula

Below is a generic example. What I'd like to end up seeing is: Name1 | 10 / Name2 | 6 / Name3 | 4 and filter out #N/A if possible. The values in this column are all the result of a vlookup.

 

[Eric W]

Something like this could work:

Book2
	A	B	C	D
1	Data		Name	Count
2	Name1		Name1	9
3	Name2		Name2	6
4	#N/A		Name3	4
5	Name1
6	Name2
7	#N/A
8	Name3
9	Name2
10	Name1
11	#N/A
12	Name1
13	Name2
14	Name1
15	#N/A
16	Name2
17	Name3
18	Name1
19	#N/A
20	Name3
21	Name1
22	#N/A
23	#N/A
24	Name1
25	Name1
26	Name2
27	Name3
28	#N/A
29
30
Sheet7

Cell Formulas
Range	Formula
C2:C10	C2	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$30)/(COUNTIF($C$1:$C1,$A$2:$A$30)=0)/NOT(ISNA($A$2:$A$30))/($A$2:$A$30<>""),1)),"")
D2:D10	D2	=IF(C2="","",COUNTIF(A:A,C2))
A4,A28,A22:A23,A19,A15,A11,A7	A4	=NA()

You should update your member profile to show which version of Excel you're using. Newer versions have new functions that might make this easier.

 

[ArPharazon]

2007 on my personal machine and 2013 on my work laptop.

 

[Eric W]

The formulas in post #2 should work on your work laptop. IFERROR and AGGREGATE were introduced in Excel 2010. For your home pc, try:

Book1
	A	B	C	D	E
1	Data		Name	Count	How many unique names
2	Name1		Name1	9	3
3	Name2		Name2	6
4	#N/A		Name3	4
5	Name1
6	Name2
7	#N/A
8	Name3
9	Name2
10	Name1
11	#N/A
12	Name1
13	Name2
14	Name1
15	#N/A
16	Name2
17	Name3
18	Name1
19	#N/A
20	Name3
21	Name1
22	#N/A
23	#N/A
24	Name1
25	Name1
26	Name2
27	Name3
28	#N/A
29
30
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=IF(ROWS($C$2:$C2)>$E$2,"",INDEX($A:$A,SMALL(IF(ISTEXT($A$2:$A$30),IF(MATCH($A$2:$A$30,$A$2:$A$30,0)=ROW($A$2:$A$30)-ROW($A$2)+1,ROW($A$2:$A$30))),ROWS($C$2:$C2))))
D2:D10	D2	=IF(C2="","",COUNTIF(A:A,C2))
E2	E2	=SUM(SIGN(FREQUENCY(IF(ISTEXT($A$2:$A$30),MATCH($A$2:$A$30,$A$2:$A$30,0)),ROW($A$2:$A$30)-ROW($A$2)+1)))
A4,A28,A22:A23,A19,A15,A11,A7	A4	=NA()
Press CTRL+SHIFT+ENTER to enter array formulas.

This should work for your work laptop too. In the latest Excel 365, there's a new function called UNIQUE that would make this even easier.

 

[ArPharazon]

I will try both in the morning. Thanks, and I'll let you know.

 

[ArPharazon]

I used your first set of formulas, in my case I located the results below everything else. I replaced ranges with what I think are the correct values, I'm dealing with Column K.

K412 contains the first actual name in Column K (all the way up in K3), L412 should be "2". I'm not sure how to get all names from Column K to display. If I drag K412, the same name gets repeated.

I'm sure it's something simple I'm missing.

 

[Peter_SSs]

2007 on my personal machine and 2013 on my work laptop.

If you update your Account details (click your user name at the top right of the forum) then helpers will always know what Excel version(s) & platform(s) you are using instead of having to ask each time or you having to remember to include that detail. (Don’t forget to scroll down & ‘Save’)

 

[ArPharazon]

Done, but no mas

 

[Peter_SSs]

Done

Thanks.

 

[Eric W]

Here's the difference:

=IFERROR(INDEX($K:$K,AGGREGATE(15,6,ROW($K$2:$K$409)/(COUNTIF($K$411:$K411,$K$2:$K$409)=0)/NOT(ISNA($K$2:$K$409))/($K$2:$K$409<>""),1)),"")

The range in red should refer to the cell immediately above the cell the formula is placed in (K412).