Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count quantity of repeated items in a column
I have a list that contains numbers that repeat Example: 900,825,827,900,
765,827,765, 765, 902, 900, etcc... I want to know how many times each number is repeated in that column. What function in Excel 2007 do I use so that I know 900 was repeated 340 times and 825 was repeated 23 times. There are thousands of numbers repeated thousands of times so how do I found out how many times each number is repeated? Can someone explain this step by step because Im new to Excel... Thanks, Susan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count quantity of repeated items in a column
Let put the count only the first time the number is found. Assume the
numbers start in A1. Put this formula in B1 and then copy down Column B =IF(COUNTIF(A$1:A1,A1)=1,COUNTIF($A:$A,A1),"") Countif will count the numbe of times a number appears. **** First Countif ****** COUNTIF(A$1:A1,A1)=1 - Notice the dollar sign in front of the 1. This will fix the first cell of the range when you copy down column B B1 = COUNTIF(A$1:A1,A1)=1 B2 = COUNTIF(A$1:A2,A2)=1 B3 = COUNTIF(A$1:A3,A3)=1 B4 = COUNTIF(A$1:A4,A4)=1 B5 = COUNTIF(A$1:A5,A5)=1 **** Second Countif ****** B1 = COUNTIF($A:$A,A1) B2 = COUNTIF($A:$A,A2) B3 = COUNTIF($A:$A,A3) B4 = COUNTIF($A:$A,A4) B5 = COUNTIF($A:$A,A5) The 2nd Countif counts the number of times the number appears in Column A ($A:$A) where the 1st Countif counts only the number of times the number appears in the rows equal or above where the formula occurs. The test for equal one will only be true the first time a number appears in column A the IF statement works like this =if(Condition,true,false) In the formula the false is not display anything which is "" the two sets of double quotes. So the formula "Susienak" wrote: I have a list that contains numbers that repeat Example: 900,825,827,900, 765,827,765, 765, 902, 900, etcc... I want to know how many times each number is repeated in that column. What function in Excel 2007 do I use so that I know 900 was repeated 340 times and 825 was repeated 23 times. There are thousands of numbers repeated thousands of times so how do I found out how many times each number is repeated? Can someone explain this step by step because Im new to Excel... Thanks, Susan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count quantity of repeated items in a column
Joel... All that does is tell me the amount of times one number repeats. I
literally have a million numbers and probably 40,000 of those are unique so the rest of them are repeats and I want to know how many times each of those unique numbers repeats. I cant put that formula into every box 40,000 times. There isnt a formula that will tell me every unique number and the number of times it repeats? So say column a has 100,000 boxes starting with A2 all the way to A100,001. Of all of those numbers about 400 are unique so what I want to know is how many times each number repeats. There has to be a way to tell me all at once that 900 appears 2,028 times, and 876 appears 560 times, etc... without having to find each of the 400 unique numbers and then putting that formula in for each of the 400 numbers. Should I separate the unique numbers into their own column and then can a formula compare column a to column b and give me totals for each unique formula? "Joel" wrote: Let put the count only the first time the number is found. Assume the numbers start in A1. Put this formula in B1 and then copy down Column B =IF(COUNTIF(A$1:A1,A1)=1,COUNTIF($A:$A,A1),"") Countif will count the numbe of times a number appears. **** First Countif ****** COUNTIF(A$1:A1,A1)=1 - Notice the dollar sign in front of the 1. This will fix the first cell of the range when you copy down column B B1 = COUNTIF(A$1:A1,A1)=1 B2 = COUNTIF(A$1:A2,A2)=1 B3 = COUNTIF(A$1:A3,A3)=1 B4 = COUNTIF(A$1:A4,A4)=1 B5 = COUNTIF(A$1:A5,A5)=1 **** Second Countif ****** B1 = COUNTIF($A:$A,A1) B2 = COUNTIF($A:$A,A2) B3 = COUNTIF($A:$A,A3) B4 = COUNTIF($A:$A,A4) B5 = COUNTIF($A:$A,A5) The 2nd Countif counts the number of times the number appears in Column A ($A:$A) where the 1st Countif counts only the number of times the number appears in the rows equal or above where the formula occurs. The test for equal one will only be true the first time a number appears in column A the IF statement works like this =if(Condition,true,false) In the formula the false is not display anything which is "" the two sets of double quotes. So the formula "Susienak" wrote: I have a list that contains numbers that repeat Example: 900,825,827,900, 765,827,765, 765, 902, 900, etcc... I want to know how many times each number is repeated in that column. What function in Excel 2007 do I use so that I know 900 was repeated 340 times and 825 was repeated 23 times. There are thousands of numbers repeated thousands of times so how do I found out how many times each number is repeated? Can someone explain this step by step because Im new to Excel... Thanks, Susan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count quantity of repeated items in a column
1) First, How can a UNIQUE number repeat?
2) The unique numbers are the ones that have a count of 1 in column B. You can sort column B to find the numbers that appear only once. 3) Here is an easy method of copying the formula down column B a) copy cell B1 - It will be highlighted. b) Select cell A1 c) Press Control and Down - Arrow , this will get you to last cell in Column A d) Select the cell to the right of the highlighted cell. This will be the last cell in Column B e) Press Control and Up-arrow. Now Column B should be highlighted. f) right click mouse and select Paste. "Susienak" wrote: Joel... All that does is tell me the amount of times one number repeats. I literally have a million numbers and probably 40,000 of those are unique so the rest of them are repeats and I want to know how many times each of those unique numbers repeats. I cant put that formula into every box 40,000 times. There isnt a formula that will tell me every unique number and the number of times it repeats? So say column a has 100,000 boxes starting with A2 all the way to A100,001. Of all of those numbers about 400 are unique so what I want to know is how many times each number repeats. There has to be a way to tell me all at once that 900 appears 2,028 times, and 876 appears 560 times, etc... without having to find each of the 400 unique numbers and then putting that formula in for each of the 400 numbers. Should I separate the unique numbers into their own column and then can a formula compare column a to column b and give me totals for each unique formula? "Joel" wrote: Let put the count only the first time the number is found. Assume the numbers start in A1. Put this formula in B1 and then copy down Column B =IF(COUNTIF(A$1:A1,A1)=1,COUNTIF($A:$A,A1),"") Countif will count the numbe of times a number appears. **** First Countif ****** COUNTIF(A$1:A1,A1)=1 - Notice the dollar sign in front of the 1. This will fix the first cell of the range when you copy down column B B1 = COUNTIF(A$1:A1,A1)=1 B2 = COUNTIF(A$1:A2,A2)=1 B3 = COUNTIF(A$1:A3,A3)=1 B4 = COUNTIF(A$1:A4,A4)=1 B5 = COUNTIF(A$1:A5,A5)=1 **** Second Countif ****** B1 = COUNTIF($A:$A,A1) B2 = COUNTIF($A:$A,A2) B3 = COUNTIF($A:$A,A3) B4 = COUNTIF($A:$A,A4) B5 = COUNTIF($A:$A,A5) The 2nd Countif counts the number of times the number appears in Column A ($A:$A) where the 1st Countif counts only the number of times the number appears in the rows equal or above where the formula occurs. The test for equal one will only be true the first time a number appears in column A the IF statement works like this =if(Condition,true,false) In the formula the false is not display anything which is "" the two sets of double quotes. So the formula "Susienak" wrote: I have a list that contains numbers that repeat Example: 900,825,827,900, 765,827,765, 765, 902, 900, etcc... I want to know how many times each number is repeated in that column. What function in Excel 2007 do I use so that I know 900 was repeated 340 times and 825 was repeated 23 times. There are thousands of numbers repeated thousands of times so how do I found out how many times each number is repeated? Can someone explain this step by step because Im new to Excel... Thanks, Susan |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count quantity of repeated items in a column
Joel, A unique number or value is an Excel term... it takes alist and tells
you within the list all of the unique numbers so yes they do repeat. I figured out the answer. I had to use Pivottable and drag and drop the entire list (original column) into both rows and values on the bottom right and poof... that gives you the unique values in one row and the times each unique value repeats in the second row. If anyone out there needs me to explain inmore detail just write to this post and I'll answer what I found. It was a beyond easy answer that took less then 30 seconds... no formulas, no functions, no copying, no separating... easy. "Joel" wrote: 1) First, How can a UNIQUE number repeat? 2) The unique numbers are the ones that have a count of 1 in column B. You can sort column B to find the numbers that appear only once. 3) Here is an easy method of copying the formula down column B a) copy cell B1 - It will be highlighted. b) Select cell A1 c) Press Control and Down - Arrow , this will get you to last cell in Column A d) Select the cell to the right of the highlighted cell. This will be the last cell in Column B e) Press Control and Up-arrow. Now Column B should be highlighted. f) right click mouse and select Paste. "Susienak" wrote: Joel... All that does is tell me the amount of times one number repeats. I literally have a million numbers and probably 40,000 of those are unique so the rest of them are repeats and I want to know how many times each of those unique numbers repeats. I cant put that formula into every box 40,000 times. There isnt a formula that will tell me every unique number and the number of times it repeats? So say column a has 100,000 boxes starting with A2 all the way to A100,001. Of all of those numbers about 400 are unique so what I want to know is how many times each number repeats. There has to be a way to tell me all at once that 900 appears 2,028 times, and 876 appears 560 times, etc... without having to find each of the 400 unique numbers and then putting that formula in for each of the 400 numbers. Should I separate the unique numbers into their own column and then can a formula compare column a to column b and give me totals for each unique formula? "Joel" wrote: Let put the count only the first time the number is found. Assume the numbers start in A1. Put this formula in B1 and then copy down Column B =IF(COUNTIF(A$1:A1,A1)=1,COUNTIF($A:$A,A1),"") Countif will count the numbe of times a number appears. **** First Countif ****** COUNTIF(A$1:A1,A1)=1 - Notice the dollar sign in front of the 1. This will fix the first cell of the range when you copy down column B B1 = COUNTIF(A$1:A1,A1)=1 B2 = COUNTIF(A$1:A2,A2)=1 B3 = COUNTIF(A$1:A3,A3)=1 B4 = COUNTIF(A$1:A4,A4)=1 B5 = COUNTIF(A$1:A5,A5)=1 **** Second Countif ****** B1 = COUNTIF($A:$A,A1) B2 = COUNTIF($A:$A,A2) B3 = COUNTIF($A:$A,A3) B4 = COUNTIF($A:$A,A4) B5 = COUNTIF($A:$A,A5) The 2nd Countif counts the number of times the number appears in Column A ($A:$A) where the 1st Countif counts only the number of times the number appears in the rows equal or above where the formula occurs. The test for equal one will only be true the first time a number appears in column A the IF statement works like this =if(Condition,true,false) In the formula the false is not display anything which is "" the two sets of double quotes. So the formula "Susienak" wrote: I have a list that contains numbers that repeat Example: 900,825,827,900, 765,827,765, 765, 902, 900, etcc... I want to know how many times each number is repeated in that column. What function in Excel 2007 do I use so that I know 900 was repeated 340 times and 825 was repeated 23 times. There are thousands of numbers repeated thousands of times so how do I found out how many times each number is repeated? Can someone explain this step by step because Im new to Excel... Thanks, Susan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count like items (words) in a column? | New Users to Excel | |||
SUMPRODUCT to count items with duplicates where another column contains two defined items | Excel Worksheet Functions | |||
How do I sort - and count - items in a column? | Excel Discussion (Misc queries) | |||
How do I count the items in one column if another column is blank | Excel Worksheet Functions | |||
Count number of items in one column that have a value in another? | Excel Worksheet Functions |