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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
834
gvsumich
G
E
  • Question
Adding a custom message.
Replies
1
Views
431
Micron
M
E
  • Question
VBA for Web Scraping help
Replies
0
Views
593
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,215,059
Messages
6,122,913
Members
449,093
Latest member
dbomb1414

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