Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check for duplicate numbers
I have a spreadhseet that has 50 rows (steps) and 12 columns. The columns represent groupings while the row are an assigned steps in the pay schedule. The first column A (monthly pay) is column B (annual salary) divided by 12. The same is true for the rest of the columns like C and D, E and F, G and H and I and J. Recently I was given a task to raise salaries by a same factor for all employees. I did this using simple formulas and checked the result the long way by printing and going through the whole record. My question is does any one know a formula to check for duplicate figures? obviously the same rate can not be applied to two employees at different Steps and Columns. -- Ash |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check for duplicate numbers
Assuming the values you need to check are in A1:A50, enter into B1 and copy
down to B50, =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") Then if you wish, sort columns A and B by column B. You could also look at Conditional Formatting in Help to colour the duplicates red or whatever. Obviously change the cell references to suit your needs, Regards, Alan. "Ash" wrote in message ... I have a spreadhseet that has 50 rows (steps) and 12 columns. The columns represent groupings while the row are an assigned steps in the pay schedule. The first column A (monthly pay) is column B (annual salary) divided by 12. The same is true for the rest of the columns like C and D, E and F, G and H and I and J. Recently I was given a task to raise salaries by a same factor for all employees. I did this using simple formulas and checked the result the long way by printing and going through the whole record. My question is does any one know a formula to check for duplicate figures? obviously the same rate can not be applied to two employees at different Steps and Columns. -- Ash |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check for duplicate numbers
Sorry, should be
=IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") You can enter this formula anywhere you want to and change the A$1:A$50 to the column you want to check, Regards, Alan. "Ash" wrote in message ... Alan Wrote: Assuming the values you need to check are in A1:A50, enter into B1 and copy down to B50, =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") Then if you wish, sort columns A and B by column B. You could also look at Conditional Formatting in Help to colour the duplicates red or whatever. Obviously change the cell references to suit your needs, Regards, Alan. "Ash" wrote in message ...- I have a spreadhseet that has 50 rows (steps) and 12 columns. The columns represent groupings while the row are an assigned steps in the pay schedule. The first column A (monthly pay) is column B (annual salary) divided by 12. The same is true for the rest of the columns like C and D, E and F, G and H and I and J. Recently I was given a task to raise salaries by a same factor for all employees. I did this using simple formulas and checked the result the long way by printing and going through the whole record. My question is does any one know a formula to check for duplicate figures? obviously the same rate can not be applied to two employees at different Steps and Columns. -- Ash - Alan - your formula is missing something. Also where do I put the checked value? Since the spreadsheet is in continum A1:j50, i would like to get a checked result some where out of this range. -- Ash |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check for duplicate numbers
Alan Wrote: Sorry, should be =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") You can enter this formula anywhere you want to and change the A$1:A$50 to the column you want to check, Regards, Alan. "Ash" wrote in message ...- Alan Wrote:- Assuming the values you need to check are in A1:A50, enter into B1 and copy down to B50, =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") Then if you wish, sort columns A and B by column B. You could also look at Conditional Formatting in Help to colour the duplicates red or whatever. Obviously change the cell references to suit your needs, Regards, Alan. "Ash" wrote in message ...- I have a spreadhseet that has 50 rows (steps) and 12 columns. The columns represent groupings while the row are an assigned steps in the pay schedule. The first column A (monthly pay) is column B (annual salary) divided by 12. The same is true for the rest of the columns like C and D, E and F, G and H and I and J. Recently I was given a task to raise salaries by a same factor for all employees. I did this using simple formulas and checked the result the long way by printing and going through the whole record. My question is does any one know a formula to check for duplicate figures? obviously the same rate can not be applied to two employees at different Steps and Columns. -- Ash -- Alan - your formula is missing something. Also where do I put the checked value? Since the spreadsheet is in continum A1:j50, i would like to get a checked result some where out of this range. -- Ash - Alan - I know you are trying to help me and I really appreciate it. But you gave me the same formula again. I am getting an error message every way i tried. when I select the whole spreadsheet and put the formula below data cells I get a circular ref error. I can email you if you want. My email is -- Ash |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check for duplicate numbers
Ash and Alan,
This is what's happening. I don't know what caused it though. Both times Alan wrote this formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") which includes the greater than sign "" i.e. 1. However, when Ash replied, the message quoted dropped the greater than sign. It read: Alan wrote ....... =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") See how the message dropped the greater than sign from the formula? Ash, make sure you have 1 in the formula. Try to type in the formula instead of copy/paste. If your last column is J, try to use column k or l or m for the formula. You said you had 12 columns. So, I don't understand why you said earlier your last column would be J? A to J = 10 columns. I am lost. Epinn "Ash" wrote in message ... Alan Wrote: Sorry, should be =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") You can enter this formula anywhere you want to and change the A$1:A$50 to the column you want to check, Regards, Alan. "Ash" wrote in message ...- Alan Wrote:- Assuming the values you need to check are in A1:A50, enter into B1 and copy down to B50, =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") Then if you wish, sort columns A and B by column B. You could also look at Conditional Formatting in Help to colour the duplicates red or whatever. Obviously change the cell references to suit your needs, Regards, Alan. "Ash" wrote in message ...- I have a spreadhseet that has 50 rows (steps) and 12 columns. The columns represent groupings while the row are an assigned steps in the pay schedule. The first column A (monthly pay) is column B (annual salary) divided by 12. The same is true for the rest of the columns like C and D, E and F, G and H and I and J. Recently I was given a task to raise salaries by a same factor for all employees. I did this using simple formulas and checked the result the long way by printing and going through the whole record. My question is does any one know a formula to check for duplicate figures? obviously the same rate can not be applied to two employees at different Steps and Columns. -- Ash -- Alan - your formula is missing something. Also where do I put the checked value? Since the spreadsheet is in continum A1:j50, i would like to get a checked result some where out of this range. -- Ash - Alan - I know you are trying to help me and I really appreciate it. But you gave me the same formula again. I am getting an error message every way i tried. when I select the whole spreadsheet and put the formula below data cells I get a circular ref error. I can email you if you want. My email is -- Ash |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check for duplicate numbers
Epinn,
I noticed that too, but only after I read Ash's second reply. When I read the first one I just thought that I'd written the formula wrongly so I sent the correct one a second time, thanks for pointing it out to me. Ash, You mention a circular reference, this is probably because you're entering the formula in the range that it's searching. I can't really be specific on your exact spreadsheet for obvious reasons, I'm just giving you a formula that will highlight duplicates, you need to adapt yourself to suit your needs, if indeed it's suitable to you. The formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") counts the number of times that the exact contents of cell A1 occurs in the range A1:A50, if it occurs more than once it will display "Duplicate", if not it will stay blank. This needs to be in a cell out of the range it's searching otherwise you will get the CR error message because it's searching a range that includes itself. Regards, Alan. "Epinn" wrote in message ... Ash and Alan, This is what's happening. I don't know what caused it though. Both times Alan wrote this formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") which includes the greater than sign "" i.e. 1. However, when Ash replied, the message quoted dropped the greater than sign. It read: Alan wrote ....... =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") See how the message dropped the greater than sign from the formula? Ash, make sure you have 1 in the formula. Try to type in the formula instead of copy/paste. If your last column is J, try to use column k or l or m for the formula. You said you had 12 columns. So, I don't understand why you said earlier your last column would be J? A to J = 10 columns. I am lost. Epinn "Ash" wrote in message ... Alan Wrote: Sorry, should be =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") You can enter this formula anywhere you want to and change the A$1:A$50 to the column you want to check, Regards, Alan. "Ash" wrote in message ...- Alan Wrote:- Assuming the values you need to check are in A1:A50, enter into B1 and copy down to B50, =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") Then if you wish, sort columns A and B by column B. You could also look at Conditional Formatting in Help to colour the duplicates red or whatever. Obviously change the cell references to suit your needs, Regards, Alan. "Ash" wrote in message ...- I have a spreadhseet that has 50 rows (steps) and 12 columns. The columns represent groupings while the row are an assigned steps in the pay schedule. The first column A (monthly pay) is column B (annual salary) divided by 12. The same is true for the rest of the columns like C and D, E and F, G and H and I and J. Recently I was given a task to raise salaries by a same factor for all employees. I did this using simple formulas and checked the result the long way by printing and going through the whole record. My question is does any one know a formula to check for duplicate figures? obviously the same rate can not be applied to two employees at different Steps and Columns. -- Ash -- Alan - your formula is missing something. Also where do I put the checked value? Since the spreadsheet is in continum A1:j50, i would like to get a checked result some where out of this range. -- Ash - Alan - I know you are trying to help me and I really appreciate it. But you gave me the same formula again. I am getting an error message every way i tried. when I select the whole spreadsheet and put the formula below data cells I get a circular ref error. I can email you if you want. My email is -- Ash |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check for duplicate numbers
Alan, I don't understand Ash's need entirely. One interpretation is that the columns A, C, E, G and I should be checked for duplicates simultaneously and not by individual column. I know duplicates are not allowed in a column. If a number exists in column A, can it also exist in column C? Only Ash can tell.
I wonder why others don't join the party? Usually this subject attracts quite a bit of attention. May be we have too much of checking for duplicate numbers and counting unique values lately. Ash, feel free to do a search. There are quite a few thread on this subject recently. Good info. Epinn "Alan" wrote in message ... Epinn, I noticed that too, but only after I read Ash's second reply. When I read the first one I just thought that I'd written the formula wrongly so I sent the correct one a second time, thanks for pointing it out to me. Ash, You mention a circular reference, this is probably because you're entering the formula in the range that it's searching. I can't really be specific on your exact spreadsheet for obvious reasons, I'm just giving you a formula that will highlight duplicates, you need to adapt yourself to suit your needs, if indeed it's suitable to you. The formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") counts the number of times that the exact contents of cell A1 occurs in the range A1:A50, if it occurs more than once it will display "Duplicate", if not it will stay blank. This needs to be in a cell out of the range it's searching otherwise you will get the CR error message because it's searching a range that includes itself. Regards, Alan. "Epinn" wrote in message ... Ash and Alan, This is what's happening. I don't know what caused it though. Both times Alan wrote this formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") which includes the greater than sign "" i.e. 1. However, when Ash replied, the message quoted dropped the greater than sign. It read: Alan wrote ....... =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") See how the message dropped the greater than sign from the formula? Ash, make sure you have 1 in the formula. Try to type in the formula instead of copy/paste. If your last column is J, try to use column k or l or m for the formula. You said you had 12 columns. So, I don't understand why you said earlier your last column would be J? A to J = 10 columns. I am lost. Epinn "Ash" wrote in message ... Alan Wrote: Sorry, should be =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") You can enter this formula anywhere you want to and change the A$1:A$50 to the column you want to check, Regards, Alan. "Ash" wrote in message ...- Alan Wrote:- Assuming the values you need to check are in A1:A50, enter into B1 and copy down to B50, =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") Then if you wish, sort columns A and B by column B. You could also look at Conditional Formatting in Help to colour the duplicates red or whatever. Obviously change the cell references to suit your needs, Regards, Alan. "Ash" wrote in message ...- I have a spreadhseet that has 50 rows (steps) and 12 columns. The columns represent groupings while the row are an assigned steps in the pay schedule. The first column A (monthly pay) is column B (annual salary) divided by 12. The same is true for the rest of the columns like C and D, E and F, G and H and I and J. Recently I was given a task to raise salaries by a same factor for all employees. I did this using simple formulas and checked the result the long way by printing and going through the whole record. My question is does any one know a formula to check for duplicate figures? obviously the same rate can not be applied to two employees at different Steps and Columns. -- Ash -- Alan - your formula is missing something. Also where do I put the checked value? Since the spreadsheet is in continum A1:j50, i would like to get a checked result some where out of this range. -- Ash - Alan - I know you are trying to help me and I really appreciate it. But you gave me the same formula again. I am getting an error message every way i tried. when I select the whole spreadsheet and put the formula below data cells I get a circular ref error. I can email you if you want. My email is -- Ash |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check for duplicate numbers
'I don't understand Ash's need entirely.'
Neither do I. I would have thought that only one column would need to be checked at a time, but maybe not. Curious about the disappearing '' though isn't it? I've looked at these groups for quite some time and I've never come across that before, Regards, Alan. "Epinn" wrote in message ... Alan, I don't understand Ash's need entirely. One interpretation is that the columns A, C, E, G and I should be checked for duplicates simultaneously and not by individual column. I know duplicates are not allowed in a column. If a number exists in column A, can it also exist in column C? Only Ash can tell. I wonder why others don't join the party? Usually this subject attracts quite a bit of attention. May be we have too much of checking for duplicate numbers and counting unique values lately. Ash, feel free to do a search. There are quite a few thread on this subject recently. Good info. Epinn "Alan" wrote in message ... Epinn, I noticed that too, but only after I read Ash's second reply. When I read the first one I just thought that I'd written the formula wrongly so I sent the correct one a second time, thanks for pointing it out to me. Ash, You mention a circular reference, this is probably because you're entering the formula in the range that it's searching. I can't really be specific on your exact spreadsheet for obvious reasons, I'm just giving you a formula that will highlight duplicates, you need to adapt yourself to suit your needs, if indeed it's suitable to you. The formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") counts the number of times that the exact contents of cell A1 occurs in the range A1:A50, if it occurs more than once it will display "Duplicate", if not it will stay blank. This needs to be in a cell out of the range it's searching otherwise you will get the CR error message because it's searching a range that includes itself. Regards, Alan. "Epinn" wrote in message ... Ash and Alan, This is what's happening. I don't know what caused it though. Both times Alan wrote this formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") which includes the greater than sign "" i.e. 1. However, when Ash replied, the message quoted dropped the greater than sign. It read: Alan wrote ....... =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") See how the message dropped the greater than sign from the formula? Ash, make sure you have 1 in the formula. Try to type in the formula instead of copy/paste. If your last column is J, try to use column k or l or m for the formula. You said you had 12 columns. So, I don't understand why you said earlier your last column would be J? A to J = 10 columns. I am lost. Epinn "Ash" wrote in message ... Alan Wrote: Sorry, should be =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") You can enter this formula anywhere you want to and change the A$1:A$50 to the column you want to check, Regards, Alan. "Ash" wrote in message ...- Alan Wrote:- Assuming the values you need to check are in A1:A50, enter into B1 and copy down to B50, =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") Then if you wish, sort columns A and B by column B. You could also look at Conditional Formatting in Help to colour the duplicates red or whatever. Obviously change the cell references to suit your needs, Regards, Alan. "Ash" wrote in message ...- I have a spreadhseet that has 50 rows (steps) and 12 columns. The columns represent groupings while the row are an assigned steps in the pay schedule. The first column A (monthly pay) is column B (annual salary) divided by 12. The same is true for the rest of the columns like C and D, E and F, G and H and I and J. Recently I was given a task to raise salaries by a same factor for all employees. I did this using simple formulas and checked the result the long way by printing and going through the whole record. My question is does any one know a formula to check for duplicate figures? obviously the same rate can not be applied to two employees at different Steps and Columns. -- Ash -- Alan - your formula is missing something. Also where do I put the checked value? Since the spreadsheet is in continum A1:j50, i would like to get a checked result some where out of this range. -- Ash - Alan - I know you are trying to help me and I really appreciate it. But you gave me the same formula again. I am getting an error message every way i tried. when I select the whole spreadsheet and put the formula below data cells I get a circular ref error. I can email you if you want. My email is -- Ash |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check for duplicate numbers
May be it only happens around Halloween! <g
When you click reply, you will see ...... "wrote in message," earlier I saw Greek. Can't find it, otherwise I would have shown it to you. Epinn "Alan" wrote in message ... 'I don't understand Ash's need entirely.' Neither do I. I would have thought that only one column would need to be checked at a time, but maybe not. Curious about the disappearing '' though isn't it? I've looked at these groups for quite some time and I've never come across that before, Regards, Alan. "Epinn" wrote in message ... Alan, I don't understand Ash's need entirely. One interpretation is that the columns A, C, E, G and I should be checked for duplicates simultaneously and not by individual column. I know duplicates are not allowed in a column. If a number exists in column A, can it also exist in column C? Only Ash can tell. I wonder why others don't join the party? Usually this subject attracts quite a bit of attention. May be we have too much of checking for duplicate numbers and counting unique values lately. Ash, feel free to do a search. There are quite a few thread on this subject recently. Good info. Epinn "Alan" wrote in message ... Epinn, I noticed that too, but only after I read Ash's second reply. When I read the first one I just thought that I'd written the formula wrongly so I sent the correct one a second time, thanks for pointing it out to me. Ash, You mention a circular reference, this is probably because you're entering the formula in the range that it's searching. I can't really be specific on your exact spreadsheet for obvious reasons, I'm just giving you a formula that will highlight duplicates, you need to adapt yourself to suit your needs, if indeed it's suitable to you. The formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") counts the number of times that the exact contents of cell A1 occurs in the range A1:A50, if it occurs more than once it will display "Duplicate", if not it will stay blank. This needs to be in a cell out of the range it's searching otherwise you will get the CR error message because it's searching a range that includes itself. Regards, Alan. "Epinn" wrote in message ... Ash and Alan, This is what's happening. I don't know what caused it though. Both times Alan wrote this formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") which includes the greater than sign "" i.e. 1. However, when Ash replied, the message quoted dropped the greater than sign. It read: Alan wrote ....... =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") See how the message dropped the greater than sign from the formula? Ash, make sure you have 1 in the formula. Try to type in the formula instead of copy/paste. If your last column is J, try to use column k or l or m for the formula. You said you had 12 columns. So, I don't understand why you said earlier your last column would be J? A to J = 10 columns. I am lost. Epinn "Ash" wrote in message ... Alan Wrote: Sorry, should be =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") You can enter this formula anywhere you want to and change the A$1:A$50 to the column you want to check, Regards, Alan. "Ash" wrote in message ...- Alan Wrote:- Assuming the values you need to check are in A1:A50, enter into B1 and copy down to B50, =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") Then if you wish, sort columns A and B by column B. You could also look at Conditional Formatting in Help to colour the duplicates red or whatever. Obviously change the cell references to suit your needs, Regards, Alan. "Ash" wrote in message ...- I have a spreadhseet that has 50 rows (steps) and 12 columns. The columns represent groupings while the row are an assigned steps in the pay schedule. The first column A (monthly pay) is column B (annual salary) divided by 12. The same is true for the rest of the columns like C and D, E and F, G and H and I and J. Recently I was given a task to raise salaries by a same factor for all employees. I did this using simple formulas and checked the result the long way by printing and going through the whole record. My question is does any one know a formula to check for duplicate figures? obviously the same rate can not be applied to two employees at different Steps and Columns. -- Ash -- Alan - your formula is missing something. Also where do I put the checked value? Since the spreadsheet is in continum A1:j50, i would like to get a checked result some where out of this range. -- Ash - Alan - I know you are trying to help me and I really appreciate it. But you gave me the same formula again. I am getting an error message every way i tried. when I select the whole spreadsheet and put the formula below data cells I get a circular ref error. I can email you if you want. My email is -- Ash |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check for duplicate numbers
Ash,
The formula is correct. You need to enter these formula at the next column not at the same column. That's why you are getting the circular references. Copy and and paste the formula to all the columns till you need to now the duplicate entries. Hope that helps, Thankyou, Shail Ash wrote: Alan Wrote: Sorry, should be =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") You can enter this formula anywhere you want to and change the A$1:A$50 to the column you want to check, Regards, Alan. "Ash" wrote in message ...- Alan Wrote:- Assuming the values you need to check are in A1:A50, enter into B1 and copy down to B50, =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") Then if you wish, sort columns A and B by column B. You could also look at Conditional Formatting in Help to colour the duplicates red or whatever. Obviously change the cell references to suit your needs, Regards, Alan. "Ash" wrote in message ...- I have a spreadhseet that has 50 rows (steps) and 12 columns. The columns represent groupings while the row are an assigned steps in the pay schedule. The first column A (monthly pay) is column B (annual salary) divided by 12. The same is true for the rest of the columns like C and D, E and F, G and H and I and J. Recently I was given a task to raise salaries by a same factor for all employees. I did this using simple formulas and checked the result the long way by printing and going through the whole record. My question is does any one know a formula to check for duplicate figures? obviously the same rate can not be applied to two employees at different Steps and Columns. -- Ash -- Alan - your formula is missing something. Also where do I put the checked value? Since the spreadsheet is in continum A1:j50, i would like to get a checked result some where out of this range. -- Ash - Alan - I know you are trying to help me and I really appreciate it. But you gave me the same formula again. I am getting an error message every way i tried. when I select the whole spreadsheet and put the formula below data cells I get a circular ref error. I can email you if you want. My email is -- Ash |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check for duplicate numbers
Alan Wrote: 'I don't understand Ash's need entirely.' Neither do I. I would have thought that only one column would need to be checked at a time, but maybe not. Curious about the disappearing '' though isn't it? I've looked at these groups for quite some time and I've never come across that before, Regards, Alan. "Epinn" _SPAM wrote in message ... Alan, I don't understand Ash's need entirely. One interpretation is that the columns A, C, E, G and I should be checked for duplicates simultaneously and not by individual column. I know duplicates are not allowed in a column. If a number exists in column A, can it also exist in column C? Only Ash can tell. I wonder why others don't join the party? Usually this subject attracts quite a bit of attention. May be we have too much of checking for duplicate numbers and counting unique values lately. Ash, feel free to do a search. There are quite a few thread on this subject recently. Good info. Epinn "Alan" wrote in message ... Epinn, I noticed that too, but only after I read Ash's second reply. When I read the first one I just thought that I'd written the formula wrongly so I sent the correct one a second time, thanks for pointing it out to me. Ash, You mention a circular reference, this is probably because you're entering the formula in the range that it's searching. I can't really be specific on your exact spreadsheet for obvious reasons, I'm just giving you a formula that will highlight duplicates, you need to adapt yourself to suit your needs, if indeed it's suitable to you. The formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") counts the number of times that the exact contents of cell A1 occurs in the range A1:A50, if it occurs more than once it will display "Duplicate", if not it will stay blank. This needs to be in a cell out of the range it's searching otherwise you will get the CR error message because it's searching a range that includes itself. Regards, Alan. "Epinn" _SPAM wrote in message ... Ash and Alan, This is what's happening. I don't know what caused it though. Both times Alan wrote this formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") which includes the greater than sign "" i.e. 1. However, when Ash replied, the message quoted dropped the greater than sign. It read: Alan wrote ....... =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") See how the message dropped the greater than sign from the formula? Ash, make sure you have 1 in the formula. Try to type in the formula instead of copy/paste. If your last column is J, try to use column k or l or m for the formula. You said you had 12 columns. So, I don't understand why you said earlier your last column would be J? A to J = 10 columns. I am lost. Epinn "Ash" wrote in message ... Alan Wrote:- Sorry, should be =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") You can enter this formula anywhere you want to and change the A$1:A$50 to the column you want to check, Regards, Alan. "Ash" wrote in message ...- Alan Wrote:- Assuming the values you need to check are in A1:A50, enter into B1 and copy down to B50, =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") Then if you wish, sort columns A and B by column B. You could also look at Conditional Formatting in Help to colour the duplicates red or whatever. Obviously change the cell references to suit your needs, Regards, Alan. "Ash" wrote in message ...- I have a spreadhseet that has 50 rows (steps) and 12 columns. The columns represent groupings while the row are an assigned steps in the pay schedule. The first column A (monthly pay) is column B (annual salary) divided by 12. The same is true for the rest of the columns like C and D, E and F, G and H and I and J. Recently I was given a task to raise salaries by a same factor for all employees. I did this using simple formulas and checked the result the long way by printing and going through the whole record. My question is does any one know a formula to check for duplicate figures? obviously the same rate can not be applied to two employees at different Steps and Columns. -- Ash -- Alan - your formula is missing something. Also where do I put the checked value? Since the spreadsheet is in continum A1:j50, i would like to get a checked result some where out of this range. -- Ash -- Alan - I know you are trying to help me and I really appreciate it. But you gave me the same formula again. I am getting an error message every way i tried. when I select the whole spreadsheet and put the formula below data cells I get a circular ref error. I can email you if you want. My email is -- Ash Many thanks to Alan and Eppin. The drop in the numbers/signs was very confusing. I am working on teachers pay schedule. A teacher with BA would fall on group one in which case the first column is monthly pay while the second is annual salary. Each column represents some kind of degree + credit hours. I do need to check all columns for duplicate entry. Since I inherited a pay scale that does not have any meaning (percentage wise) as to by how much each increase is I had to come up with a somewhat complicated formula. Eppin sorry for the column count mistake. I do have 12 columns. As to why there is no mass response, I am as interested as you are to know why? Was this a simple question, perhaps? -- Ash |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check for duplicate numbers
May be it only happens around Halloween! <g
Maybe! I hadn't thought of that, perhaps dark forces are at work here <g Regards, Alan. "Epinn" wrote in message ... May be it only happens around Halloween! <g When you click reply, you will see ...... "wrote in message," earlier I saw Greek. Can't find it, otherwise I would have shown it to you. Epinn "Alan" wrote in message ... 'I don't understand Ash's need entirely.' Neither do I. I would have thought that only one column would need to be checked at a time, but maybe not. Curious about the disappearing '' though isn't it? I've looked at these groups for quite some time and I've never come across that before, Regards, Alan. "Epinn" wrote in message ... Alan, I don't understand Ash's need entirely. One interpretation is that the columns A, C, E, G and I should be checked for duplicates simultaneously and not by individual column. I know duplicates are not allowed in a column. If a number exists in column A, can it also exist in column C? Only Ash can tell. I wonder why others don't join the party? Usually this subject attracts quite a bit of attention. May be we have too much of checking for duplicate numbers and counting unique values lately. Ash, feel free to do a search. There are quite a few thread on this subject recently. Good info. Epinn "Alan" wrote in message ... Epinn, I noticed that too, but only after I read Ash's second reply. When I read the first one I just thought that I'd written the formula wrongly so I sent the correct one a second time, thanks for pointing it out to me. Ash, You mention a circular reference, this is probably because you're entering the formula in the range that it's searching. I can't really be specific on your exact spreadsheet for obvious reasons, I'm just giving you a formula that will highlight duplicates, you need to adapt yourself to suit your needs, if indeed it's suitable to you. The formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") counts the number of times that the exact contents of cell A1 occurs in the range A1:A50, if it occurs more than once it will display "Duplicate", if not it will stay blank. This needs to be in a cell out of the range it's searching otherwise you will get the CR error message because it's searching a range that includes itself. Regards, Alan. "Epinn" wrote in message ... Ash and Alan, This is what's happening. I don't know what caused it though. Both times Alan wrote this formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") which includes the greater than sign "" i.e. 1. However, when Ash replied, the message quoted dropped the greater than sign. It read: Alan wrote ....... =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") See how the message dropped the greater than sign from the formula? Ash, make sure you have 1 in the formula. Try to type in the formula instead of copy/paste. If your last column is J, try to use column k or l or m for the formula. You said you had 12 columns. So, I don't understand why you said earlier your last column would be J? A to J = 10 columns. I am lost. Epinn "Ash" wrote in message ... Alan Wrote: Sorry, should be =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") You can enter this formula anywhere you want to and change the A$1:A$50 to the column you want to check, Regards, Alan. "Ash" wrote in message ...- Alan Wrote:- Assuming the values you need to check are in A1:A50, enter into B1 and copy down to B50, =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") Then if you wish, sort columns A and B by column B. You could also look at Conditional Formatting in Help to colour the duplicates red or whatever. Obviously change the cell references to suit your needs, Regards, Alan. "Ash" wrote in message ...- I have a spreadhseet that has 50 rows (steps) and 12 columns. The columns represent groupings while the row are an assigned steps in the pay schedule. The first column A (monthly pay) is column B (annual salary) divided by 12. The same is true for the rest of the columns like C and D, E and F, G and H and I and J. Recently I was given a task to raise salaries by a same factor for all employees. I did this using simple formulas and checked the result the long way by printing and going through the whole record. My question is does any one know a formula to check for duplicate figures? obviously the same rate can not be applied to two employees at different Steps and Columns. -- Ash -- Alan - your formula is missing something. Also where do I put the checked value? Since the spreadsheet is in continum A1:j50, i would like to get a checked result some where out of this range. -- Ash - Alan - I know you are trying to help me and I really appreciate it. But you gave me the same formula again. I am getting an error message every way i tried. when I select the whole spreadsheet and put the formula below data cells I get a circular ref error. I can email you if you want. My email is -- Ash |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check for duplicate numbers
Hi Ash
It is unclear what you are trying to achieve. What does each row represent, a different employee? What does each column represent? Can you provide more detail with a sample of a couple of rows of data, and a further explanation of what you want to achieve. Because of text wrapping with 12 columns worth of data, it will be clearer if you post the sample in a transposed way, with columns going down the screen, and rows going across the screen Col, Row 1 2 3 A,Monthly Salary,2000,2500 B,Annual Salary,24000,30000 C,BA Add'n,5%,6% etc. -- Regards Roger Govier "Ash" wrote in message ... Alan Wrote: 'I don't understand Ash's need entirely.' Neither do I. I would have thought that only one column would need to be checked at a time, but maybe not. Curious about the disappearing '' though isn't it? I've looked at these groups for quite some time and I've never come across that before, Regards, Alan. "Epinn" _SPAM wrote in message ... Alan, I don't understand Ash's need entirely. One interpretation is that the columns A, C, E, G and I should be checked for duplicates simultaneously and not by individual column. I know duplicates are not allowed in a column. If a number exists in column A, can it also exist in column C? Only Ash can tell. I wonder why others don't join the party? Usually this subject attracts quite a bit of attention. May be we have too much of checking for duplicate numbers and counting unique values lately. Ash, feel free to do a search. There are quite a few thread on this subject recently. Good info. Epinn "Alan" wrote in message ... Epinn, I noticed that too, but only after I read Ash's second reply. When I read the first one I just thought that I'd written the formula wrongly so I sent the correct one a second time, thanks for pointing it out to me. Ash, You mention a circular reference, this is probably because you're entering the formula in the range that it's searching. I can't really be specific on your exact spreadsheet for obvious reasons, I'm just giving you a formula that will highlight duplicates, you need to adapt yourself to suit your needs, if indeed it's suitable to you. The formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") counts the number of times that the exact contents of cell A1 occurs in the range A1:A50, if it occurs more than once it will display "Duplicate", if not it will stay blank. This needs to be in a cell out of the range it's searching otherwise you will get the CR error message because it's searching a range that includes itself. Regards, Alan. "Epinn" _SPAM wrote in message ... Ash and Alan, This is what's happening. I don't know what caused it though. Both times Alan wrote this formula =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") which includes the greater than sign "" i.e. 1. However, when Ash replied, the message quoted dropped the greater than sign. It read: Alan wrote ....... =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") See how the message dropped the greater than sign from the formula? Ash, make sure you have 1 in the formula. Try to type in the formula instead of copy/paste. If your last column is J, try to use column k or l or m for the formula. You said you had 12 columns. So, I don't understand why you said earlier your last column would be J? A to J = 10 columns. I am lost. Epinn "Ash" wrote in message ... Alan Wrote:- Sorry, should be =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") You can enter this formula anywhere you want to and change the A$1:A$50 to the column you want to check, Regards, Alan. "Ash" wrote in message ...- Alan Wrote:- Assuming the values you need to check are in A1:A50, enter into B1 and copy down to B50, =IF(COUNTIF(A$1:A$50,A1)1,"Duplicate","") Then if you wish, sort columns A and B by column B. You could also look at Conditional Formatting in Help to colour the duplicates red or whatever. Obviously change the cell references to suit your needs, Regards, Alan. "Ash" wrote in message ...- I have a spreadhseet that has 50 rows (steps) and 12 columns. The columns represent groupings while the row are an assigned steps in the pay schedule. The first column A (monthly pay) is column B (annual salary) divided by 12. The same is true for the rest of the columns like C and D, E and F, G and H and I and J. Recently I was given a task to raise salaries by a same factor for all employees. I did this using simple formulas and checked the result the long way by printing and going through the whole record. My question is does any one know a formula to check for duplicate figures? obviously the same rate can not be applied to two employees at different Steps and Columns. -- Ash -- Alan - your formula is missing something. Also where do I put the checked value? Since the spreadsheet is in continum A1:j50, i would like to get a checked result some where out of this range. -- Ash -- Alan - I know you are trying to help me and I really appreciate it. But you gave me the same formula again. I am getting an error message every way i tried. when I select the whole spreadsheet and put the formula below data cells I get a circular ref error. I can email you if you want. My email is -- Ash Many thanks to Alan and Eppin. The drop in the numbers/signs was very confusing. I am working on teachers pay schedule. A teacher with BA would fall on group one in which case the first column is monthly pay while the second is annual salary. Each column represents some kind of degree + credit hours. I do need to check all columns for duplicate entry. Since I inherited a pay scale that does not have any meaning (percentage wise) as to by how much each increase is I had to come up with a somewhat complicated formula. Eppin sorry for the column count mistake. I do have 12 columns. As to why there is no mass response, I am as interested as you are to know why? Was this a simple question, perhaps? -- Ash |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Completely baffled on what should be simple | Excel Discussion (Misc queries) | |||
Can you sort with check boxes? | Excel Discussion (Misc queries) | |||
how can I check a worksheet for duplicate entries or numbers? | Excel Worksheet Functions | |||
check if 2 cells are equal but only if they contain numbers not i. | Excel Worksheet Functions | |||
check numbers in a list | Excel Discussion (Misc queries) |