Listing cell contents and counting occurrences

mach · Jan 30, 2007

Hello All

I know this is going to be an easy one for all you knowledgeable people on here, and I suspect it's been covered before, so please feel free to just give a link to a previous explanation. However, I've been unable to find it via the search facility on this site or via Google. I suspect that this is down to my description rather than anything else.

What I am after is a formula to list all cell contents that occur (ignoring blanks) within a range and then to count the occurences of each. So in this example (below) the output would report that the cell contents that exist in the range a1:c3 are:-

Apple x 3
Banana x 2
Cherry x 1

-Column A - Column B - Column C
1 --Apple------Banana-----Cherry--
2 --Apple------Banana--
3 --Apple--

ps Tried following the instructions to paste an html clipboard copy of my spreadsheet here, but didn't have alot of joy - I hope the above makes sense

TIA

stanleydgromjr · Jan 30, 2007

If the data is in range A1:C3

try:
=COUNTIF($A$1:$C$3,"=Apple")

Have a great day,
Stan

mach · Jan 30, 2007

Hi

Thanks for your swift reply.

However, perhaps I didn't explain myself well - that seems to be my problem

Your solution requires that I specify "Apple" and it then counts the number of apples.

What I need is a formula or script that will look through and pick out the words of it's own accord, list the different words and then report on the number of times each word that it has found occurs.

I have many spreadsheets with different data in each, and some are very large so it is not practical to identify all the words myself. I need to be able to apply the formula to each, just varying the range that it is applied to in each case and have a result outputted.

I hope this makes sense

Cheers...

stanleydgromjr · Feb 1, 2007

CountOccurances Macro

Matt,

Here you go.

This was an interesting challenge.

The macro needs the Totals sheet to run.

The macro will not run in the 'Totals' sheet - you will get an error message.

The macro will run from within any other sheet that is active, and can only be run once.

The macro will find all the data, copy it to Totals!A:A, do a Data Filter, Advanced Filter, and copy to Totals!B:B unique values. Then the formula will be copied to all the rows in column B where there is data in column A.

Here is a part of your Sheet1 (can not display it all):

Testlisting.xls

A

B

C

D

E

F

G

H

I

1

VLK

WEIR

HLMA

WLF

DGC

CKN

SDX

CEL

CRC

2

VLK

MSI

SCE

CPW

YOU

ASC

SDX

AEX

MTC

3

VLK

RMV

CCT

CPW

VPC

MER

PMK

ISD

4

5

6

WKP

CCT

MPH

RMV

MPH

DGO

CSR

HGI

WLF

7

XPP

HWY

DGO

WKP

CSR

JII

YOU

SDX

8

DEBT

WKP

UVS

DGO

HWY

PMO

CS.

9

VLK

JKX

MTC

WCW

PFC

BYG

GFRD

HLCL

MNE

10

CPW

YOU

DGO

DNX

BUR

CHTR

HGI

WCW

ANTO

11

SDX

HTG

PFC

12

13

PPR

ABU

CHTR

PFC

CCT

RBS

GFRD

SDR

14

SDX

KLR

VLK

15

MPH

VLK

SDX

STHR

YOU

OML

16

MCHL

ABU

KLR

ECWC

JII

17

OML

BUR

LEAD

OPD

CART

STHR

RE.

KCOM

1

