Count Unique Items in Column

M

mobiius

New Member
Joined
Mar 30, 2011
Messages
37
Hey everyone,
I've been working on a solution to a small problem for a while now and haven't quite found a method that works.

In Sheet1 I have a list of Names in column F (F1:F1372).

What I would like to do is summarize this list in Sheet2.
I would like to show the persons name in column D (no duplicates) and in column E show how many times their name appeared in Sheet1(F)

<TABLE style="WIDTH: 206pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=275 x:str><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7716" width=211><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 158pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=211>Submitter</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64>Count</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Owen </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: red; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 align=right x:num>69</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Jean</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: red; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 align=right x:num>60</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Sasha</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: red; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 align=right x:num>54</TD></TR></TBODY></TABLE>

and so on.

Anyone know of a good way to do this?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
A pivot table will do what you want as long as there are no more than around 8000 unique names (maybe more allowed if using Excel version >2000, not sure)
Use Submitter as row field and count or submitter in data region
 
Upvote 0
I've written code in a workbook (lets call it Report1) as a way to automatically extract information from a separate workbook (TrackingLog).

TrackingLog is updated by another individual on a regular basis, and I don't want to add this functionality to it.

When Report1 is opened it automatically opens the TrackingLog, copies data from it into Sheet1, closes it, then strips away all the unnecessary data, then creates a few different summaries on Sheet2, then saves it into a specific directory using the current date as part of the filename.

I'm not familiar with using Pivot Tables and would rather use VBA if possible.

I have the following code right now count and list, but it is not returning the correct numbers and I'm not sure why. I've looked at this a thousand times and I'm just not seeing the problem:

