Help Finding unique values from a database with a criteria

J

Jaywardhan

New Member
Joined
Aug 1, 2008
Messages
10
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Please help me count unique data from a table having 2 columns one with alphabets. as shown below.<o:p></o:p>
<o:p></o:p>
<TABLE class=MsoNormalTable style="WIDTH: 123pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" cellSpacing=0 cellPadding=0 width=164 border=0 u1:str><COLGROUP><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 45pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=60>Column 1<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 78pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=104>Column 2<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">X<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">A<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">X<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">A<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Y<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">A<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Z<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">B<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Y<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">B<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Z<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">B<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 7"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">X<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">C<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 8"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Y<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">C<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 9; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">X<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">C<o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p></o:p>
So if I have to Count unique for "A"(Column 2) it should Give me 2 as only "X" and "Y" is adjacent to "A" same ways for "B" it should Give me 2 and for "C" the count should be 3 as there are “X”, “Y” and “Z”.<o:p></o:p>
<o:p> </o:p>
Please help.<o:p></o:p>
<o:p> </o:p>
Regards,<o:p></o:p>
Jay.<o:p></o:p>
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Hi,

=SUM(IF(FREQUENCY(IF(B2:B10=C2,MATCH(A2:A10&B2:B10,A2:A10&B2:B10,0)),ROW(A2:A10)-ROW(A2)+1),1))

Confirmed with CTRL+SHIFT+ENTER

HTH
 
Upvote 0
I don't think there's a need to include Column B within the MATCH function. Assuming that D2:D4 contains A, B, and C, try...

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

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

Hope this helps!
 
Upvote 0
Welcome, Jay.

The count for C is three not two - there is X & Y but no Z.

Here is one implementation of a non-formula approach, using SQL. This will be good even with tens of thousands of records.

I assume the data table has headers - "ColA" & "ColB" - and has defined name "source" (not a dynamic named range, BTW). The SQL will be
Code: 
SELECT ColB, Count(*) AS [HowMany]
FROM (SELECT DISTINCT ColA, ColB FROM source)
GROUP BY ColB

Starting from menu data, import external data, new database query then Excel files. OK. Select your Excel file, OK, then the above assumed defined name "source". Continue until there is a choice to edit in MS Query. Then hit the 'SQL' icon and edit the text to be like above. OK for this SQL and then hit the open door icon to return the results to a worksheet. This is now refreshable like a pivot table. It will populate the ColB entries - A, B & C - and the distinct counts - 2, 2 & 2.

Regards, Fazza
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
450
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
453
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
266
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
693
CrashOD
C
E
  • Question
Create mails with table - standard header specific row
Replies
1
Views
432
esben
E

Forum statistics

Threads
1,203,236
Messages
6,054,301
Members
444,715
Latest member
GlitchHawk

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top