using OFFSET to fine DUPLICATES in a list
 

Hello,

I have a list of names in column A (a4:a14). In column B I would like
to indicate if the name in column A is a duplicate. Column A is
sorted. Below is the example - I would like to put my OFFSET function
in the DUPLICATE INDICATOR column and use a 1 to indicate the first
occurnace of a name and a 0 for indicating a duplicate.

NAME DUPLICATE INDICATOR
Jack
Jack
Jack
Jill
Fred
Fred
Fred
Fred
Mark
Nancy
Nancy

Thank you!!

myk

using OFFSET to fine DUPLICATES in a list
 

Is there a particular reason you are asking to use OFFSET? I ask because
there are other ways to do this with out using a Volatile function like
OFFSET. Assuming your data starts in A2, put this in B2 and copy down...

=IF(A2="","",--(COUNTIF(A$2:A2,A2)=1))

and copy it down as far as you want.

--
Rick (MVP - Excel)


"myk" wrote in message
...
Hello,

I have a list of names in column A (a4:a14). In column B I would like
to indicate if the name in column A is a duplicate. Column A is
sorted. Below is the example - I would like to put my OFFSET function
in the DUPLICATE INDICATOR column and use a 1 to indicate the first
occurnace of a name and a 0 for indicating a duplicate.

NAME DUPLICATE INDICATOR
Jack
Jack
Jack
Jill
Fred
Fred
Fred
Fred
Mark
Nancy
Nancy

Thank you!!

myk

using OFFSET to fine DUPLICATES in a list
 

=IF(COUNTIF(A:A;"=" & A1)1;IF(MATCH(A1;A:A;0)<ROW();0;1);"")

using OFFSET to fine DUPLICATES in a list
 

You posted this 7 minutes ago in worksheet functions don't multi post, it's
irritating to those who take the time to respond.

Mike

"myk" wrote:

Hello,

I have a list of names in column A (a4:a14). In column B I would like
to indicate if the name in column A is a duplicate. Column A is
sorted. Below is the example - I would like to put my OFFSET function
in the DUPLICATE INDICATOR column and use a 1 to indicate the first
occurnace of a name and a 0 for indicating a duplicate.

NAME DUPLICATE INDICATOR
Jack
Jack
Jack
Jill
Fred
Fred
Fred
Fred
Mark
Nancy
Nancy

Thank you!!

myk

using OFFSET to fine DUPLICATES in a list
 

In support of Mike's message, consider this for your future postings....

From a post by Jeff Johnson:

"You have posted this question individually to multiple groups.
This is called Multiposting and it's BAD. Replies made in one
group will not be visible in the other groups, which may cause
multiple people to respond to your question with the same answer
because they didn't know someone else had already done it. This
is a waste of time.

If you MUST post your message to multiple groups, post a single
message and select all the groups (or type their names manually
in the Newsgroups field, separated by commas) in which you want
it to be seen. This is called Crossposting and when used properly
it is GOOD."

Some additional comment previously posted by me:

"You may not see this as a problem, but those of us who volunteer
answering questions on newsgroups do see it as a problem. You can't
imagine how annoying it is for a volunteer to read a question,
research background material, test sample code and then formulate
and post an answer to the original question only to go to another
newsgroup and find the question posted and ALREADY answered over
there. On top of that, if you cross-post your question, all of the
readers in all the newsgroups it is cross-posted to see both the
original question and all of the answers given to it. This is
beneficial to you because then we can add additional material to,
add clarification to, as well as add additional examples to an
answer you have received previously... that means you end up with
a more complete solution to your problem. This is a win-win
situation for all of us."

And if you are using a web interface that does not allow you to specify
multiple newsgroups as indicated above, then simply pick one newsgroup, post
your message and **wait**... most of the volunteers here visit all the
newsgroups, so you should get the same answers anyway.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
You posted this 7 minutes ago in worksheet functions don't multi post,
it's
irritating to those who take the time to respond.

Mike

"myk" wrote:

Hello,

I have a list of names in column A (a4:a14). In column B I would like
to indicate if the name in column A is a duplicate. Column A is
sorted. Below is the example - I would like to put my OFFSET function
in the DUPLICATE INDICATOR column and use a 1 to indicate the first
occurnace of a name and a 0 for indicating a duplicate.

NAME DUPLICATE INDICATOR
Jack
Jack
Jack
Jill
Fred
Fred
Fred
Fred
Mark
Nancy
Nancy

Thank you!!

myk

using OFFSET to fine DUPLICATES in a list
 

legit complaint - wasn't aware - am now - thanks for the etiquette tip
AND thank you for all the quick (and correct) responses!

myk


On Sep 17, 11:28 am, "Rick Rothstein"
wrote:
In support of Mike's message, consider this for your future postings....

From a post by Jeff Johnson:

"You have posted this question individually to multiple groups.
This is called Multiposting and it's BAD. Replies made in one
group will not be visible in the other groups, which may cause
multiple people to respond to your question with the same answer
because they didn't know someone else had already done it. This
is a waste of time.

If you MUST post your message to multiple groups, post a single
message and select all the groups (or type their names manually
in the Newsgroups field, separated by commas) in which you want
it to be seen. This is called Crossposting and when used properly
it is GOOD."

Some additional comment previously posted by me:

"You may not see this as a problem, but those of us who volunteer
answering questions on newsgroups do see it as a problem. You can't
imagine how annoying it is for a volunteer to read a question,
research background material, test sample code and then formulate
and post an answer to the original question only to go to another
newsgroup and find the question posted and ALREADY answered over
there. On top of that, if you cross-post your question, all of the
readers in all the newsgroups it is cross-posted to see both the
original question and all of the answers given to it. This is
beneficial to you because then we can add additional material to,
add clarification to, as well as add additional examples to an
answer you have received previously... that means you end up with
a more complete solution to your problem. This is a win-win
situation for all of us."

And if you are using a web interface that does not allow you to specify
multiple newsgroups as indicated above, then simply pick one newsgroup, post
your message and **wait**... most of the volunteers here visit all the
newsgroups, so you should get the same answers anyway.

--
Rick (MVP - Excel)

"Mike H" wrote in message

...

You posted this 7 minutes ago in worksheet functions don't multi post,
it's
irritating to those who take the time to respond.

Mike

"myk" wrote:

Hello,

I have a list of names in column A (a4:a14). In column B I would like
to indicate if the name in column A is a duplicate. Column A is
sorted. Below is the example - I would like to put my OFFSET function
in the DUPLICATE INDICATOR column and use a 1 to indicate the first
occurnace of a name and a 0 for indicating a duplicate.

NAME DUPLICATE INDICATOR
Jack
Jack
Jack
Jill
Fred
Fred
Fred
Fred
Mark
Nancy
Nancy

Thank you!!

myk