Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicates Formula
On my spreadsheet I have a column (B) for Job Numbers. Example of what the
job numbers look like.... 1929A 90 07747E L 20299A EA I have rows and rows of job numbers that I need to make sure they are not duplicates. I have a formula in Conditional Formatting to find any duplicate job numbers that looks like this =COUNTIF(B:B,B2)1 If excel finds any duplicates it highlights them orange. Sometimes excel will only hightlight one of the duplicates and I need it to highlight ALL of the duplicates not just the first one it sees. Sometimes the formula works where it will highlight both but not always. There have even been times that I have found duplicates that it didn't catch!! This is not good! Is there something wrong with my formula? How can I make this formula work correctly? Thanks so much in advance...You guys always help me find the answers I need to help me do my job!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicates Formula
It might be leading, trailing spaces, so try this
=SUMPRODUCT(--(TRIM(B2:B1000)=TRIM(B2))1 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ryan''s Girl" wrote in message ... On my spreadsheet I have a column (B) for Job Numbers. Example of what the job numbers look like.... 1929A 90 07747E L 20299A EA I have rows and rows of job numbers that I need to make sure they are not duplicates. I have a formula in Conditional Formatting to find any duplicate job numbers that looks like this =COUNTIF(B:B,B2)1 If excel finds any duplicates it highlights them orange. Sometimes excel will only hightlight one of the duplicates and I need it to highlight ALL of the duplicates not just the first one it sees. Sometimes the formula works where it will highlight both but not always. There have even been times that I have found duplicates that it didn't catch!! This is not good! Is there something wrong with my formula? How can I make this formula work correctly? Thanks so much in advance...You guys always help me find the answers I need to help me do my job!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicates Formula
That formula highlighted every job!! : )
"Bob Phillips" wrote: It might be leading, trailing spaces, so try this =SUMPRODUCT(--(TRIM(B2:B1000)=TRIM(B2))1 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ryan''s Girl" wrote in message ... On my spreadsheet I have a column (B) for Job Numbers. Example of what the job numbers look like.... 1929A 90 07747E L 20299A EA I have rows and rows of job numbers that I need to make sure they are not duplicates. I have a formula in Conditional Formatting to find any duplicate job numbers that looks like this =COUNTIF(B:B,B2)1 If excel finds any duplicates it highlights them orange. Sometimes excel will only hightlight one of the duplicates and I need it to highlight ALL of the duplicates not just the first one it sees. Sometimes the formula works where it will highlight both but not always. There have even been times that I have found duplicates that it didn't catch!! This is not good! Is there something wrong with my formula? How can I make this formula work correctly? Thanks so much in advance...You guys always help me find the answers I need to help me do my job!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicates Formula
It should have been
=SUMPRODUCT(--(TRIM($B$2:$B$1000)=TRIM(B2)))1 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ryan''s Girl" wrote in message ... That formula highlighted every job!! : ) "Bob Phillips" wrote: It might be leading, trailing spaces, so try this =SUMPRODUCT(--(TRIM(B2:B1000)=TRIM(B2))1 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ryan''s Girl" wrote in message ... On my spreadsheet I have a column (B) for Job Numbers. Example of what the job numbers look like.... 1929A 90 07747E L 20299A EA I have rows and rows of job numbers that I need to make sure they are not duplicates. I have a formula in Conditional Formatting to find any duplicate job numbers that looks like this =COUNTIF(B:B,B2)1 If excel finds any duplicates it highlights them orange. Sometimes excel will only hightlight one of the duplicates and I need it to highlight ALL of the duplicates not just the first one it sees. Sometimes the formula works where it will highlight both but not always. There have even been times that I have found duplicates that it didn't catch!! This is not good! Is there something wrong with my formula? How can I make this formula work correctly? Thanks so much in advance...You guys always help me find the answers I need to help me do my job!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to seek and find duplicates | Excel Discussion (Misc queries) | |||
Duplicates in Rand Formula | Excel Discussion (Misc queries) | |||
array formula with duplicates | Excel Discussion (Misc queries) | |||
Formula to recognize Duplicates | Excel Worksheet Functions | |||
Formula that recognizes duplicates | Excel Worksheet Functions |