frequencies and vba

[Guest]

Hi

I do import data into Excel columns. The data are colors, white black red green blue (5 different colors
A1 to A7 may look as follows

whit
blac
re
blac
whit
gree
blac

What I need is a vba macro that shows me the frequency of each color. The result should be look like this
(B1) white (C1)
(B2) black (C2)
(B3) red (C3)
(B4) green (C4)
(B5) blue (C5)

Who knows the solution for this problem

Thanks a lot and kind regard
Michael

 

[Jake Marx]

Hi Michael,

COUNTIF will do this for you. In C1, enter the following formula and copy
down to C5:

=COUNTIF($A$1:$A$7,B1)

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Tom Ogilvy]

Sub CountColors()
Dim rng As Range, rng1 as Range
Rows(1).Insert Shift:=xlDown
Range("A1:B1").Value = "Colors"
Range("A1:B1").Font.Bold = True
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
rng.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("B1"), Unique:=True
Set rng1 = Range(Cells(2, 2), Cells(2, 2).End(xlDown))
rng1.Offset(0, 1).Formula = "=countif($A:$A,$B2)"
Rows(1).Delete
End Sub

--
Regards,
Tom Ogilvy

Hi,

I do import data into Excel columns. The data are colors, white black red

green blue (5 different colors)

A1 to A7 may look as follows:

white
black
red
black
white
green
black

What I need is a vba macro that shows me the frequency of each color. The

result should be look like this: