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]

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.

Thanks Jason (once again), that clears it up nicely.
Let me know if you find any other trick to fix the issue I previously mentioned.
I appreciate your help!

 

[jasonb75]

Looks like you're moved some of the columns around on the sheet, so not sure if this is entirely correct.

I have the "Submitter" list in column C, this column must have a header.

Sub test()
'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("BC1"), 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$2:$C$" & lstA_Rw & ",BC2)"
        End With
End Sub

The code you were using was very close.