list of frequency of occurrences of text in a list

a

4

3

a

b

3

4

a

c

3

5

a

d

5

6

b

e

3

7

b

8

b

9

c

10

c

11

c

12

d

13

d

14

d

15

d

16

d

17

e

18

e

19

e

Sheet1

Or you could crreate a pivot table, and do a count on the records instead of a sum.

Book1

A

B

C

D

3

CountofNumber

4

Text

Total

5

a

4

6

b

3

7

c

3

8

d

5

9

e

3

10

GrandTotal

18

Sheet4

Richard

Yogi Anand · Jan 28, 2004

Hi Richard:

In your PivotTable solution, the entries in column A and B can be sorted by Column B in descending order to give from the most to the least per OP's specification.

Yogi Anand · Jan 28, 2004

dnickelson said:
also, the list can be different lengths, so I ran into problems with blank entries being counted in the frequency, which is something I will have to take into account as well, so will have to adjust the length of the search area, or counteract the empty cells somehow.

I can possibly fill this area with some hidden text to ignore or something, but any help there would be good too.

Hi dnickelson:

How do you want to handle the blank entries? -- do you want to disregard the blank entries with in a range of values? -- or do you want to count the blank entries as a type of item?

dnickelson · Jan 28, 2004

The first solution looks closer to what I was going for, though I don't want to modify the original list and I was trying to stay away from so many helper columns, as I have several of these lists on different tabs to deal with. As far as the empty cells I should probably explain what i'm trying to do.

I have a database that lists items that have been transferred to certain groups, say group car, boat, train, plane. each group can have 0 to indefinite tickets, normally 0 to 10 to 30 items. I have a VBA query that pulls the results into a spreadsheet range. What I'm looking for is a side 'legend' that lists the number of items transferred to each group, and having the formula be sorted by default (formula just grabs the next in line on it's own) would be a definite benefit. The problem with the blank cells comes in where some items are not transferred to a group yet, their group field is blank in the database. I was thinking I could either make these blank cells contain a default value, and try rigging the formula to ignore "" in cells, or use some (xldown) command or something to have the range only search down to the used cells (most of the other columns will have something in the same row as the group, even if the group is blank.)

Sorry for the long winded explanation, though if it helps I could explain more.

Yogi Anand · Jan 28, 2004

Hi dnickelson:

Thanks for the explanation. It appears what you are struggling with in regard to the blank cells is more a matter of design/architecture of your worksheet -- in terms of what are the implications in regards to group assignments and so on. It appears that you are considering all of those things and are perhaps struggling with some unknowns and trying to come up with a strategy to avoid potential conflicts in future. The spreadsheet is in Good Hands.

So, let us keep EXCELing!

Aladin Akyurek · Jan 29, 2004

dnickelson said:
Looking to create a list of the number of times each text occurs within a list, as well as sort this list from most to least occurrences.
the list changes often and can contain up to 20 different text entries, but not necessarily any specific one
....

am using an example I found on the board for another issue. This returns the text that occurs the most often easily enough, and with a little modification on the LARGE parameters, I can get the next few in line, but once it gets down to the 8th or 9th largest occurrences, and it starts to be only 3 or fewer entries, it starts flaking out, reporting more entries that really exist, not in a recognizeable order in the COUNTIF array (not recognizable to me anyway.

=INDEX(A$10:A$117,MATCH(LARGE(COUNTIF(A$10:A$117,A$10:A$117),C1),COUNTIF(A$10:A$117,A$10:A$117),0))

Any other ideas? the above was a Ctrl+Shift+Enter formula btw

Some observations:

Combining LARGE with retrieval functions with duplicate values in the range of interest won't work.

In this thread, you expressed your wish not to have any helper columns. But you have to if you also consider the efficiency issue.

In what follows I describe a system of formulas that gives you the desired result -- a sorted list of distinct items along with the frequencies of occurrence.

Let A1:A19 on Sheet1 house the following sample, similar to yours:

{"List";"a";"a";"a";"a";"b";"b";"e";"c";"";"";"d";"e";"e";"e";"c";"d";"f";"g"}

where "" stands for an empty cell or a cell housing a formula returned "".

Insert a new sheet and name it, e.g., Support.

Lets first define BigStr...

Activate Insert|Name|Define.
Enter BigStr in the Names in Workbook box.
Enter the following in the Refers to box:

=REPT("z",255)

Click OK.

Support

dnickelson.xls

A

B

C

D

1

2

3

4

Rank

5

1

6

7

8

9

7

10

11

12

13

14

15

11

16

17

18

19

20

21

22

23

Support

Formulas...

A2:

=MATCH(BigStr,Sheet1!A:A)

A3:

=A2-(CELL("Row",Sheet1!A2)-1)

A5, which is copied down...

=IF((Sheet1!A2<>"")*ISNA(MATCH(Sheet1!A2,Sheet1!$A$1:A1,0)),SUMPRODUCT((Sheet1!A2>OFFSET(Sheet1!$A$2,0,0,$A$3,1))+0)+1,"")

B3:

=COUNT($B$5:INDEX(B:B,A3+ROW(A5)-1))

B5, which is copied down...

=IF(A5<>"",RANK(A5,$A$5:INDEX(A:A,$A$3+ROW($A$5)-1),1)+COUNTIF($A$5:A5,A5)-1,"")

The next post shows Sheet1 along with the result list.

Aladin Akyurek · Jan 29, 2004

Aladin Akyurek said:
The next post shows Sheet1 along with the result list.

Sheet1

dnickelson.xls

A

B

C

D

1

List

SortedList

Freq

2

a

3

a

b

4

a

c

5

a

d

6

b

e

7

b

f

1

8

e

g

1

9

c

10

11

12

d

13

e

14

e

15

e

16

c

17

d

18

f

19

g

Sheet1

Column A houses the sample of interest.

B1 houses a formula for cosmetics...

="Sorted "&Sheet1!A1

B2, which is copied down...

=IF(ROW()-ROW($B$2)+1<=Support!$B$3,INDEX(Sheet1!$A$2:INDEX(Sheet1!A:A,Support!$A$3+ROW(Support!$A$5)-1),MATCH(ROW()-ROW($B$2)+1,Support!$B$5:INDEX(Support!B:B,Support!$A$3+ROW(Support!$A$5)-1),0)),"")

C2, which is copied down.

=IF(B2<>"",COUNTIF(Sheet1!$A$2:INDEX(Sheet1!A:A,Support!$A$2),B2),"")

This system would be fully automatic with code in VBA that would copy the required formulas down as far as needed.

The next post shows a simpler system if the distinct items need not be sorted.

Aladin Akyurek · Jan 29, 2004

Aladin Akyurek said:
...

The next post shows a simpler system if the distinct items need not be sorted.

dnickelson withnosort.xls

A

B

C

D

1

20

10

2

List

0

DistinctList

Freq

3

a

a

5

a

e

6

a

c

7

b

d

8

b

e

9

e

c

10

c

11

12

13

d