After running the macro:<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Testlisting.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 11.0 : OS = Windows XP</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb605117><INPUT onclick='window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);' type=button value="Copy Formula" name=btCb942116></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb202339><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name=sltNb447362><OPTION value==COUNTIF(rngMyRange,A1) selected>B2<OPTION value==COUNTIF(rngMyRange,A2)>B3<OPTION value==COUNTIF(rngMyRange,A3)>B4<OPTION value==COUNTIF(rngMyRange,A4)>B5<OPTION value==COUNTIF(rngMyRange,A5)>B6<OPTION value==COUNTIF(rngMyRange,A6)>B7<OPTION value==COUNTIF(rngMyRange,A7)>B8<OPTION value==COUNTIF(rngMyRange,A8)>B9<OPTION value==COUNTIF(rngMyRange,A9)>B10<OPTION value==COUNTIF(rngMyRange,A10)>B11<OPTION value==COUNTIF(rngMyRange,A11)>B12<OPTION value==COUNTIF(rngMyRange,A12)>B13<OPTION value==COUNTIF(rngMyRange,A13)>B14<OPTION value==COUNTIF(rngMyRange,A14)>B15<OPTION value==COUNTIF(rngMyRange,A15)>B16<OPTION value==COUNTIF(rngMyRange,A16)>B17<OPTION value==COUNTIF(rngMyRange,A17)>B18<OPTION value==COUNTIF(rngMyRange,A18)>B19<OPTION value==COUNTIF(rngMyRange,A19)>B20<OPTION value==COUNTIF(rngMyRange,A20)>B21<OPTION value==COUNTIF(rngMyRange,A21)>B22<OPTION value==COUNTIF(rngMyRange,A22)>B23<OPTION value==COUNTIF(rngMyRange,A23)>B24<OPTION value==COUNTIF(rngMyRange,A24)>B25<OPTION value==COUNTIF(rngMyRange,A25)>B26<OPTION value==COUNTIF(rngMyRange,A26)>B27<OPTION value==COUNTIF(rngMyRange,A27)>B28<OPTION value==COUNTIF(rngMyRange,A28)>B29</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value==COUNTIF(rngMyRange,A1) name=txbFb150492></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">List</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Totals</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">ABU</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">0</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">ADM</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">2</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">AEX</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">1</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">AFHC</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">2</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>6</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">AHT</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">1</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>7</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">ANGL</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">1</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>8</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">ANTO</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">2</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>9</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">ASC</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">1</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>10</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">ASF</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">2</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>11</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">BBAY</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">1</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>12</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">BLT</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">1</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>13</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">BNH</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">1</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>14</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">BP.</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">1</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>15</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">BUR</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">2</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt soli

jindon · Feb 1, 2007

try

Code:

Sub test()
Dim a, e, b(), n As Long
With Activesheet.Range("a1").CurrentRegion.Resize(,3)
     a = .Value
     ReDim b(1 To .Cells.Count, 1 To 3)
End With
With CreateObject("Scripting.Dictionary")
     .CompareMode = vbTextCompare
     For Each e In a
          If Not IsEmpty(e) Then
               If Not .exists(e) Then
                    n = n + 1
                    b(n,1) = e
                    .Add e, n
               End If
               b(.item(e), 3) = b(.item(e), 3) + 1
          End If
     Next
End With
With ActiveSheet.Range("f1")
     .CurrentRegion.ClearContents
     .Resize(n,3).Value = b
End With
End Sub

stanleydgromjr · Feb 1, 2007

Sorry for that, my code did not post.

Sorry for that, my code did not post.

Rich (BB code):

Option Explicit


