Counting unique values with multiple arguments

[dgexcellent]

Hi, I have a 900+ line data set like the below. What I need is a formula that counts the number of unique occurrences in the second column in reference to the first. This value would then be referenced into a second table using the unique values of the first.

So I'm after two formulas :
create a list of the unique values from Data1 (having this auto-sorted numerically would be fantastic but not vital)
count the number of unique values in Data2.

Thanks greatly in advance,

Dave

Data and intended Results look like :

Data1	Data2			Results	No. Of Unique Values in Data 2
f720298	3608866336			f720298	1
f722942	9897888929			f722942	1
f724251	3501430051			f724251	2
f724251	3501430051			f807531	1
f724251	9897888929			g341351	1
f807531	1867781528
g341351	1867781528

<tbody>
</tbody>

 

[Akashwani]

Hi Dave,

Is this what you require?...

Excel Workbook
	A	B	C	D	E	F
1	Data1	Data2	*	Results	No. Of Unique Values in Data 2	*
2	f720298	3608866336	*	f720298	1	*
3	f722942	9897888929	*	f722942	1	*
4	f724251	3501430051	*	f724251	2	*
5	f724251	3501430051	*	f807531	1	*
6	f724251	9897888929	*	g341351	1	*
7	f807531	1867781528	*	*	*	*
8	g341351	1867781528	*	*	*	*
9	*	*	*	*	*	*
Sheet3

The formulas need entering with ctrl shift enter NOT just enter, they can then be copied down as far as you require.
You will obviously have to change the cell references to suit your layout!!

Solutions found here....

Count unique distinct values that meet multiple criteria in excel | Get Digital Help - Microsoft Excel resource

I hope that helps.

Ak

 

[Aladin Akyurek]

Data1	Data2			Results	Distinct count
f720298	3608866336			f720298	1
f722942	9897888929			f722942	1
f724251	3501430051			f724251	2
f724251	3501430051			f807531	1
f724251	9897888929			g341351	1
f807531	1867781528
g341351	1867781528

<colgroup><col style="width: 94pt; mso-width-source: userset; mso-width-alt: 4437;" width="125"> <col style="width: 95pt; mso-width-source: userset; mso-width-alt: 4494;" width="126"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 70pt; mso-width-source: userset; mso-width-alt: 3299;" width="93"> <col style="width: 102pt; mso-width-source: userset; mso-width-alt: 4835;" width="136"> <tbody>
</tbody>

F2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($B$2:$B$8<>"",
  IF($A$2:$A$8=E2,MATCH("~"&$B$2:$B$8,$B$2:$B$8&"",0))),
  ROW($B$2:$B$8)-ROW($B$2)+1),1))

If the target entries are never surrounded with specila meaning chars, the "~"& and &"" bits can be removed from the formula.

 

[dgexcellent]

Worked perfectly, thank you very much

 

[jamtay317]

this is assuming that it is starts in b1 I know that you got it to work, but I thought I would put a formula on here too.
=sumproduct(1/countif(b1:b900,b1:b900)) and that will do it

 

[Aladin Akyurek]

this is assuming that it is starts in b1 I know that you got it to work, but I thought I would put a formula on here too.
=sumproduct(1/countif(b1:b900,b1:b900)) and that will do it

See:
http://www.mrexcel.com/forum/excel-questions/16682-count-unique-entries.html
http://www.mrexcel.com/forum/excel-questions/70835-count-distinct-function.html
http://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html

 

[jamtay317]

See:
http://www.mrexcel.com/forum/excel-questions/16682-count-unique-entries.html
http://www.mrexcel.com/forum/excel-questions/70835-count-distinct-function.html
http://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html

whats wrong with my formula?

 

[Aladin Akyurek]

whats wrong with my formula?

It's not a question of the formula under question being wrong, rather:

1) It does not anwer the OP's question, which involves a conditional distinct count.
2) It will flounder on possible blank/empty cells (although it can be made to understand such situations), hence the first links which attempt to explain how it works and how it is affected in a situation with blank/empty cells.
2) It's not very efficient (hence the link on performance).

 

[jamtay317]

i didnt understand his question

 

[jamtay317]

thank you, I didnt know that it was so bad on performance