Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values in 1 Column based on Date Range in another Column | Excel Worksheet Functions | |||
Count cells w/values in column if the data in column a matches cri | Excel Worksheet Functions | |||
Count entries in one column based on values in another column | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Can I count values in column 1 if criteria in column 2 are met | Excel Worksheet Functions |