Counting unique values with multiple arguments

dgexcellent

New Member
Joined
Nov 5, 2012
Messages
20
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 :
Data1Data2ResultsNo. Of Unique Values in Data 2
f7202983608866336f7202981
f7229429897888929f7229421
f7242513501430051f7242512
f7242513501430051f8075311
f7242519897888929g341351 1
f8075311867781528
g3413511867781528

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi Dave,

Is this what you require?...

Excel Workbook
ABCDEF
1Data1Data2*ResultsNo. Of Unique Values in Data 2*
2f7202983608866336*f7202981*
3f7229429897888929*f7229421*
4f7242513501430051*f7242512*
5f7242513501430051*f8075311*
6f7242519897888929*g3413511*
7f8075311867781528****
8g3413511867781528****
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Data1Data2 ResultsDistinct count
f7202983608866336 f7202981
f7229429897888929 f7229421
f7242513501430051 f7242512
f7242513501430051 f8075311
f7242519897888929 g3413511
f8075311867781528
g3413511867781528

<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:
Rich (BB code):
=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.
 

jamtay317

Well-known Member
Joined
Mar 6, 2013
Messages
769

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,109,411
Messages
5,528,623
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top