Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
locating duplicates
using Excel 2003
I have a list of invoice numbers and need to find duplicates. I tried using conditional formatting in column E as such: condition 1 formula is =COUNTIF(E:E,E1)1 (cell shading to yellow) The problem I'm running into is that, for instance, it sees invoice numbers 0000003730 and 03730 as duplicates. I only want it to shade if it's an exact match, i.e. 03730 and 03730. I tried using a helper column (F) and in F2 put the formula =IF(E2=E1,"duplicate","") But I want ALL cells containing the same number to say "duplicate", i.e. all occurences of 03730 should state duplicate, not just the second (or third or fourth!) time it appears. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
locating duplicates
=countif() does text comparisons.
So '1 and 1 will be counted. You could use =sumproduct() =sumproduct(--($e$1:$E$99=e1)) to distinguish between text and numbers. But if your values are really numbers just with different numberformats, you'll have to do something different. About the =sumproduct() formula: Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html bshorey wrote: using Excel 2003 I have a list of invoice numbers and need to find duplicates. I tried using conditional formatting in column E as such: condition 1 formula is =COUNTIF(E:E,E1)1 (cell shading to yellow) The problem I'm running into is that, for instance, it sees invoice numbers 0000003730 and 03730 as duplicates. I only want it to shade if it's an exact match, i.e. 03730 and 03730. I tried using a helper column (F) and in F2 put the formula =IF(E2=E1,"duplicate","") But I want ALL cells containing the same number to say "duplicate", i.e. all occurences of 03730 should state duplicate, not just the second (or third or fourth!) time it appears. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
locating duplicates
Thanks, Dave.
I wasn't totally clear on your instructions, so I tried using the sumproduct in conditional formatting and everything shaded yellow. Instead, I tried pasting the sumproduct in a helper column (F). It returned numbers that indicated how many times that particular invoice number occurred in the spreadsheet (i.e. 2, 5, 16). So essentially it worked. But it also involved copying and pasting special values into another helper column (G) to get rid of the formulas, then deleting the original helper column (F), then doing a bunch of sorts to see what kind of results I had gotten. I would prefer color shading to the number return, so if anyone has any other solutions, I'd like to know! "Dave Peterson" wrote: =countif() does text comparisons. So '1 and 1 will be counted. You could use =sumproduct() =sumproduct(--($e$1:$E$99=e1)) to distinguish between text and numbers. But if your values are really numbers just with different numberformats, you'll have to do something different. About the =sumproduct() formula: Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
locating duplicates
How about this in the format|Conditional formatting dialog:
=AND(E1<"",SUMPRODUCT(--($E$1:$E$99=E1))1) (with E1 the activecell) bshorey wrote: Thanks, Dave. I wasn't totally clear on your instructions, so I tried using the sumproduct in conditional formatting and everything shaded yellow. Instead, I tried pasting the sumproduct in a helper column (F). It returned numbers that indicated how many times that particular invoice number occurred in the spreadsheet (i.e. 2, 5, 16). So essentially it worked. But it also involved copying and pasting special values into another helper column (G) to get rid of the formulas, then deleting the original helper column (F), then doing a bunch of sorts to see what kind of results I had gotten. I would prefer color shading to the number return, so if anyone has any other solutions, I'd like to know! "Dave Peterson" wrote: =countif() does text comparisons. So '1 and 1 will be counted. You could use =sumproduct() =sumproduct(--($e$1:$E$99=e1)) to distinguish between text and numbers. But if your values are really numbers just with different numberformats, you'll have to do something different. About the =sumproduct() formula: Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
locating duplicates
EXACTLY. Thank you!
"Dave Peterson" wrote: How about this in the format|Conditional formatting dialog: =AND(E1<"",SUMPRODUCT(--($E$1:$E$99=E1))1) (with E1 the activecell) bshorey wrote: Thanks, Dave. I wasn't totally clear on your instructions, so I tried using the sumproduct in conditional formatting and everything shaded yellow. Instead, I tried pasting the sumproduct in a helper column (F). It returned numbers that indicated how many times that particular invoice number occurred in the spreadsheet (i.e. 2, 5, 16). So essentially it worked. But it also involved copying and pasting special values into another helper column (G) to get rid of the formulas, then deleting the original helper column (F), then doing a bunch of sorts to see what kind of results I had gotten. I would prefer color shading to the number return, so if anyone has any other solutions, I'd like to know! "Dave Peterson" wrote: =countif() does text comparisons. So '1 and 1 will be counted. You could use =sumproduct() =sumproduct(--($e$1:$E$99=e1)) to distinguish between text and numbers. But if your values are really numbers just with different numberformats, you'll have to do something different. About the =sumproduct() formula: Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locating Under Payments (HELP!) | Excel Discussion (Misc queries) | |||
Locating | New Users to Excel | |||
locating the top 5 number (in a col) | Excel Worksheet Functions | |||
Locating a Chart | Excel Discussion (Misc queries) | |||
Locating a Chart | Charts and Charting in Excel |