|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 10th, 2009, 08:01 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating duplicates
Hi all,
How I calculating duplicates in excel? Example: A1: 1 A2: 2 A3: 2 A4: 3 A5: 3 A6: 3 A7: 4 Answer: How many duplicates: A8: 3 Answer: How many unique values: A9: 4 I meen, what kind of functions I need? This is same function that tool "Remove Duplicates" but I need automatic information in Excel cells. 
|
|
#2
November 10th, 2009, 08:27 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating duplicates
--In cell A8
=MAX(FREQUENCY(A1:A7,A1:A7)) --In cell A9 =SUMPRODUCT((A1:A7"")/COUNTIF(A1:A7,A1:A7&"")) Incase your data do not have any blank entries you can try the below formula =SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7)) If this post helps click Yes --------------- Jacob Skaria "backmara" wrote: Hi all, How I calculating duplicates in excel? Example: A1: 1 A2: 2 A3: 2 A4: 3 A5: 3 A6: 3 A7: 4 Answer: How many duplicates: A8: 3 Answer: How many unique values: A9: 4 I meen, what kind of functions I need? This is same function that tool "Remove Duplicates" but I need automatic information in Excel cells.
|
|
#3
November 10th, 2009, 08:36 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating duplicates
shouldn't be A8=2 ?
On 10 Lis, 09:01, backmara wrote: Hi all, How I calculating duplicates in excel? Example: A1: * * 1 A2: * * 2 A3: * * 2 A4: * * 3 A5: * * 3 A6: * * 3 A7: * * 4 Answer: How many duplicates: * * * A8: 3 Answer: How many unique values: A9: 4 I meen, what kind of functions I need? This is same function that tool "Remove Duplicates" but I need automatic information in Excel cells.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
