Filtering Duplicate Data to obtain Unique record
This may an issue of the definition of unique records
do you mean you want to find the number of records assigned to an individual
with an entry in another column
try
=Sumproduct(--(Name range =Name),--(Item range = item))
I normally think of unique records as not having a duplicate
If you have another meaning please let me know.
"EricB" wrote:
Formula is killing EXCEL on 25.000+ records. Futhermore, I am trying to
extract data for certain 'sales' people, the formula does not allow me to
define what I want to count. I need to input a 'Sales' persons name and get
the unique records for the individual.
Any other advice?
"bj" wrote:
try =sumproduct(1/countif(A$2:A$8628,A2:A8628))
to get the number of unique values
also you can use advanced filter unique to get a list of the unique values.
"EricB" wrote:
When using: =COUNTIF(A2:A8628,A2) and select all 1s from the Filter to
obtain the Unique records my count on these records was 8193.
In looking for a more automated way of counting unique records I found the following formula: =SUM(N(FREQUENCY(IF(J$2:J$50000=L5,A$2:A$50000),A$ 2:A$50000)0))
'A' being my Unique record Range
'J' Criteria
'L' Specified item to count from Unique record
But my unique records now yielded a total of 8487 (??)
Which formula is extracting the wrong total?
Further, having to use Ctrl/Shift/Enter for each criteria range in a pain. Is the no way around this, we need to copy & past data daily and require formulas to update automatically.
Any advice?
|