Hi friends,

I have 6 columns with 100 names, I would like to count how many the names
appear in the differnt columns and have the results on the same or another
sheet with the number of times the name repeates
eg.
Paul 4
Mike 3
Sandy 2
and so on,

Thanks for your help

There are two ways:

1. Use Countif. As in =Countif(A:A,a1)

2. To get a summary of the names with their counts, the best way is to
create a pivot table. Post back if you need help on this.

Regards,
Fred

"Roshlin" wrote in message
...
Hi friends,

I have 6 columns with 100 names, I would like to count how many the names
appear in the differnt columns and have the results on the same or
another
sheet with the number of times the name repeates
eg.
Paul 4
Mike 3
Sandy 2
and so on,

Thanks for your help

Nit pick!

X...X...X...X

How many times is X duplicated?

3 or 4 ?

--
Biff
Microsoft Excel MVP


"Roshlin" wrote in message
...
Hi friends,

I have 6 columns with 100 names, I would like to count how many the names
appear in the differnt columns and have the results on the same or
another
sheet with the number of times the name repeates
eg.
Paul 4
Mike 3
Sandy 2
and so on,

Thanks for your help

HI FRED & VALKO,
Thanks for your quick response. My knowledge of excel is basic.
So it would be nice if you explain in a little more detail.
This is what I want to achive.
I have a team of 100 members which we rank periodically.
So now I have a table with the top 20 at different time intervals, say week,
month etc. Till here I have it working.
Now I want a table to know how many are regulars, so the good ones will come
more times in the top 20.
To continue with the earlier example, lets say,
Paul 4 , ranked 4 times in the top 20, week, month, quarter and year
Mike 3, ranked thrice, weeK, quarter and year
Sandy 2 , month and year and so on.
I want Excel to go through the week, month etc columns and get me the nunber
of times each player appears in the top 20. and display the results as I
mentioned earlier.
Paul 4
Mike 3
Sandy 2

Thanks

"Fred Smith" wrote:

There are two ways:

1. Use Countif. As in =Countif(A:A,a1)

2. To get a summary of the names with their counts, the best way is to
create a pivot table. Post back if you need help on this.

Regards,
Fred

"Roshlin" wrote in message
...
Hi friends,

I have 6 columns with 100 names, I would like to count how many the names
appear in the differnt columns and have the results on the same or
another
sheet with the number of times the name repeates
eg.
Paul 4
Mike 3
Sandy 2
and so on,

Thanks for your help

Sorry, Roshlin, I'm lost.

You say you *have* a table of the top 20, and that you have a count of top
20 appearances (eg, Paul 4, Mike 3, Sandy 2).

If you already have this table, what more do you want Excel to do? You say
you want it to go through and pick out the top 20, but by your description,
that's already been done.

Please elaborate,
Regards,
Fred.

"Roshlin" wrote in message
...
HI FRED & VALKO,
Thanks for your quick response. My knowledge of excel is basic.
So it would be nice if you explain in a little more detail.
This is what I want to achive.
I have a team of 100 members which we rank periodically.
So now I have a table with the top 20 at different time intervals, say
week,
month etc. Till here I have it working.
Now I want a table to know how many are regulars, so the good ones will
come
more times in the top 20.
To continue with the earlier example, lets say,
Paul 4 , ranked 4 times in the top 20, week, month, quarter and year
Mike 3, ranked thrice, weeK, quarter and year
Sandy 2 , month and year and so on.
I want Excel to go through the week, month etc columns and get me the
nunber
of times each player appears in the top 20. and display the results as I
mentioned earlier.
Paul 4
Mike 3
Sandy 2

Thanks

"Fred Smith" wrote:

There are two ways:

1. Use Countif. As in =Countif(A:A,a1)

2. To get a summary of the names with their counts, the best way is to
create a pivot table. Post back if you need help on this.

Regards,
Fred

"Roshlin" wrote in message
...
Hi friends,

I have 6 columns with 100 names, I would like to count how many the
names
appear in the differnt columns and have the results on the same or
another
sheet with the number of times the name repeates
eg.
Paul 4
Mike 3
Sandy 2
and so on,

Thanks for your help

Hi Fred,
I have a table of the top 20, with 6 columns, week month etc

but need the count as to how many times the names appear in i these columns.
So if paul comes in 4 times in the top 20 columns, I want excel to count
that and give it to me in a table.

