Counting duplicates

[Cpt_kludge]

I have a worksheet that I use to track names. There are about 5000 of them and when I add names I have the conditional formatting set to show me those duplicates.
Those that are duplicates I then delete and only keep one of each on my worksheet.
I would like to set up a way to COUNT how many duplicates I have added then deleted so I can see how many times a name has appeared or I have tried to add it.
i currently have the columns set up as:
D is the column of names, one full name per cell
A is the formula =COUNTIF(D:D, Dx) with x being the row number
B is the formula =IF(Ax>1, Ax+1, 1) with x being the row number
C is an extra column that I set up in hopes that it would capture and hold the count.......no such luck.
I have a copy of the file in my dropbox if the forum allows.
I have tried some event macro's but none has effected the count. Perhaps I am doing it wrong?
Thanks for any help
Joe

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

[jason.b75]

Welcome to the forum, Joe!

Unfortunately you can't count something with a formula once you delete it. It might be possible with circular references, but I think that they might be unreliable in this situation, something like a worksheet change event might be the beat way to go.

It would be helpful if you could attach a copy of your file for us to work with. Please remove any personal / confidential information first, if that information is relevant to the question then it is best to substitute it with fictional data that is in the same format as your actual data.

To attach your file, please scroll down a little and look for the 'Go Advanced' button to the bottom right of the reply box. Click on that, then scroll down again and look for 'manage attachments' (just a text link this time, no button). This link will open a new window / tab for you to attach your file.

Please do not use dropbox or similar file sharing services. As a new member, you will not be able to post links. Also most members of the forum will not follow links to such sites when they are posted, too many unscrupulous characters loitering on the internet

[Cpt_kludge]

Taken care of!

[Cpt_kludge]

Good point. I will attach the FILE
This is an abbreviated version of the one with 5k names
TIA

[AliGW]

Please read our rules regarding cross-posting, which you have done here: https://www.mrexcel.com/forum/excel-...then-keep.html

Our rules require you to disclose cross-posting yourself - please bear this in mind in future.

[Cpt_kludge]

