Highlight Date

I have 7K records with duplicate entries. I think I have found the two dates where there are duplicate entries. I need a formula that will count how many times '2016-11-10' for instance, is in each cell and highlight the cell if the date appears more than once in that cell. Any takers?


2017-04-05 xXnie X Xe x X Xonti: Sent xXvis TrXnsfer XebinXr Invite. -2017-03-03 xXnie X Xe x X Xonti: Sent xXvis TrXnsfer XebinXr Invite. -2017-03-02 xXnie X Xe x X Xonti: Sent xXvis TrXnsfer XebinXr Invite. -2017-02-23 xXnie X Xe x X Xonti: Sent xXvis TrXnsfer XebinXr Invite. -2017-02-23 xXnie X Xe x X Xonti: Sent xXvis TrXnsfer XebinXr Invite. -2017-02-23 xXnie X Xe x X Xonti: Sent xXvis TrXnsfer XebinXr Invite. -2016-11-10 : Sent xXvis TrXnsfer XebinXr Invite 2016-11-10 : Sent xXvis TrXnsfer XebinXr Invite 2016-11-10 : Sent xXvis TrXnsfer XebinXr Invite 2016-11-10 : Sent xXvis TrXnsfer XebinXr Invite 2016-11-10 : Sent xXvis TrXnsfer XebinXr Invite

Attach a sample workbook (not a picture). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks for the directions and sorry if they are posted :-O

Attached. (hopefully)

Sandy, does that attachment have what is required? Thanks!

Dear friend
I said "attach example file" but I didn't say "I will help NOW"

and YES file is attached.

IMHO, you will need VBA and UDF

of course, at your leisure. (just wanted to make sure I gave the group what was needed. )

had to Google those acronyms BTW, so I'm happy to have the help.

What if there is more than one date duplicated?

eg. in B1 you've:
2017-05-12
2017-04-11
2017-04-05
2017-03-03
2017-03-02
2017-02-23
2017-02-23
2016-11-10
2016-11-10
2016-11-10
so what should be counted?
Do you've defined all dates in every cell?

the count I would need would be if the date is ever recorded twice in one cell. I highlighted one case where the date (any date really) is recorded twice as those are the entries I am looking to purge, while leaving a single entry for each date... does that make sense?

before:

2017-05-12
2017-04-11
2017-04-05
2017-03-03
2017-03-02
2017-02-23
2017-02-23
2016-11-10
2016-11-10
2016-11-10

after purge:

2017-05-12
2017-04-11
2017-04-05
2017-03-03
2017-03-02
2017-02-23
2016-11-10

Check this one:

Formula:

Please Login or Register  to view this content.

= 0 - no duplicates
> 0 - number of duplicates in a single cell
I checked only a few cells because I'm too lazy

That looks perfect! How do I modify your formula for these parameters?

2 columns, 7290 rows, and I've named the tab 'Before'

I bow to my sensei

http://makeagif.com/i/BR19_8

I changed the formula, of which I am obviously too ignorant to write LOL, to this:

=SUM(IFERROR(SUMPRODUCT(((LEN(B1)-LEN(SUBSTITUTE(B1,B1:$B$7290,"")))/LEN($B$1:$B$7290))),"")-SUMPRODUCT(--ISNUMBER(SEARCH($B$1:$B$7290,$B1))))


and the first cell looks like it is counting correctly, showing '0'. But when I copy it down, I receive the #VALUE! error.

I'm not sure what you mean about 'size range' please forgive my ignorance!

The sheet has 2 columns (the first is of no consequence, as it is just an ID field, I should have removed it in my example I can see that now... but I need it once I correct the comments so I can run a mass update, so it can just be omitted from the formula) and 7290 rows, what other 'size range' info should I add?

Thanks again! I will definitely be able to apply this formula in the future once I get past the learning curve of editing it.

w00t! I totally get it now. Thank you so much!

Do I need to 'mark this resolved'? How do I do that?

see here for both pictures: https://www.excelforum.com/developme...ml#post4668522

I was wrong, now I am going back into the cells and some of them are showing zero (0) duplicates, but there are duplicates in there. This is the formula I am using:

=SUM(IFERROR(SUMPRODUCT(((LEN(B1)-LEN(SUBSTITUTE(B1,$G$1:$G$32,"")))/LEN($G$1:$G$32))),"")-SUMPRODUCT(--ISNUMBER(SEARCH($G$1:$G$32,$B1))))

and I created the 'G' reference column with these dates:



2016-10-21
2016-11-10
2016-11-11
2016-11-15
2016-11-16
2016-11-21
2016-11-28
2016-11-29
2016-12-01
2016-12-06
2016-12-27
2017-01-10
2017-01-30
2017-01-31
2017-02-10
2017-02-23
2017-02-24
2017-03-02
2017-03-03
2017-03-10
2017-03-22
2017-03-23
2017-04-03
2017-04-04
2017-04-05
2017-04-11
2017-04-17
2017-04-18
2017-04-19
2017-05-03
2017-05-07
2017-05-12



thanks

attach sample file