"Fred Smith" wrote:

Sorry, Roshlin, I'm lost.

You say you *have* a table of the top 20, and that you have a count of top
20 appearances (eg, Paul 4, Mike 3, Sandy 2).

If you already have this table, what more do you want Excel to do? You say
you want it to go through and pick out the top 20, but by your description,
that's already been done.

Please elaborate,
Regards,
Fred.

"Roshlin" wrote in message
...
HI FRED & VALKO,
Thanks for your quick response. My knowledge of excel is basic.
So it would be nice if you explain in a little more detail.
This is what I want to achive.
I have a team of 100 members which we rank periodically.
So now I have a table with the top 20 at different time intervals, say
week,
month etc. Till here I have it working.
Now I want a table to know how many are regulars, so the good ones will
come
more times in the top 20.
To continue with the earlier example, lets say,
Paul 4 , ranked 4 times in the top 20, week, month, quarter and year
Mike 3, ranked thrice, weeK, quarter and year
Sandy 2 , month and year and so on.
I want Excel to go through the week, month etc columns and get me the
nunber
of times each player appears in the top 20. and display the results as I
mentioned earlier.
Paul 4
Mike 3
Sandy 2

Thanks

"Fred Smith" wrote:

There are two ways:

1. Use Countif. As in =Countif(A:A,a1)

2. To get a summary of the names with their counts, the best way is to
create a pivot table. Post back if you need help on this.

Regards,
Fred

"Roshlin" wrote in message
...
Hi friends,

I have 6 columns with 100 names, I would like to count how many the
names
appear in the differnt columns and have the results on the same or
another
sheet with the number of times the name repeates
eg.
Paul 4
Mike 3
Sandy 2
and so on,

Thanks for your help

Hello Roshlin,

I suggest to use my UDF lfreq:
http://www.sulprobil.com/html/listfreq.html

Press ALT + F11, insert a module, copy my macro text into it, then go
back to your worksheet and you can use lfreq as a function...

Regards,
Bernd

Hi Bernd,

I inserted your module, and tried to use the funtion but get error, please
see where I'm going wrong.

I a new sheet I clicked insert funtion selected lfreq and selected the
tables where the data is, (see below) what exactlt am I supposed to de... I
get error in both the modules,
=lfreq('Top 20'!B8:B27;'Top 20'!E8:E27;'Top 20'!H8:H28;'Top 20'!K8:K27;'Top
20'!N8:N27)

Thanks for your help

"Bernd P" wrote:

Hello Roshlin,

I suggest to use my UDF lfreq:
http://www.sulprobil.com/html/listfreq.html

Press ALT + F11, insert a module, copy my macro text into it, then go
back to your worksheet and you can use lfreq as a function...

Regards,
Bernd

Hello Roshlin,

If you need your non-adjacent areas counted together, select them all,
goto Insert, Name, Define... and define a name for this non-adjacent
range - let us say D.

Then select an adjacent output area of two colums and with as many
rows as you need and array-enter
=lfreq(D)
[enter with CTRL + SHIFT + ENTER, not only with ENTER]

The expected output should appear now.

Regards,
Bernd

Hi Bernd,
I think I'm near, but still get this error #¿NOMBRE? (in all the cells)
"nombre" is name
This is what I have done selected the data, right click and assign name to a
range, put d . Selected 2 colums with 30 rows and in the formula typed:
lfreq(d)
Then [enter with CTRL + SHIFT + ENTER, not only with ENTER]
And I get the above error, could it be that the data is a result of a
formula, these are the top 20 results from another table, if so is there
work around for it.

Thanks for your help
Roshlin

"Bernd P" wrote:

Hello Roshlin,

If you need your non-adjacent areas counted together, select them all,
goto Insert, Name, Define... and define a name for this non-adjacent
range - let us say D.

Then select an adjacent output area of two colums and with as many
rows as you need and array-enter
=lfreq(D)
[enter with CTRL + SHIFT + ENTER, not only with ENTER]

The expected output should appear now.

Regards,
Bernd

Hi again, send me your file then. Regards, Bernd

I've emailed it to you
Thanks

"Bernd P" wrote:

Hi again, send me your file then. Regards, Bernd

Thanks Bernd,
It works like a charm

"Roshlin" wrote:

I've emailed it to you
Thanks

"Bernd P" wrote:

Hi again, send me your file then. Regards, Bernd