About Us

Ash

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Completely baffled on what should be simple	Pat Hughes	Excel Discussion (Misc queries)	12	October 23rd 06 08:24 PM
Can you sort with check boxes?	Q	Excel Discussion (Misc queries)	3	November 10th 05 08:11 PM
how can I check a worksheet for duplicate entries or numbers?	RFI	Excel Worksheet Functions	1	October 19th 05 04:08 AM
check if 2 cells are equal but only if they contain numbers not i.	Peter Boardman	Excel Worksheet Functions	2	April 17th 05 08:13 PM
check numbers in a list	WYN	Excel Discussion (Misc queries)	3	February 14th 05 08:09 PM

Menu

About Us