Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Getting frequency of identifier

Nov 22, 2005 6:30AM PST

I have a sheet containing 30 columns of identifiers (lets call them names). I want to have frequency of every name appearing in the columns, All columns are of different lengths.
In other words, is it possible to compare all these 30 columns at once and get an output as name of identifier and its frequency?

Discussion is locked

- Collapse -
Is this in Excel Spreadsheet?
Nov 24, 2005 7:48AM PST

If so, try this function;

=COUNTIF(A1:B39,''Fred'')

This will count the number of times Fred, (or fred), appears in columns A and B, from rows 1 to 39.

It displays the number of times the word appears.

Any help?

Mark

- Collapse -
There are MANY identifiers
Nov 24, 2005 7:57PM PST

Hi mark,

Your formula is perfect if I want to get a fequency of certain 'known' identifier. My columns are long, more than 100 and 30 such columns. May be only 90 appear in all 30 columns but then I have to get list of unique identifiers in all columns and use your formula no. of unique identifiers time.

Still this serves good but any other quick solution (as I have more than 20 such sheets)

- Collapse -
Seems like Excel is the wrong tool here,
Nov 24, 2005 8:06PM PST

unless you want to do some VBA programming.

Kees

- Collapse -
And ...
Nov 24, 2005 8:33PM PST

one of the problems seems to be that the values to be identified (and counted) aren't in one column, but in thirty. Still, you might be inspired by http://www.cpearson.com/excel/duplicat.htm

But, somehow, this seems more like an MS Access type of database application than an Excel one.

Kees

- Collapse -
I agree with Kees
Nov 25, 2005 12:05AM PST

You're tending towards a database management requirement here, not a spreadsheet, and although I still can't quite get a handle on what you are trying to do, I also feel that Access would be the better application.

I'm absolutely no good with Access or any other database.

Kees link is good though, an array formula. I've used them myself but have to keep reading up each time I need one.

But I reckon Access is the way to go.

Mark