Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Duplicates in a range - skip blanks
What I am trying to do is this 1. Go to column C of sheet1 and highlight from C2: to the last entry in the column. ex(C35) 2. Name the selected Range "PurchaseOrderNumber" 3. Go to the first empty column - row 2 and put in this formula =IF(COUNTIF(PurchaseOrderNumber,C2)1,"Duplicate", "") 4. Fill down this in the empty column to the corresponding last entry in column C (see step1) ex (c35) 5. then have a message box to alert if there are any displays. ******** the only condition is ****** 1. in the Column where the purchase order numbers are - some are blank, some are text, and some are numbers. I have tried to used the macro recorder for certain parts of the code but I am stuck now. I am basically trying to find if there are any duplicate numbers in this range. -- jhahes ------------------------------------------------------------------------ jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596 View this thread: http://www.excelforum.com/showthread...hreadid=520252 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Duplicates in a range - skip blanks
Why not use a formula:
=IF(ISNUMBER(C2),IF(COUNTIF($C$2:$C$35,C2)1,"dupl icate",""),"") will check for numbers only. "jhahes" wrote: What I am trying to do is this 1. Go to column C of sheet1 and highlight from C2: to the last entry in the column. ex(C35) 2. Name the selected Range "PurchaseOrderNumber" 3. Go to the first empty column - row 2 and put in this formula =IF(COUNTIF(PurchaseOrderNumber,C2)1,"Duplicate", "") 4. Fill down this in the empty column to the corresponding last entry in column C (see step1) ex (c35) 5. then have a message box to alert if there are any displays. ******** the only condition is ****** 1. in the Column where the purchase order numbers are - some are blank, some are text, and some are numbers. I have tried to used the macro recorder for certain parts of the code but I am stuck now. I am basically trying to find if there are any duplicate numbers in this range. -- jhahes ------------------------------------------------------------------------ jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596 View this thread: http://www.excelforum.com/showthread...hreadid=520252 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Duplicates in a range - skip blanks
I like the solution However 1. there are blanks, text, and numbers - I want the code to skip the blanks 2. I don't know how many rows or columns of data the sheet will have, it could change Thanks -- jhahes ------------------------------------------------------------------------ jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596 View this thread: http://www.excelforum.com/showthread...hreadid=520252 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Duplicates in a range - skip blanks
It ignores blanks ... why not test it? . and your posting talked about column
C only. Is your column remark relating to the postion of the column where the formula is to be placed? Easiest solution is to insert column at beginning of sheet, do run, and then delete it. "jhahes" wrote: I like the solution However 1. there are blanks, text, and numbers - I want the code to skip the blanks 2. I don't know how many rows or columns of data the sheet will have, it could change Thanks -- jhahes ------------------------------------------------------------------------ jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596 View this thread: http://www.excelforum.com/showthread...hreadid=520252 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Skip Blanks Not Working | Excel Worksheet Functions | |||
skip blanks | Excel Worksheet Functions | |||
Skip Over Blanks | Excel Discussion (Misc queries) | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) | |||
Skip multiple blanks | Excel Discussion (Misc queries) |