'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"
[email protected]_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"
[email protected] 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"
[email protected]_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"
[email protected] 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"
[email protected] 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"
[email protected] 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
[email protected]
