Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting unique values
I am trying to count the number of unique values in my data. Here is an
example: 589450 589451 589452 589453 589454 589455 589455 589455 589455 Ideally I want to know that the count for the first 5 values will be 1, but also the count of 4 for the last four values, such as: 589450 1 589451 1 589452 1 589453 1 589454 1 589455 4 I have tried VLOOKUP, advanced filters, and COUNTIF, but I get the following result: 589450 1 589451 1 589452 1 589453 1 589454 1 589455 4 589455 4 589455 4 589455 4 I have over 19,000 values that I need to count in this way so duplicates as seen above in the last example are not desirable. Any help would be greatly appreciated, and many thanks in advance for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting unique values
I put your data in column range A1:A9. Then put this formula in cell B1 and
copy down column b. =IF(COUNTIF(A$1:A1,A1)=1,1,0) Notice the dollar sign in front of the 1. The code counts the number of times the data appears from the bottom of the table to the current row and only puts a 1 in column B the first time the number in column A occurs. You can then sum column b to get your unique values. "Dean" wrote: I am trying to count the number of unique values in my data. Here is an example: 589450 589451 589452 589453 589454 589455 589455 589455 589455 Ideally I want to know that the count for the first 5 values will be 1, but also the count of 4 for the last four values, such as: 589450 1 589451 1 589452 1 589453 1 589454 1 589455 4 I have tried VLOOKUP, advanced filters, and COUNTIF, but I get the following result: 589450 1 589451 1 589452 1 589453 1 589454 1 589455 4 589455 4 589455 4 589455 4 I have over 19,000 values that I need to count in this way so duplicates as seen above in the last example are not desirable. Any help would be greatly appreciated, and many thanks in advance for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting unique values
Użytkownik "Dean" napisał w wiadomo¶ci ... I am trying to count the number of unique values in my data. Here is an example: 589450 589451 589452 589453 589454 589455 589455 589455 589455 Ideally I want to know that the count for the first 5 values will be 1, but also the count of 4 for the last four values, such as: 589450 1 589451 1 589452 1 589453 1 589454 1 589455 4 Have you tried a Pivot Table ? In your data column insert one row above the data to label the column (ie "numbers") and then mark entire column and choose "Data-Pivot Table and PivotChart Report" from Excel menu. Next in wizard drag "numbers" into "Drop Row Fields Here" area and additionally into "Drop Data Items Here" area. You will have "589450" etc numbers in both areas. Then do right mouse click on "Drop Data Items Here" area and choose "Field settings" (or something like that - I use Polish version of Excel and I don't know how it is named in English version - it is ninth option from above in right-click menu. Finally in opened window choose "counter" (or sth with similiar name) in listbox and choose OK. -- ============================== Janusz Pawlinka |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique values | Excel Discussion (Misc queries) | |||
Counting Unique Values | Excel Discussion (Misc queries) | |||
Counting unique values | Excel Discussion (Misc queries) | |||
Counting unique values | Excel Worksheet Functions | |||
Counting Unique Values | Excel Worksheet Functions |