Count different values in a column

[Kaziglu Bey]

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.

[Bob Phillips]

=COUNTIF(A:A,B1)

in column C

--
HTH

Bob Phillips

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

"Kaziglu Bey" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Kaziglu Bey]

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.

[QUOTE=Bob Phillips]=COUNTIF(A:A,B1)

in column C

--
HTH

Bob Phillips

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

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Kaziglu Bey]

I have uploaded an example of what I am looking for. This might help me as I was rather confused with your last answer.

In column A, I have my data. You will note that some of the data in column A is repeated. In the file I am working with we are looking at roughly 5000 rows, but many of them are duplicates.

Column C represents a shortened list of data found in column A.

Essentially, I need to extract only one of each piece of data from A and put it into column C; thus I will be able to tally the total number of different data found in column A and see it as well.

[tmiley]

Hello,

I have worked out a solution for your problem. [See attached file]

This should work fine for your 20k + records, it just might take a few minutes to process. Press the Sort button to see it work on the list in Sheet1

Hope this helps!

Thomas