Translate Forumula For Filtering
 

Hello All,

Teethless Mama helped me with formula yesterday.

Can someone translate/breakdown the formula please? See below

Thank you all!
In B1: =COUNTIF($A$1:A1,A1)1
Copy down as far as needed.

AutoFilter the TRUE value, then go to Edit Delete


"Susan" wrote:

Hello All,

Have 10 columns of data for each row(record). Dates, serial numbers, dollar
amts.
Sometimes a serial number is keyed twice. What is the best way to find if
there are duplicate serial numbers after keying about 500 records? Is there a
way to prevent?
Using Excel 2002.
Many thanks to all!

Susan

Translate Forumula For Filtering
 

Presumably you want to see a list of duplicates from column A and then
get rid of them. The COUNTIF part of the formula will count how many
items are in the range, and when the formula gets copied down it will
become:

=COUNTIF($A$1:A2,A2)1
then
=COUNTIF($A$1:A3,A3)1
etc

So, on row 2 it will look to see if the value in A2 is contained
within the range A1:A2, and on row 3 it will compare A3 with the range
A1:A3 etc. Clearly, A2 will appear at least once in the range A1:A2
and A3 will appear at least once in the range A1:A3, but if these
values appear more than once in the range, then the COUNTIF function
will return a number greater than 1.

The formula as written is a kind of shorthand version of an IF
statement, i.e.:

=IF(COUNTIF($A$1:A1,A1)1,TRUE,FALSE)

It will return a value of TRUE if the COUNTIF function returns a
number greater than 1, and a FALSE value if the function returns 1. It
will return 1 (i.e. a FALSE value from the formula) only for the first
occurrence of a value in column A - all repeat occurrences will be
flagged as TRUE. So, if you then filter the TRUE values and delete
them, you will be left with the unique values.

Hope this helps.

Pete

On Mar 11, 8:35*pm, Susan wrote:
Hello All,

Teethless Mama helped me with formula yesterday.

Can someone translate/breakdown the formula please? *See below

Thank you all!
In B1: =COUNTIF($A$1:A1,A1)1
Copy down as far as needed.

AutoFilter the TRUE value, then go to Edit Delete



"Susan" wrote:
Hello All,

Have 10 columns of data for each row(record). Dates, serial numbers, dollar
amts.
Sometimes a serial number is keyed twice. What is the best way to find if
there are duplicate serial numbers after keying about 500 records? Is there a
way to prevent?
Using Excel 2002.
Many thanks to all!

Susan- Hide quoted text -

- Show quoted text -

Translate Forumula For Filtering
 

Thank you Pete!

"Pete_UK" wrote:

Presumably you want to see a list of duplicates from column A and then
get rid of them. The COUNTIF part of the formula will count how many
items are in the range, and when the formula gets copied down it will
become:

=COUNTIF($A$1:A2,A2)1
then
=COUNTIF($A$1:A3,A3)1
etc

So, on row 2 it will look to see if the value in A2 is contained
within the range A1:A2, and on row 3 it will compare A3 with the range
A1:A3 etc. Clearly, A2 will appear at least once in the range A1:A2
and A3 will appear at least once in the range A1:A3, but if these
values appear more than once in the range, then the COUNTIF function
will return a number greater than 1.

The formula as written is a kind of shorthand version of an IF
statement, i.e.:

=IF(COUNTIF($A$1:A1,A1)1,TRUE,FALSE)

It will return a value of TRUE if the COUNTIF function returns a
number greater than 1, and a FALSE value if the function returns 1. It
will return 1 (i.e. a FALSE value from the formula) only for the first
occurrence of a value in column A - all repeat occurrences will be
flagged as TRUE. So, if you then filter the TRUE values and delete
them, you will be left with the unique values.

Hope this helps.

Pete

On Mar 11, 8:35 pm, Susan wrote:
Hello All,

Teethless Mama helped me with formula yesterday.

Can someone translate/breakdown the formula please? See below

Thank you all!
In B1: =COUNTIF($A$1:A1,A1)1
Copy down as far as needed.

AutoFilter the TRUE value, then go to Edit Delete



"Susan" wrote:
Hello All,

Have 10 columns of data for each row(record). Dates, serial numbers, dollar
amts.
Sometimes a serial number is keyed twice. What is the best way to find if
there are duplicate serial numbers after keying about 500 records? Is there a
way to prevent?
Using Excel 2002.
Many thanks to all!

Susan- Hide quoted text -

- Show quoted text -

Translate Forumula For Filtering
 

You're welcome - hope that explained it for you.

Pete

On Mar 11, 10:06*pm, Susan wrote:
Thank you Pete!



"Pete_UK" wrote:
Presumably you want to see a list of duplicates from column A and then
get rid of them. The COUNTIF part of the formula will count how many
items are in the range, and when the formula gets copied down it will
become:

=COUNTIF($A$1:A2,A2)1
then
=COUNTIF($A$1:A3,A3)1
etc

So, on row 2 it will look to see if the value in A2 is contained
within the range A1:A2, and on row 3 it will compare A3 with the range
A1:A3 etc. Clearly, A2 will appear at least once in the range A1:A2
and A3 will appear at least once in the range A1:A3, but if these
values appear more than once in the range, then the COUNTIF function
will return a number greater than 1.

The formula as written is a kind of shorthand version of an IF
statement, i.e.:

=IF(COUNTIF($A$1:A1,A1)1,TRUE,FALSE)

It will return a value of TRUE if the COUNTIF function returns a
number greater than 1, and a FALSE value if the function returns 1. It
will return 1 (i.e. a FALSE value from the formula) only for the first
occurrence of a value in column A - all repeat occurrences will be
flagged as TRUE. So, if you then filter the TRUE values and delete
them, you will be left with the unique values.

Hope this helps.

Pete

On Mar 11, 8:35 pm, Susan wrote:
Hello All,

Teethless Mama helped me with formula yesterday.

Can someone translate/breakdown the formula please? *See below

Thank you all!
In B1: =COUNTIF($A$1:A1,A1)1
Copy down as far as needed.

AutoFilter the TRUE value, then go to Edit Delete

"Susan" wrote:
Hello All,

Have 10 columns of data for each row(record). Dates, serial numbers, dollar
amts.
Sometimes a serial number is keyed twice. What is the best way to find if
there are duplicate serial numbers after keying about 500 records? Is there a
way to prevent?
Using Excel 2002.
Many thanks to all!

Susan- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -