Filtering Duplicate Data to obtain Unique record
 

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?

Filtering Duplicate Data to obtain Unique record
 

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?

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?

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?

Filtering Duplicate Data to obtain Unique record
 

I seem to be having difficulty in explaining what I need.

In Column €˜A I have client reference numbers €“ some duplicated
In Column €˜B I have my 5 €˜sales people that worked on the above clients, these are identified by their initials, i.e. EB, EF, ME
I need to determine how many individual clients we worked, i.e. wanting to extract the unique records from €˜A.
In certain cases EB/EF & ME worked the same client, which returns 3 records/hits. I only need 1 (one) unique record returned.
Hence, a formula is required where multiple criterias can be used.

Hope the explanation helps; I am starting to confuse myself.

Regards

EricB


"bj" wrote:

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?

Filtering Duplicate Data to obtain Unique record
 

It spunds like you really need at least two different equaitons
one the number of unique clients and
two the number of clients for each sales person

For unique client numbers
going back to your original posting did you use in your helper column (B?)
=COUNTIF(A2:A8628,A2) or actually
=COUNTIF(A$2:A$8628,A2)
If you used the first, the number should have been correct
if you used the second
a correct number could be achieved by
=countif(B:B,1)+Countif(B:B,2/2+countif(B:B,3)/3 ... up to the max number of
duplicates

for he issue raised in the second posting where you want to know the number
of clients each salesman has
I assume Column J
=countif(J:J,l5) etc
should give you the number of times the Saleman in L5 etc is listed

was the value of 8487 from the sum of the count of each salespereson?
if this is the case there might be some names not entered correctly

"EricB" wrote:

I seem to be having difficulty in explaining what I need.

In Column €˜A I have client reference numbers €“ some duplicated
In Column €˜B I have my 5 €˜sales people that worked on the above clients, these are identified by their initials, i.e. EB, EF, ME
I need to determine how many individual clients we worked, i.e. wanting to extract the unique records from €˜A.
In certain cases EB/EF & ME worked the same client, which returns 3 records/hits. I only need 1 (one) unique record returned.
Hence, a formula is required where multiple criterias can be used.

Hope the explanation helps; I am starting to confuse myself.

Regards

EricB


"bj" wrote:

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?

Filtering Duplicate Data to obtain Unique record
 

A B C D
Clientno Decision User
12035576 2 NK DM5
12035576 1 LF EF2
5205119 2 NK LF
5205119 1 EF2 ME
7265625 2 EF2 NK
7265625 1 LF
10115273 2 DM5
10115273 1 LF
12265699 2 EF2
12265699 1 LF
X System
XX System
XXX System
Column A - is my list of clients, some 30.000 per months (60% =
System/40%User) €“ I am just interested in counting the 40% in €˜D.
Column B - =COUNTIF(A2:A30000,A2)

Formulas used:
=SUM(N(FREQUENCY(IF(I$14:I$50012=D2,A$14:A$50012), A$14:A$50012)=1))
=COUNT(1/FREQUENCY(IF(I$14:I50012=D2,MATCH(A$14:A$50012,A$1 4:A$50012,0)),ROW(A$14:A$50012)-ROW(A$14)+1))
=SUM(IF(FREQUENCY(IF($I$14:$I$50000=D2,MATCH($A$14 :$A$50000,$A$14:$A$50000)),ROW($I$14:$I$50000)-ROW($I$14)+1),1))
=SUM(N(FREQUENCY(IF($I$14:$I$50000=D2,$A$14:$A$500 00),$A$14:$A$50000)0))

In the above sample, we have 10 Unique records in €˜A, due to different
users looking at the same deal twice, the above formulas are all returning a
total of 20.
(D2 is €˜dragged down to D6 and then Auto Summed.)

Is there no one stop formula available?

EricB


"bj" wrote:

It spunds like you really need at least two different equaitons
one the number of unique clients and
two the number of clients for each sales person

For unique client numbers
going back to your original posting did you use in your helper column (B?)
=COUNTIF(A2:A8628,A2) or actually
=COUNTIF(A$2:A$8628,A2)
If you used the first, the number should have been correct
if you used the second
a correct number could be achieved by
=countif(B:B,1)+Countif(B:B,2/2+countif(B:B,3)/3 ... up to the max number of
duplicates

for he issue raised in the second posting where you want to know the number
of clients each salesman has
I assume Column J
=countif(J:J,l5) etc
should give you the number of times the Saleman in L5 etc is listed

was the value of 8487 from the sum of the count of each salespereson?
if this is the case there might be some names not entered correctly

"EricB" wrote:

I seem to be having difficulty in explaining what I need.

In Column €˜A I have client reference numbers €“ some duplicated
In Column €˜B I have my 5 €˜sales people that worked on the above clients, these are identified by their initials, i.e. EB, EF, ME
I need to determine how many individual clients we worked, i.e. wanting to extract the unique records from €˜A.
In certain cases EB/EF & ME worked the same client, which returns 3 records/hits. I only need 1 (one) unique record returned.
Hence, a formula is required where multiple criterias can be used.

Hope the explanation helps; I am starting to confuse myself.

Regards

EricB


"bj" wrote:

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?

Filtering Duplicate Data to obtain Unique record
 

restating what I now think you want
You want unique combinations of columns A and C when C does not equal "system"

maybe
in E2 enter
=if(C2="System",0,A2&C2)
in F2
=if(e2<0,1/countif(E:E,E2),0)

your unique combos of A and C will =sum(F:F)
"EricB" wrote:

A B C D
Clientno Decision User
12035576 2 NK DM5
12035576 1 LF EF2
5205119 2 NK LF
5205119 1 EF2 ME
7265625 2 EF2 NK
7265625 1 LF
10115273 2 DM5
10115273 1 LF
12265699 2 EF2
12265699 1 LF
X System
XX System
XXX System
Column A - is my list of clients, some 30.000 per months (60% =
System/40%User) €“ I am just interested in counting the 40% in €˜D.
Column B - =COUNTIF(A2:A30000,A2)

Formulas used:
=SUM(N(FREQUENCY(IF(I$14:I$50012=D2,A$14:A$50012), A$14:A$50012)=1))
=COUNT(1/FREQUENCY(IF(I$14:I50012=D2,MATCH(A$14:A$50012,A$1 4:A$50012,0)),ROW(A$14:A$50012)-ROW(A$14)+1))
=SUM(IF(FREQUENCY(IF($I$14:$I$50000=D2,MATCH($A$14 :$A$50000,$A$14:$A$50000)),ROW($I$14:$I$50000)-ROW($I$14)+1),1))
=SUM(N(FREQUENCY(IF($I$14:$I$50000=D2,$A$14:$A$500 00),$A$14:$A$50000)0))

In the above sample, we have 10 Unique records in €˜A, due to different
users looking at the same deal twice, the above formulas are all returning a
total of 20.
(D2 is €˜dragged down to D6 and then Auto Summed.)

Is there no one stop formula available?

EricB


"bj" wrote:

It spunds like you really need at least two different equaitons
one the number of unique clients and
two the number of clients for each sales person

For unique client numbers
going back to your original posting did you use in your helper column (B?)
=COUNTIF(A2:A8628,A2) or actually
=COUNTIF(A$2:A$8628,A2)
If you used the first, the number should have been correct
if you used the second
a correct number could be achieved by
=countif(B:B,1)+Countif(B:B,2/2+countif(B:B,3)/3 ... up to the max number of
duplicates

for he issue raised in the second posting where you want to know the number
of clients each salesman has
I assume Column J
=countif(J:J,l5) etc
should give you the number of times the Saleman in L5 etc is listed

was the value of 8487 from the sum of the count of each salespereson?
if this is the case there might be some names not entered correctly

"EricB" wrote:

I seem to be having difficulty in explaining what I need.

In Column €˜A I have client reference numbers €“ some duplicated
In Column €˜B I have my 5 €˜sales people that worked on the above clients, these are identified by their initials, i.e. EB, EF, ME
I need to determine how many individual clients we worked, i.e. wanting to extract the unique records from €˜A.
In certain cases EB/EF & ME worked the same client, which returns 3 records/hits. I only need 1 (one) unique record returned.
Hence, a formula is required where multiple criterias can be used.

Hope the explanation helps; I am starting to confuse myself.

Regards

EricB


"bj" wrote:

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?

Filtering Duplicate Data to obtain Unique record
 

Thank you for your assistance and commitment in working on my problem. The
'one' formula I was hoping for seems not to exist. Thus I will lay this issue
to rest.

Kind regards

EricB


"bj" wrote:

restating what I now think you want
You want unique combinations of columns A and C when C does not equal "system"

maybe
in E2 enter
=if(C2="System",0,A2&C2)
in F2
=if(e2<0,1/countif(E:E,E2),0)

your unique combos of A and C will =sum(F:F)
"EricB" wrote:

A B C D
Clientno Decision User
12035576 2 NK DM5
12035576 1 LF EF2
5205119 2 NK LF
5205119 1 EF2 ME
7265625 2 EF2 NK
7265625 1 LF
10115273 2 DM5
10115273 1 LF
12265699 2 EF2
12265699 1 LF
X System
XX System
XXX System
Column A - is my list of clients, some 30.000 per months (60% =
System/40%User) €“ I am just interested in counting the 40% in €˜D.
Column B - =COUNTIF(A2:A30000,A2)

Formulas used:
=SUM(N(FREQUENCY(IF(I$14:I$50012=D2,A$14:A$50012), A$14:A$50012)=1))
=COUNT(1/FREQUENCY(IF(I$14:I50012=D2,MATCH(A$14:A$50012,A$1 4:A$50012,0)),ROW(A$14:A$50012)-ROW(A$14)+1))
=SUM(IF(FREQUENCY(IF($I$14:$I$50000=D2,MATCH($A$14 :$A$50000,$A$14:$A$50000)),ROW($I$14:$I$50000)-ROW($I$14)+1),1))
=SUM(N(FREQUENCY(IF($I$14:$I$50000=D2,$A$14:$A$500 00),$A$14:$A$50000)0))

In the above sample, we have 10 Unique records in €˜A, due to different
users looking at the same deal twice, the above formulas are all returning a
total of 20.
(D2 is €˜dragged down to D6 and then Auto Summed.)

Is there no one stop formula available?

EricB


"bj" wrote:

It spunds like you really need at least two different equaitons
one the number of unique clients and
two the number of clients for each sales person

For unique client numbers
going back to your original posting did you use in your helper column (B?)
=COUNTIF(A2:A8628,A2) or actually
=COUNTIF(A$2:A$8628,A2)
If you used the first, the number should have been correct
if you used the second
a correct number could be achieved by
=countif(B:B,1)+Countif(B:B,2/2+countif(B:B,3)/3 ... up to the max number of
duplicates

for he issue raised in the second posting where you want to know the number
of clients each salesman has
I assume Column J
=countif(J:J,l5) etc
should give you the number of times the Saleman in L5 etc is listed

was the value of 8487 from the sum of the count of each salespereson?
if this is the case there might be some names not entered correctly

"EricB" wrote:

I seem to be having difficulty in explaining what I need.

In Column €˜A I have client reference numbers €“ some duplicated
In Column €˜B I have my 5 €˜sales people that worked on the above clients, these are identified by their initials, i.e. EB, EF, ME
I need to determine how many individual clients we worked, i.e. wanting to extract the unique records from €˜A.
In certain cases EB/EF & ME worked the same client, which returns 3 records/hits. I only need 1 (one) unique record returned.
Hence, a formula is required where multiple criterias can be used.

Hope the explanation helps; I am starting to confuse myself.

Regards

EricB


"bj" wrote:

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?