Code: 
        'Determine Last Row in Column F
        lstA_Rw = Range("F" & Rows.Count).End(xlUp).Row
        
        'Filter the list into Column M
        Range("F1:F" & lstA_Rw).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Range("M1"), Unique:=True
        
        'Determine Last Row in Filtered List
        lstB_Rw = Range("M" & Rows.Count).End(xlUp).Row
        
        'Put Formulas next to Filtered List
        'Range("C1") = "Counts"
        With Range("N1:N" & lstB_Rw)
            .Formula = "=COUNTIF($F$2:$F$" & lstA_Rw & ",M2)"
        End With
        ' Copies and pastes the Submitter and Count columns into Sheet 2.
        Range("M1:N500").Copy
        Sheets("Sheet2").Select
        Range("D2").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        Range("D1").Value = "Submitter"
        Range("E1").Value = "Count"
        Range("E2").Select
        Range("D2:E264").Sort Key1:=Range("E2"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

If I do a COUNTIF on the data in Sheet1 for "Mike", it comes back with 36 (the correct value), but in the summary in Sheet2 it returns 7.
It's also showing other names with a much higher value than they should have.

There are hundreds of unique names in the list so I don't want to write a separate COUNTIF for each one because the person who manages the TrackingLog will need to add/remove names as required.

Am I making this more complicated than it needs to be? I would have throught that it should be pretty straight forward... count the unique items and show how many times they appear.

Help! :eeek:
 
Upvote 0
mobiius,


Sample raw data in Sheet1:


Excel Workbook
F
1Submitter
2Owen
3Jean
4Sasha
5mobiius
6Owen
7Owen
8Jean
9mobiius
10Sasha
11Owen
12Owen
13Sasha
14mobiius
15Sasha
16Owen
17mobiius
18Jean
19Sasha
20
Sheet1





And, in worksheet Sheet2, beginning in cell D2, we have a sorted list of names from Sheet1:


Excel Workbook
DE
1SubmitterCount
2Jean3
3mobiius4
4Owen6
5Sasha5
6
Sheet2





The array formula in Sheet2, D2, confirmed with CTRL + SHIFT + ENTER (not just ENTER), copied down until the cell is blank:

=IF(ROWS($D$2:D2)>SUMPRODUCT((Sheet1!$F$2:$F$2000<>"")/COUNTIF(Sheet1!$F$2:$F$2000,Sheet1!$F$2:$F$2000&"")),"",INDEX(Sheet1!$F$2:$F$2000,MATCH(SMALL(IF(COUNTIF(OFFSET(Sheet1!$F$2,0,0,ROW(Sheet1!$F$2:$F$2000)-ROW(Sheet1!$F$2)+1,1),Sheet1!$F$2:$F$2000)=1,COUNTIF(Sheet1!$F$2:$F$2000,"<"&Sheet1!$F$2:$F$2000)),ROWS($D$2:D2)),IF(COUNTIF(OFFSET(Sheet1!$F$2,0,0,ROW(Sheet1!$F$2:$F$2000)-ROW(Sheet1!$F$2)+1,1),Sheet1!$F$2:$F$2000)=1,COUNTIF(Sheet1!$F$2:$F$2000,"<"&Sheet1!$F$2:$F$2000)),0)))




The formula in Sheet2, E2, copied down:


=COUNTIF(Sheet1!F:F,D2)

 
Upvote 0
Thanks Hiker95, but it didn't work. Maybe I'm missing something...

I pasted the formula you provided into Sheet2, cell D2 then hit Ctrl+Shift+Enter.
I did the same to the other formula you provided for cell E2.

Am I supposed to click and drag the formula down the colums?

I can only guess as to how many unique items will appear because the list will change month to month. Would I just drag the formulas down to, say D/E2000?

I tried to click-drag the formulas down to row 2000, but when I attempt to save the file it hangs and creates an 8 character file in the folder... ?
 
Last edited:
Upvote 0
mobiius,


Sample raw data in worksheet Sheet1:


Excel Workbook
F
1Submitter
2Owen
3Jean
4Sasha
5mobiius
6Owen
7Owen
8Jean
9mobiius
10Sasha
11Owen
12Owen
13Sasha
14mobiius
15Sasha
16Owen
17mobiius
18Jean
19Sasha
20
Sheet1





The macro will clear worksheet Summary columns D and E, and then:


Excel Workbook
DE
1SubmitterCount
2Jean3
3mobiius4
4Owen6
5Sasha5
6
Summary





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub UpdateSummary()
' hiker95, 04/13/2011
' http://www.mrexcel.com/forum/showthread.php?t=543227
Dim w1 As Worksheet, wS As Worksheet
Dim LR As Long, LR2 As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
LR = w1.Cells(Rows.Count, "F").End(xlUp).Row
Set wS = Worksheets("Summary")
wS.Columns("D:E").ClearContents
w1.Columns("F").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wS.Columns("D"), Unique:=True
LR2 = wS.Cells(Rows.Count, "D").End(xlUp).Row
wS.Range("D2:D" & LR2).Sort Key1:=wS.Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
  OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
wS.Range("E1") = "Count"
wS.Range("E2").Formula = "=COUNTIF(Sheet1!F:F,D2)"
wS.Range("E2").AutoFill Destination:=wS.Range("E2:E" & LR2)
wS.Columns("D:E").AutoFit
wS.Activate
Application.ScreenUpdating = True
End Sub


Then run the UpdateSummary macro.
 
Upvote 0
That array formula is no good for 2000 rows of data, too much to calculate.

The formula can't give wrong results on the summary sheet, your code is pasting values so there is no fomula.

Do you have prefilled names in the summary? If so your problem could be different sort order between sheets.
 
Upvote 0
Hiker - The count column shows 4 all the way down and the first name is out of place (next to count?).

<TABLE style="WIDTH: 172pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=229 x:str><COLGROUP><COL style="WIDTH: 141pt; mso-width-source: userset; mso-width-alt: 6875" width=188><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1499" width=41><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 141pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_2682349 class=xl24 height=17 width=188>Mark </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=41>Count</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>Abdul </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>Adam </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>Adon </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>Afroz </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>AGNIESZKA </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>Ahmad </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>ALAETTIN</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>Alan</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>Alan</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 align=right x:num>4</TD></TR></TBODY></TABLE>


Jason, the names are not prefilled. When Workbook1 is opened, both sheet1 and sheet2 are completely blank. I'd like to take the names from sheet1, list them in sheet 2 and next to the names show how many times they appear in sheet 1. I don't know why this is giving me such a hard time lol, I've tried a handful of different methods and none seem to work properly.. :confused:
 
Upvote 0
You nearly had it right with your own code, this should work.

Code: 
'Determine Last Row in Column F
        lstA_Rw = Range("F" & Rows.Count).End(xlUp).Row
        
        'Filter the list into Column M
        Range("F1:F" & lstA_Rw).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Range("M1"), Unique:=True
        
        'Determine Last Row in Filtered List
        lstB_Rw = Range("M" & Rows.Count).End(xlUp).Row
        
        'Put Formulas next to Filtered List
        'Range("C1") = "Counts"
        Range("N1:N" & lstB_Rw).FormulaR1C1 = "=COUNTIF(C6,Rc[-1])"
        
        ' Copies and pastes the Submitter and Count columns into Sheet 2.
        Range("M1:N500").Copy
        Sheets("Sheet2").Select
        Range("D2").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        Range("D1").Value = "Submitter"
        Range("E1").Value = "Count"
        Range("E2").Select
        Range("D2:E264").Sort Key1:=Range("E2"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
127
Kaper
Kaper
E
  • Question Question
VBA code clean up
Replies
9
Views
582
mumps
mumps
D
  • Solved
Count total award value per unique supplier
Replies
4
Views
124
Fluff
Fluff
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A

Forum statistics

Threads
1,224,584
Messages
6,179,693
Members
452,938
Latest member
babeneker

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