Count different values in a column
 

I would like some help on a macro that would count the number of times
value appears in a column as well as seperate all the values in
seperate column.

Example:

Column A might have the values

ABC123
ABC123
ABC123
BCC123
BCC123
CBB123

Thus, column B would have the values:

ABC123
BCC123
CBB123

And column C would have the values

3
2
1

I would effectively be able to determine the total different number o
values in the column as well as the number of times each value appear
in the column.

Using the CountIf formula, I can determine the number of times eac
value appears in a column, but I am unable to determine the number o
different values.

Thanks

--
Kaziglu Be
-----------------------------------------------------------------------
Kaziglu Bey's Profile: http://www.excelforum.com/member.php...fo&userid=3608
View this thread: http://www.excelforum.com/showthread.php?threadid=57379

Count different values in a column
 

=COUNTIF(A:A,B1)

in column C

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Kaziglu Bey"
wrote in message
...

I would like some help on a macro that would count the number of times a
value appears in a column as well as seperate all the values in a
seperate column.

Example:

Column A might have the values

ABC123
ABC123
ABC123
BCC123
BCC123
CBB123

Thus, column B would have the values:

ABC123
BCC123
CBB123

And column C would have the values

3
2
1

I would effectively be able to determine the total different number of
values in the column as well as the number of times each value appears
in the column.

Using the CountIf formula, I can determine the number of times each
value appears in a column, but I am unable to determine the number of
different values.

Thanks.


--
Kaziglu Bey
------------------------------------------------------------------------
Kaziglu Bey's Profile:
http://www.excelforum.com/member.php...o&userid=36086
View this thread: http://www.excelforum.com/showthread...hreadid=573793

Count different values in a column
 

I think I stated it wrong.

In my example, I wish to find a method of only displaying each valu
from column A into column B one time. Thus I would be able to coun
each row and determine the number of different values that appear i
column A.

Basically, I have a 23,000+ row spreadsheet.

One of the columns has numerous values within it; however, many o
those values (like, most) are redundant and appear numerous times.

Using the CountIf formula will tell me how many times each valu
appears in the column, but does nothing to help me determine how man
different values exist overall.

The complete end result I am looking for is to copy the column o
differing values into column A of a new spreadsheet. Then I fil
column B with one of each value from column A (so as to count tota
different values). After which I would have the total number of time
each value appears in column A shown in column C. I then would be abl
to organize the data based on frequency to be able to work with dat
that has the highest frequency first.

Bob Phillips Wrote:
=COUNTIF(A:A,B1)

in column C

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct

--
Kaziglu Be
-----------------------------------------------------------------------
Kaziglu Bey's Profile: http://www.excelforum.com/member.php...fo&userid=3608
View this thread: http://www.excelforum.com/showthread.php?threadid=57379

Count different values in a column
 

On sheet2, A1

=Sheet1!A1

B1

=IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,Sheet1!$A$1:$A$ 20&""),0)),"",
INDEX(IF(ISBLANK(Sheet1!$A$1:$A$20),"",Sheet1!$A$1 :$A$20),MATCH(0,COUNTIF(A$
1:A1,Sheet1!$A$1:$A$20&""),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Copy B1 down as far as you need to go, Use the countif formula in B to get
the counts.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Kaziglu Bey"
wrote in message
...

I think I stated it wrong.

In my example, I wish to find a method of only displaying each value
from column A into column B one time. Thus I would be able to count
each row and determine the number of different values that appear in
column A.

Basically, I have a 23,000+ row spreadsheet.

One of the columns has numerous values within it; however, many of
those values (like, most) are redundant and appear numerous times.

Using the CountIf formula will tell me how many times each value
appears in the column, but does nothing to help me determine how many
different values exist overall.

The complete end result I am looking for is to copy the column of
differing values into column A of a new spreadsheet. Then I fill
column B with one of each value from column A (so as to count total
different values). After which I would have the total number of times
each value appears in column A shown in column C. I then would be able
to organize the data based on frequency to be able to work with data
that has the highest frequency first.

Bob Phillips Wrote:
=COUNTIF(A:A,B1)

in column C

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)


--
Kaziglu Bey
------------------------------------------------------------------------
Kaziglu Bey's Profile:
http://www.excelforum.com/member.php...o&userid=36086
View this thread: http://www.excelforum.com/showthread...hreadid=573793