Formula for categorizing and labeling duplicate entry

Folks,

I was hoping someone could help me out. I will continue trying to solve it, but I think this problem is far beyond my Excel ability, which is poor.

I have hundreds of cells in Column A, some of which are duplicate cells. Now, I want Column B to indicate whether the entry in Column A is duplicate entry or not. If the entry is duplicate, I want Excel to indicate as much in the first such entry.

By way of example, this is what I'm hoping the end result will be:

Individual	Existing (Y/N)
John	No
Jane	No
John	Yes

Please let me know if you guys have any clue how to do this. I'd do it manually but that is a cop out, and I have a range of 1000s of cells.

wishiknewexcel, welcome to the forum.

You can start, if you'll excuse the pun by having a quick look at the forum rules, namely # 1 : http://www.excelforum.com/forum-rule...rum-rules.html, and renaming your thread with an appropriate title.

See if this will work for you.

Hi WishYouKnew,

Try this - it assumes that the data above is in Columns A and B:

Please Login or Register  to view this content.

Hi WishYouKnew,

Try this - it assumes that the data above is in Columns A and B:

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.


Select “Module” from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

I attempted to run the macro and it sort of works.

I think the fault lies with my chart's layout. Here's an extended sample of what my chart looks like (sorry that I cannot provide the file or chart itself):

NBA Stats
2006 Season
Name	Existing (Y/N)
John	No
Girard	No
John	Yes
2007 Season
Name	Existing (Y/N)
Girard	Yes
Cindy	No

So, let me explain how the macro "somewhat worked." For entries within the same season that were duplicate (e.g., John in the table above) the macro caught them and put in "Yes." However if there were duplicate entries that occurred across seasons (e.g., Girard in the table above), the macro is not recognizing them as duplicative.

Now, I can't redesign the sheet; I'm stuck with this format. Can we tweak the macro to work around this formatting limitation? I'm sorry that I am so useless—I tried figuring out the coding and making adjustments on my own and that was, in a word, disastrous. I had enough trouble navigating the coding requirements of making the chart!

If anyone understands and can solve this puzzle, thank you!

I'm testing with a mock-up but I haven't cracked it yet!

Hi WishYouKnew,

Try this - same conditions as my first post:

Please Login or Register  to view this content.

Assuming your data starts in A1, try this in B3:
(revised-)

Formula:

Please Login or Register  to view this content.

drag down

Hope this helps

EDIT-
Posted the formula from B4; Not B3...see revised above

Hi Wishi,

We have only just begun to fight - what's it doing exactly??

I added another series in the same format and it worked nicely???

Can you post a sample where it doesn't work???