Formula for Counting Unique Entries

marshalgaddis

New Member
Joined
Jul 27, 2010
Messages
44
Hi all,

Does anybody know a formula that reads one cell, finds that value in a table and counts the number of unique entries in a different column of the table. Ie, my table:

<TABLE style="WIDTH: 290pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=387><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=84>Sales Rep</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 129pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=172>Customer</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=131>Location</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>John</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Burger Prince</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Austin</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>John</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Burger Prince</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">DC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>John</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Mc Ds</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">DC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Bob</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Mc Ds</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">NYC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Bob</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Sandwich of the West</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">NYC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Bob</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Sandwich of the West</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Miami</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Ashley</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Horriblue Bell Ice Cream</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Salt Lake</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Ashley</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Horriblue Bell Ice Cream</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">White Fish</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Ashley</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Yogurt Clot</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">White fish</TD></TR></TBODY></TABLE>

And I have this framework:
<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=84>Sales Rep</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 129pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=172>Locations</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>John</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">[Formula]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Bob
Ashley


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>



</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR></TBODY></TABLE>

And I want to be able to enter a formula and drag down to count the number of locations.

Thanks!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Stu Dapples

Active Member
Joined
Jan 12, 2009
Messages
252
Someone gave me this little beauty recently:

=IF(ROWS(A$6:A6)<=$D$3,INDEX('Operator Data'!B$2:B$30000,SMALL(IF('Operator Data'!$B$2:$B$30000=$A$3,ROW('Operator Data'!$B$2:$B$30000)-ROW('Operator Data'!$B$2)+1),ROWS(A$6:A6))),"")

It is edited to suit my needs but effectively the B$2:B$300000 is the range of your table you are looking into, the A$6:A6 is the the cell in which you type the name you are looking for....

THe $D$3 is a count of how many records it found, this then sets the range for the search and stops it returning #Num errors!

When you have entered it, you need to enter with ctrl and enter which will insert curly braces {} around it, dont ask me why, I dont know, I do know however that if you dont, it wont work!!

Good luck!
 
Last edited:

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Just to be clear, is it that John has 2 Loactions - Austin and DC?

Matty
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Assuming my previous post was correct, try:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 58px"><COL style="WIDTH: 143px"><COL style="WIDTH: 66px"><COL style="WIDTH: 19px"><COL style="WIDTH: 58px"><COL style="WIDTH: 79px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-SIZE: 10pt; FONT-WEIGHT: bold">Sales Rep</TD><TD style="FONT-SIZE: 10pt; FONT-WEIGHT: bold">Customer</TD><TD style="FONT-SIZE: 10pt; FONT-WEIGHT: bold">Location</TD><TD></TD><TD style="FONT-SIZE: 10pt; FONT-WEIGHT: bold">Sales Rep</TD><TD style="FONT-SIZE: 10pt; FONT-WEIGHT: bold">Locations</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-SIZE: 10pt">John</TD><TD style="FONT-SIZE: 10pt">Burger Prince</TD><TD style="FONT-SIZE: 10pt">Austin</TD><TD></TD><TD style="FONT-SIZE: 10pt">John</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">2</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 10pt">John</TD><TD style="FONT-SIZE: 10pt">Burger Prince</TD><TD style="FONT-SIZE: 10pt">DC</TD><TD></TD><TD style="FONT-SIZE: 10pt">Bob</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">2</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 10pt">John</TD><TD style="FONT-SIZE: 10pt">Mc Ds</TD><TD style="FONT-SIZE: 10pt">DC</TD><TD></TD><TD style="FONT-SIZE: 10pt">Ashley</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">2</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 10pt">Bob</TD><TD style="FONT-SIZE: 10pt">Mc Ds</TD><TD style="FONT-SIZE: 10pt">NYC</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 10pt">Bob</TD><TD style="FONT-SIZE: 10pt">Sandwich of the West</TD><TD style="FONT-SIZE: 10pt">NYC</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 10pt">Bob</TD><TD style="FONT-SIZE: 10pt">Sandwich of the West</TD><TD style="FONT-SIZE: 10pt">Miami</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-SIZE: 10pt">Ashley</TD><TD style="FONT-SIZE: 10pt">Horriblue Bell Ice Cream</TD><TD style="FONT-SIZE: 10pt">Salt Lake</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-SIZE: 10pt">Ashley</TD><TD style="FONT-SIZE: 10pt">Horriblue Bell Ice Cream</TD><TD style="FONT-SIZE: 10pt">White Fish</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-SIZE: 10pt">Ashley</TD><TD style="FONT-SIZE: 10pt">Yogurt Clot</TD><TD style="FONT-SIZE: 10pt">White fish</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

Formula in F2 is:

Code:
=SUM(IF(FREQUENCY(IF(A$2:A$10=E2,MATCH(C$2:C$10,C$2:C$10,0)),ROW(C$2:C$10)-ROW(C$2)+1),1))

Which needs committing with Ctrl+Shift+Enter and can then be copied down...

Matty
 

