Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to another forumula? | Excel Discussion (Misc queries) | |||
Need help with a look up forumula I haven't seen before | Excel Worksheet Functions | |||
I Need a forumula or VBA code | Excel Discussion (Misc queries) | |||
Need help making a forumula!!!! | Excel Discussion (Misc queries) | |||
Help with FORUMULA | Excel Discussion (Misc queries) |