Sub CountOccurances()
'
' CountOccurances Macro
' Macro created 01/31/2007 by Stanley D. Grom, Jr.
'
'
    Dim nname As String
    Dim lngEndOfDataRow As Long
    Dim lngEndOfDataColumn As Long
    Dim rngMyData As Range
    Dim intTotalsRow As Integer
    Dim c

    If ActiveCell.Worksheet.Name = "Totals" Then
        MsgBox "This macro can not be run from within the 'Totals' sheet - macro terminated!"
        Exit Sub
    End If

    If Range("Totals!A1") = "List" Then
        MsgBox "This macro has been run in this workbook already - macro terminated!"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False

    nname = "rngMyRange"
    GoSub IfRangeNameExistsDeleteRangeName

    Cells(1, 1).Select
    Selection.Offset((LastCell(ActiveSheet).Row - 1), _
        LastCell(ActiveSheet).Column - 1).Range("A1").Select

    lngEndOfDataRow = ActiveCell.Row
    lngEndOfDataColumn = ActiveCell.Column
    Range(Cells(1, 1), Cells(lngEndOfDataRow, lngEndOfDataColumn)).Select
    Selection.Name = "rngMyRange"

    Set rngMyData = Range(Cells(1, 1), Cells(lngEndOfDataRow, lngEndOfDataColumn))

    'Copy all non-blank cells in MyRange to Totals!A:A
    Sheets("Totals").Cells(1, 1) = "List"
    intTotalsRow = 2
    For Each c In rngMyData.Cells
        If c.Value = "" Then
            'do nothing
            intTotalsRow = intTotalsRow + 1
        Else
            Sheets("Totals").Cells(intTotalsRow, 1) = c.Value
            intTotalsRow = intTotalsRow + 1
        End If
    Next
    Cells(1, 1).Select

    'Sort Totals!A:A, ascending
    Sheets("Totals").Select
    Columns("A:A").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

    'Data, Filter, Advanced Filter, Unique A:A to B:B
    intTotalsRow = Range("A65536").End(xlUp).Row
    Columns("A:A").Select
    Range("A1:A" & intTotalsRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
        "B:B"), Unique:=True

    'Remove column A
    Range("A:A").EntireColumn.Delete

    'Add formula to all data rows in Column B
    Cells(1, 2) = "Totals"
    Cells(2, 2).Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(rngMyRange,R[-1]C[-1])"
    intTotalsRow = Range("A65536").End(xlUp).Row
    Cells(2, 2).Copy Destination:=Range(Cells(3, 2), Cells(intTotalsRow, 2))
    Cells(1, 3).Select

    Application.ScreenUpdating = True
    Exit Sub
'--------------------------------------------------------------------
IfRangeNameExistsDeleteRangeName:
' If Range Name Exists Delete Range Name
' Function created 11/06/2002 by Stanley D. Grom, Jr.
'
    Dim n As Name
    For Each n In ActiveWorkbook.Names
        If UCase(n.Name) = UCase(nname) Then
            ActiveWorkbook.Names(nname).Delete
            Return
        End If
    Next n
    Return
End Sub


Function LastCell(ws As Worksheet) As Range
'
' Identifying the Real Last Cell
' Rodney Powell
' Microsoft MVP - Excel
' http://www.beyondtechnology.com/geeks012.shtml
'
    Dim LastRow&, LastCol%
    On Error Resume Next
    With ws
        LastRow& = .Cells.Find(What:="*", _
          SearchDirection:=xlPrevious, _
          SearchOrder:=xlByRows).Row
        LastCol% = .Cells.Find(What:="*", _
          SearchDirection:=xlPrevious, _
          SearchOrder:=xlByColumns).Column
    End With
    Set LastCell = ws.Cells(LastRow&, LastCol%)
End Function

Have a great day,
Stan

stanleydgromjr · Feb 2, 2007

CountOccurances with the corrected formula for column B

Matt,

The line of code to put the formula in column B was wrong.

The correct line should be:
ActiveCell.FormulaR1C1 = "=COUNTIF(rngMyRange,R[0]C[-1])"

The range in sheet 1, A1:X30 contains 178 entries.

After running the macro in your original workbook (see my e-mail), do an auto sum in cell B96, and you should get 178.

Rich (BB code):

Option Explicit


