Formula for count unique value in large data with one criteria?

[babaso_tawase]

Hi, I have large data (about 7000 rows) like below format in excel 2007 version. I want to count no of batches (data in number and text format with repeated value) in column 2nd with corresponding name in 1st column. like how many batches corresponding to name ABC and answer should be 3.

I using Arrey formula as below

in first step listing unique list
{=IFERROR(INDEX($B$3:$B$15, MATCH(0, IF($A$1=$A$3:$A$15, COUNTIF($F$1:$F2, $B$3:$B$15), ""), 0)),"-")}

in second step counting
=COUNTIF(F2:F19,"<>*-*")

This step gives correct answer but excel sheet becomes very slow and unresponsive for large data.

please suggest is there any other solution or formula.


A1 cell = ABC

Name	Batch No.
ABC	121(1123)123
AB	12456
AC	125(1256)456
ABC	B1245
ABC	4568
ABC	B1245

 

[Peter_SSs]

You could test this structure for comparative responsiveness with your larger data.

23 12 10.xlsm
	A	B	C	D	E
1	ABC				Count Unique
2	Name	Batch No.			3
3	ABC	121(1123)123		1
4	AB	12456
5	AC	125(1256)456
6	ABC	B1245		1
7	ABC	4568		1
8	ABC	B1245
Unique List

Cell Formulas
Range	Formula
E2	E2	=SUM(D3:D7000)
D3:D8	D3	=IF(A3=A$1,IF(COUNTIFS(A$3:A3,A3,B$3:B3,B3)=1,1,""),"")

 

[babaso_tawase]

Thanks a lot, working fine... Is any formula to list out of unique value of respective name from above data, in different sheet.

i.e. For ABC
like this

Sr.No.	Batch No.
1	121(1123)123
2	B1245
3	4568

 

[Peter_SSs]

Could you use this?

Change the formulas that I used above to the new ones below (& note the zero in cell D2)

babaso_tawase.xlsm
	A	B	C	D	E
1	ABC				Count Unique
2	Name	Batch No.		0	3
3	ABC	121(1123)123		1
4	AB	12456
5	AC	125(1256)456
6	ABC	B1245		2
7	ABC	4568		3
8	ABC	B1245
Count Unique

Cell Formulas
Range	Formula
E2	E2	=MAX(D3:D7000)
D3:D8	D3	=IF(A3=A$1,IF(COUNTIFS(A$3:A3,A3,B$3:B3,B3)=1,LOOKUP(9^9,D$2:D2)+1,""),"")

Now use this in the other worksheet

babaso_tawase.xlsm
	A	B
1	Sr.No.	Batch No.
2	1	121(1123)123
3	2	B1245
4	3	4568
5
6
7
8
9
10
Unique List

Cell Formulas
Range	Formula
A2:A10	A2	=IF(B2="","",N(A1)+1)
B2:B10	B2	=IF(ROWS(B$2:B2)>'Count Unique'!E$2,"",INDEX('Count Unique'!B:B,MATCH(ROWS(B$2:B2),'Count Unique'!D:D,0)))

 

[babaso_tawase]

thanks alot

 

[Peter_SSs]

You're welcome. Glad to help.