Count Unique Items in Column

[mobiius]

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?

 

[mobiius]

You nearly had it right with your own code, this should work.

'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

That's it! It works! Thanks Jason! Man, that was giving me a headache for days.
Hiker thanks for your input as well! Much appreciated.

 

[jasonb75]

For next time

With Range("N1:N" & lstB_Rw)
            .Formula = "=COUNTIF($F$2:$F$" & lstA_Rw & ",M2)"
        End With

Your rows were out of alignment, I didn't see it until I tried the code on a test sheet to check the results.

 

[jasonb75]

Something else I just noticed

Change the copy line to

Range("M2:N500").Copy

Otherwise you copy the header from the original list and you end up with a submitter named submitter in the summary, who will get counted.

 

[mobiius]

I've discovered an issue with the code that JasonB has provided.

Here is the code I'm currently using:

'Determine Last Row in Column C
        lstA_Rw = Range("C" & Rows.Count).End(xlUp).Row
        MsgBox "Last Row in column C is " & lstA_Rw, vbOKOnly
'Filter the list into Column AM
        Range("C1:C" & lstA_Rw).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Range("BC2"), Unique:=True
'Determine Last Row in Filtered List
        lstB_Rw = Range("BC" & Rows.Count).End(xlUp).Row
        MsgBox "Last row in column BC is " & lstB_Rw, vbOKOnly
'Put Formulas next to Filtered List
        With Range("BB2:BB" & lstB_Rw)
            .Formula = "=COUNTIF($C$1:$C$" & lstA_Rw & ",BC2)"
        End With

What happens consistently is that one of the results is repeated twice. The contents of the cells are identical, but yet it appears twice.

I've looked over the code so much that I'm just no longer seeing what the cause could be. Does anyone have any ideas?

 

[jasonb75]

Is there any consistancy in the repeated entry other than being a duplicate?

For example, is it always the first entry in the list, or always the same entry even if you move it to another point in the list?

 

[mobiius]

The first cell in the filtered list appears again consistently in the 3rd cell, and it's also showing the same count. The values are identical.

 

[jasonb75]

Just noticed the range in the code, it looks like you have no column header, and your data starts in row 1, that would be the cause of the problem.

 

[mobiius]

I've tried various things with this code. I've also put in titles in the BB1 and BC1 cells, it still does the same thing.

 

[mobiius]

Can someone please explain to me in plain english, what the "FormulaR1C1" in the following code actually means? I've been having a difficult time with this code. It wants to duplicate one single entry in the spreadsheet, and in another instance, it produces the wrong value. I think I can figure it out, but I'm just having a mental block when it comes to interpretting this code:

Range("N2:N" & lstB_Rw).FormulaR1C1 = "=COUNTIF(C6,Rc[-1])"

Specifically it is the Count If portion that I'm not clear on.
Sorry if I seem dim, I just need some clarification.

Thanks.

 

[jasonb75]

It tells excel that the formula is using R1C1 references not A1 references.

In R1C1 the columns are numeric, so A1 and R1C1 are essentially the same (A1 is Row 1 Column 1)

When using dynamic ranges in VBA it is often easier to use that refernce style to omit the need for row defenition, for example, in your formula

C6 = column 6 (the entier column), which is the same as F:F this is easily written either way.

RC[-1] is a different story, as the column number is enclosed in [] it now becomes relative, not absolute, this counts the number of columns from the cell holding the formula (negative numbers move the column left, positive move right).

In addition to this, the row is refered to as R, there is no row number, this means the formula looks at the same row.

This means the formula is more versatile unchanged, to look at the value in the cell to the left of the formula using A1 reference, if the formula was in B1, then you would use =A1. If it was in G10 then you would use =F10.

Using R1C1 reference, both would use the same formula =RC[-1]

The code line you asked about tells excel which reference style is being used in that formula.

I didn't see your response on the 5th, will have another look at the code to find the problem.