Sub CountOccurances()
'
' CountOccurances Macro
' Macro created 01/31/2007 by Stanley D. Grom, Jr.
'
'
    Dim nname As String
    Dim lngEndOfDataRow As Long
    Dim lngEndOfDataColumn As Long
    Dim rngMyData As Range
    Dim intTotalsRow As Integer
    Dim c

    If ActiveCell.Worksheet.Name = "Totals" Then
        MsgBox "This macro can not be run from within the 'Totals' sheet - macro terminated!"
        Exit Sub
    End If

    If Range("Totals!A1") = "List" Then
        MsgBox "This macro has been run in this workbook already - macro terminated!"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False

    nname = "rngMyRange"
    GoSub IfRangeNameExistsDeleteRangeName

    Cells(1, 1).Select
    Selection.Offset((LastCell(ActiveSheet).Row - 1), _
        LastCell(ActiveSheet).Column - 1).Range("A1").Select

    lngEndOfDataRow = ActiveCell.Row
    lngEndOfDataColumn = ActiveCell.Column
    Range(Cells(1, 1), Cells(lngEndOfDataRow, lngEndOfDataColumn)).Select
    Selection.Name = "rngMyRange"

    Set rngMyData = Range(Cells(1, 1), Cells(lngEndOfDataRow, lngEndOfDataColumn))

    'Copy all non-blank cells in MyRange to Totals!A:A
    Sheets("Totals").Cells(1, 1) = "List"
    intTotalsRow = 2
    For Each c In rngMyData.Cells
        If c.Value = "" Then
            'do nothing
            intTotalsRow = intTotalsRow + 1
        Else
            Sheets("Totals").Cells(intTotalsRow, 1) = c.Value
            intTotalsRow = intTotalsRow + 1
        End If
    Next
    Cells(1, 1).Select

    'Sort Totals!A:A, ascending
    Sheets("Totals").Select
    Columns("A:A").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

    'Data, Filter, Advanced Filter, Unique A:A to B:B
    intTotalsRow = Range("A65536").End(xlUp).Row
    Columns("A:A").Select
    Range("A1:A" & intTotalsRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
        "B:B"), Unique:=True

    'Remove column A
    Range("A:A").EntireColumn.Delete

    'Add formula to all data rows in Column B
    Cells(1, 2) = "Totals"
    Cells(2, 2).Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(rngMyRange,R[0]C[-1])"
    intTotalsRow = Range("A65536").End(xlUp).Row
    Cells(2, 2).Copy Destination:=Range(Cells(3, 2), Cells(intTotalsRow, 2))
    Cells(1, 3).Select

    Application.ScreenUpdating = True
    Exit Sub
'--------------------------------------------------------------------
IfRangeNameExistsDeleteRangeName:
' If Range Name Exists Delete Range Name
' Function created 11/06/2002 by Stanley D. Grom, Jr.
'
    Dim n As Name
    For Each n In ActiveWorkbook.Names
        If UCase(n.Name) = UCase(nname) Then
            ActiveWorkbook.Names(nname).Delete
            Return
        End If
    Next n
    Return
End Sub


Function LastCell(ws As Worksheet) As Range
'
' Identifying the Real Last Cell
' Rodney Powell
' Microsoft MVP - Excel
' http://www.beyondtechnology.com/geeks012.shtml
'
    Dim LastRow&, LastCol%
    On Error Resume Next
    With ws
        LastRow& = .Cells.Find(What:="*", _
          SearchDirection:=xlPrevious, _
          SearchOrder:=xlByRows).Row
        LastCol% = .Cells.Find(What:="*", _
          SearchDirection:=xlPrevious, _
          SearchOrder:=xlByColumns).Column
    End With
    Set LastCell = ws.Cells(LastRow&, LastCol%)
End Function

Enjoy.

Have a great day,
Stan

mach · Feb 3, 2007

Thank you

I really appreciate the time and effort that has gone into this. I had no idea that it would be such a complicated solution.

Thanks very much indeed - what a fantastic board this is.

Cheers

Matt

Listing cell contents and counting occurrences

mach

New Member

Excel Facts

stanleydgromjr

Banned

mach

New Member

stanleydgromjr

Banned

jindon

MrExcel MVP

stanleydgromjr

Banned

stanleydgromjr

Banned

mach

New Member

Similar threads

Forum statistics

Share this page

Listing cell contents and counting occurrences

New Member

Excel Facts

Banned

New Member

Banned

MrExcel MVP

Banned

Banned

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock