Filtering Duplicate Data to obtain Unique record
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?
|