marshalgaddis

New Member
Joined
Jul 27, 2010
Messages
44

ADVERTISEMENT

Interesting. Where does this take into account the multiple column lookup? Ie, A6:A6 is my input cell and B2:B30000 are the places where I would find the instances of same value as A6:A6 (right?). But after I find those instances, I want to pop over two rows and count the number of THOSE unique values...
 

marshalgaddis

New Member
Joined
Jul 27, 2010
Messages
44

ADVERTISEMENT

Any idea why my sheet would crash when I try to run the formula using the entire column instead of just rows 1-10?
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
I wouldn't advise using such a formula against such a large number of Rows - it would make for a very slow Workbook.

How many Rows are in your real data set? Could you not simply expand the ranges of what I posted to the last Row of your data set?

Matty
 

marshalgaddis

New Member
Joined
Jul 27, 2010
Messages
44
There are many rows (hundreds). And the number of rows will change often. This formula is an integral piece of a large macro/ vba automation project that I've constructed.

Can you think of VBA code that could read intelligently the number of rows of data in the table and copy the formula into the appropriate number of rows in column F?

The number of sales reps will also change often.
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
There are many rows (hundreds). And the number of rows will change often.

A dynamic named range could be employed. Have a look at Aladin's suggestion here (post #6):

http://www.mrexcel.com/forum/showthread.php?t=485763

The number of sales reps will also change often.

Try this:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 58px"><COL style="WIDTH: 143px"><COL style="WIDTH: 66px"><COL style="WIDTH: 19px"><COL style="WIDTH: 99px"><COL style="WIDTH: 79px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-SIZE: 10pt; FONT-WEIGHT: bold">Sales Rep</TD><TD style="FONT-SIZE: 10pt; FONT-WEIGHT: bold">Customer</TD><TD style="FONT-SIZE: 10pt; FONT-WEIGHT: bold">Location</TD><TD></TD><TD style="FONT-SIZE: 10pt; FONT-WEIGHT: bold">Sales Rep Count:</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">3</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-SIZE: 10pt">John</TD><TD style="FONT-SIZE: 10pt">Burger Prince</TD><TD style="FONT-SIZE: 10pt">Austin</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 10pt">John</TD><TD style="FONT-SIZE: 10pt">Burger Prince</TD><TD style="FONT-SIZE: 10pt">DC</TD><TD></TD><TD style="FONT-SIZE: 10pt; FONT-WEIGHT: bold">Sales Rep</TD><TD style="FONT-SIZE: 10pt; FONT-WEIGHT: bold">Locations</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 10pt">John</TD><TD style="FONT-SIZE: 10pt">Mc Ds</TD><TD style="FONT-SIZE: 10pt">DC</TD><TD></TD><TD style="FONT-SIZE: 10pt">John</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">2</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 10pt">Bob</TD><TD style="FONT-SIZE: 10pt">Mc Ds</TD><TD style="FONT-SIZE: 10pt">NYC</TD><TD></TD><TD style="FONT-SIZE: 10pt">Bob</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">2</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 10pt">Bob</TD><TD style="FONT-SIZE: 10pt">Sandwich of the West</TD><TD style="FONT-SIZE: 10pt">NYC</TD><TD></TD><TD style="FONT-SIZE: 10pt">Ashley</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">2</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 10pt">Bob</TD><TD style="FONT-SIZE: 10pt">Sandwich of the West</TD><TD style="FONT-SIZE: 10pt">Miami</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-SIZE: 10pt">Ashley</TD><TD style="FONT-SIZE: 10pt">Horriblue Bell Ice Cream</TD><TD style="FONT-SIZE: 10pt">Salt Lake</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-SIZE: 10pt">Ashley</TD><TD style="FONT-SIZE: 10pt">Horriblue Bell Ice Cream</TD><TD style="FONT-SIZE: 10pt">White Fish</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-SIZE: 10pt">Ashley</TD><TD style="FONT-SIZE: 10pt">Yogurt Clot</TD><TD style="FONT-SIZE: 10pt">White fish</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

Array formula in F1 is:

Code:
=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0)),ROW(A2:A10)-ROW(A2)+1),1))

Array formula in E4 is:

Code:
=IF(ROWS(E$4:E4)<=F$1,INDEX(A$2:A$10,MATCH(0,COUNTIF(E$3:E3,A$2:A$10),0)),"")

Array formula in F4 becomes:

Code:
=IF(E4="","",SUM(IF(FREQUENCY(IF(A$2:A$10=E4,MATCH(C$2:C$10,C$2:C$10,0)),ROW(C$2:C$10)-ROW(C$2)+1),1)))

Now you can have the formulas in E4 and F4 copied down more Rows than you are ever likely to have Sales Reps to ensure all data is shown.

Note that these formula will all need making "dynamic" assuming you employ a dynamic named range set up.

Matty
 

Watch MrExcel Video

Forum statistics

Threads
1,122,967
Messages
5,599,092
Members
414,285
Latest member
excela2z

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
Top