Will do, but I had no idea that MrExcel was related.
The help I have gotten there has not helped (or I have not implemented it correctly
James006 has proposed some event macros, but none have effected the data.
I tried to post the file or a link or even some screenshots, but none of that was allowed there
Sorry for violating the rule

[AliGW]

It’s not related, but practically all forums have the same rule.

[jason.b75]

The 2 forums are not related, but there are many members who post on both and other forums as well.

Most, if not all excel help forums have a rule in place requesting that you declare any cross posts, that means on any forum, not just those which might be affilaited with the one you're posting to now. The main purpose is to prevent people wasting their time and yours by repeating existing suggestions which may not work.

Will have a look at this in more detail in the morning (UK time) if you don't get a working answer before then.

[jason.b75]

What happened with the most recent suggestion on MrExcel?

You said that the suggestions provided by James006 did not do as expected, but made no reference to the suggestion proposed by Fluff. Looking at the timestamps, I'm guessing that you possibly hadn't tried it at the time of your last posts here.

[Cpt_kludge]

None of the event macros provided seem to do anything that the formulas were not already doing.
I don't understand from the text, exactly how they were going to do what I needed, but after right clicking the worksheet tap, choosing View code, pasting the text, and choosing
"close and return to Microsoft Excel", adding duplicates to column D would increment the numbers for the rows in columns A & B but as soon as the duplicates were removed from Column D,
the numbers in Column A & B would revert to the original number,.
This is exactly what adding duplicates without an event macro installed does.
None of the codes installed seem to have any effect. I can post them here if it will help. II have the saved in a .txt file
As a check, I found an event macro online, installed it on sheet 2 and it worked as advertised.
If I am doing something wrong, I have no clue what.
Perhaps it is not possible? If so it is the first thing I have tried to do with excel that is not possible
I have found other things that I could not figure out, but this one seems like it might be outside the usage of excel If so, perhaps there is another way of doing what I an looking for?
TIA

[Fluff13]

Try it like I suggested.

[jason.b75]

How are you adding the additional names to your list?

If the suggestions have been based on the assumption that you type in a single name, then immediately delete it if it is flagged as duplicate, then adding multiple names to the list, then checking them and if necessary, deleting multiple duplicates could skew the results, or in the case of Fluff's suggestion, it will not do anything because it has been specifically coded in a way that will ignore pasting to multiple cells.

I'm fairly certain that I can do the same thing with circular references (no vba) but that will be less efficient. Also it will be reliant on single additions being made to the list, with immediate deletion of any duplicate. Let me know if you want to try this method.

Personally, I would persevere with Fluff's suggestion.

[Cpt_kludge]

I downloaded this and ran it.
It took the formulas from column's A-C and replaced column A with "1"
The event macro added was:

Please Login or Register  to view this content.

I assume that the previous is what you call "code" to me it ws "text" until it is put into a vba editor!

Tried copying several several cells in column D and pasting them at the bottom of the data in Column D
other than the duplicates in Column D being highlighted, Nothing changed.
Enameled editing and saved it as a different name.
Reloaded it and again copies cells from column D and pasted them to the end of the data in column D
As before the duplicates were highlighted, but nothing in columns A-C changed.
Beginning to think this is not possible and that I am not speaking you language or you are not speaking one I understand!
As I have violated SOOOOOOO many rule (which even though you claim NOT to be affiliated with other lists, you ALL have different rules) I will understand if this is beyond your powers and give up in it!
Merry Christmas IAC!

[jason.b75]

Please add code tags to your last post, either by selecting the code in the post, then clicking the # icon in the toolbar, or by typing [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] after it.

This is a rule of the forum, which means we are now not permitted to provide any futher assistance until you have rectified the issue. Thanks.

Given that you have tripped over 3 of the rules in your first thread, I would suggest that you take a minute to read them before you succeed in breaking all of them in your first thread

[Cpt_kludge]

I add names by putting the list into notepad, separating the names with carrage return or enter, highlighting the names then copy and paste them into excel (as many as 50-100 names). The Column D then Highlights the duplicates and I delete them keeping only the new names.

The ones I have tried seem to generate circular references,. I'm OK with that as long as it works

What suggestion has Fluff made? I cannot see any response except the one with the attached file an it does not seem to work. Is there something that needs to be enabled? I changed the file from an .xls to an .xlsm to allow for the Macro to work, but I may be missing something Merry Christmas to All around the world

[Cpt_kludge]

After reviewing the rules (again) i am posting that Yes, I have asked the question on the Mr.Excel site, gotten many answers (mostly from James006) and nothing has worked.
Here is the link.....Sorry, your rules will not let me post the link!
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
After finding Fluff's note and trying it, It was again unsuccessful.
Am close to giving up on this as it is not a need for what I am doing, merely a wish and convenience~
Merry Christmas and thanks for trying!

[mjr veverka]

Check here maybe it will be helpful:
https://www.excelforum.com/excel-pro...ficiently.html

[Cpt_kludge]

Actually, Porucha, that may be useful it is sorta on point. Perhaps a little more than I need, but I will not refuse making it easier
Thanks indeed!

[jason.b75]

You will note from post #13, I said that Fluff's suggestion (referring to the 'macro' provided to you in your other thread, and in the attachment in post #12) will only work if you enter the names one at a time!

Now that we know you're actually copying a list of names and pasting in bulk, we can make changes to the code to allow for that so that it will work.

I haven't tried this, just a quick edit to the existing event provided by Fluff. If it works perfect first time then that's great, if not I'll have another look at it later and do some testing before posting. Right now I need sleep before spending a day being civilised to people that I have no desire to be civilised to

Please Login or Register  to view this content.

[Cpt_kludge]

Thanks Jason,
Ya, being civilized is almost always a good thing, bt Ohhhhhh so hard sometimes I found as I got closer and closer to retirement, I suffered fools less and less gladly!
I'll try it out Merry Christmas to all
Joe

[Cpt_kludge]

WOW, That actually worked just the way I wanted
Fantastic and thanks to Fluff and Jason, it works GREAT!
My Christmas just got Much better

One small change, if I were to have the data in column B instead of column D(to delete columns B&C, what would I change in the macro?
I was beginning to think it could NOT be done and am ecstatic to be proved wrong
A Very Merry Christmas to all

[jason.b75]

Gotta do this quick before anyone notices that I've gone missing

Bearing in mind that Fluff is an excel proffesional, whilst I am a (badly) self educated mortal, this is more cobbled than crafted.

Please Login or Register  to view this content.

There are 2 parts that need to be changed for it to work with a different column, which I've changed and highlighted above.

The first part, with the number tells the macro which column should act as a trigger, this stops it from trying to count any other changes elsewhere in the sheet. (The number is simply a count of the columns from the left).

The second part, "B1:B" tells the macro where to look for the existing names in order to see if a duplicate has been entered.

I had the thought that if you tried to change B1 for any reason then it will all go horribly wrong, so I've added a little bit extra to prevent that from happening.

Finally, I realised that if, for any reason, the new list that you copy from notepad contains the same name twice, but that name is not in the original list, then it would not be counted, so I've corrected that and added an initial count of 1 to column A for any names that are not duplicated.

As before, I've only done the edits in the forum, not tested in excel. Hopefully all is good. I'll check back later if I can get away undetected

Merry Christmas!

[Cpt_kludge]

Thanks Jason,
Please don't put yourself at risk
I know NOTHING about VBL but thanks to you and Fluff, I am learning more
I had changed the D1:D to B1:B, but could not understand wy it did not work
Thanks for fixing this and the clear explanation
Merry Christmas Indeed

[Cpt_kludge]

I want to thank Fluff and Jason.b75 for showing me that what I wanted to do , could indeed BE done, and for writing and modifying code (with a tutorial) that makes it happen
Marking this one "SOLVED"
Merry Christmas and a Very Happy New Year to all!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Cpt_kludge]

Finally found it
Thx