Counting unique text values across a range

chendox

New Member
Joined
Jun 30, 2010
Messages
4
This one has stumped me and can't find a solution on existing threads. I am trying to count the unique names (text) across a range of cells. Specifically, I have a scheduling table of Rooms (20 rooms) vs. Days (365 days). Whenever someone reserves a room, I put his/her name in the corresponding cell. Since a person can reserve multiple rooms on multiple days, I'm trying to get an accurate read of how many unique people are making reservations. Suggestions on how to approach would be greatly appreciated!

(I'm using Mac excel 2008, but can access a PC if I need to since I know features like Pivot Tables run differently)
 

chendox

New Member
Joined
Jun 30, 2010
Messages
4
Thanks. This seems to allow me to count uniques in a given row. How does it work across a range?
 

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
Thanks. This seems to allow me to count uniques in a given row. How does it work across a range?
Sorry, I may have misunderstood your question (my bad)

Something like this? I think using COUNTIF would be suffice? If not please supply sample data along with expected result(s)

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Joe</TD><TD>Isaac</TD><TD>Isaac</TD><TD>Joe</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Noah</TD><TD>Mary</TD><TD>Noah</TD><TD>Isaac</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>John</TD><TD>John</TD><TD>John</TD><TD>Mary</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Sarah</TD><TD>Noah</TD><TD>Noah</TD><TD>Noah</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Noah</TD><TD>John</TD><TD>James</TD><TD>Gabriella</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Joe</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Noah</TD><TD style="TEXT-ALIGN: right">6</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Sarah</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>etc</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>etc.</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B7</TD><TD>=COUNTIF($A$1:$D$5,A7)</TD></TR><TR><TD>B8</TD><TD>=COUNTIF($A$1:$D$5,A8)</TD></TR><TR><TD>B9</TD><TD>=COUNTIF($A$1:$D$5,A9)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 

chendox

New Member
Joined
Jun 30, 2010
Messages
4
That's essentially the table format and I do want to know the count for each name. However, I first need to identify the unique names (A7:A11 in your example). [I have a table full of names that I didn't populate -- each individual entered their name themselves, so I don't know how many/who is participating to begin with]...
 

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
That's essentially the table format and I do want to know the count for each name. However, I first need to identify the unique names (A7:A11 in your example). [I have a table full of names that I didn't populate -- each individual entered their name themselves, so I don't know how many/who is participating to begin with]...
Ok I now understand, what your looking for is to extract a list of unique names from a range not just a column or row.

See if Aladins set up will assist you, sorry as need to rush of to a meeting. Hopefully this will help or someone else can jump in and help you out.

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

chendox

New Member
Joined
Jun 30, 2010
Messages
4
Hmmm... still don't see how I can use Autofilter, Advanced Filter, or the formulas to work across a range of columns and rows. Is there a way to combine all columns/rows into one column? (I have a 200 x 365 column x row matrix so it's not easy to copy and paste everything together)
 

Forum statistics

Threads
1,081,538
Messages
5,359,388
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top