Contents of a column/table

**Beest** · 01-06-2009, 12:10 PM

Hi All,

I want to do something seemingly easy:

Suppose I have this data

Column

RowAa
RowAa
RowAa
RowAa
RowAa
RowAb
RowAb

And I want to get to this:

RowAa 5 (as it occurred 5 times)
RowAb 2 (idem, 2 times)

I could do countif(A1:A7, “=RowAa) (supposing the data is are located in A1:A7)

However, I don’t want to assign all countif’s manually to the values I want to count.

As a somewhat acceptable solution I tried:
countif(A1:A7, “=A1”) which mysteriously comes up with 0.

All I want to do is have an overview what’s in the table. That’s: what values and how often.

Anybody have a way of doing this?

Cheers,

Dennis

**Richard Schollar** · 01-06-2009, 12:32 PM

Hi Dennis

Use a Pivot Table to do this. If you haven't used them before here's a useful guide:

http://peltiertech.com/Excel/Pivots/pivottables.htm

Regarding your problem formula, that would need to be:

=COUNTIF(A1:A7,"=" & A1)

or more simply:

=COUNTIF(A1:A7,A1)

Richard

**MickG** · 01-06-2009, 12:55 PM

Hi, This will give you the desired result in column "B"

Dim Data, Uni As Long, Multi As Long, c As Integer, P As Long, Tot()
Data = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
P = 0
For Uni = 1 To UBound(Data)

For Multi = Uni + 1 To UBound(Data)
    If Data(Uni, 1) <> "" And Data(Uni, 1) = Data(Multi, 1) Then
        c = c + 1
        Data(Multi, 1) = ""
    End If
Next Multi

If c > 0 Then
    ReDim Preserve Tot(P)
    Tot(P) = Data(Uni, 1) & "  " & c + 1
    P = P + 1
    c = 0
End If

Next Uni
Range("B1").Resize(P).Value = Application.Transpose(Tot)

Regards Mick

Contents of a column/table

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Contents of a column/table

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions