If you can send me the file I will be happy to set up the formulas for finding duplicates in each column. Let me know if you are not familiar with uploading files on this site
If the file has sensitive information you can either replace the sensitive data with dummy values, or let me know before you upload it.
If the wikisend site is not working you can try this site instead http://www.sendspace.com/
Ok, I got the file, but I don't see the 160 columns x 150 rows that you were referring to. In the HERO sheet I see 222 dates columns and some 337 rows
1) Which sheet is this on? Which specific columns, and which rows (please use column letters and row numbers to identify them)
2) Do you want to identify the duplicates within each column, or each within each row, or withing the entire grid
3) How should the found duplicates be identified, by color or what?
No the exact number of columns does not have any effect on the formula but it certainly did not help me determine which data table you were referring to.
Anyway, since you already have conditional formatting rules in these cells the only way to identify them is with a macro. Also we cannot use colors because the conditional formatting colors override the normal colors. One way to mark them is by having the macro place a sign such as an asterisk in front if each duplicate number, e..g. *W23
What do you think
Ok it should be ready in about 30 min...
In the meantime, since there will be a macro in that file you need to make sure that you have your Excel Options configured so that macros are allowed to run, otherwise nothing happens when you click the macro button . If you need assistance in this regard please let me know.
Ok, here is a sample file for your review
(click on the file name at the bottom of the page)
The macro is triggered by clicking the Update Now button. It looks for duplicated in the columns F to K and marks the duplicate (i.e, not the first occurrence) with pink
Let me know if it is ok or if you need any adjustments so I can complete the macro and send you the full version
Ah, you didn't tell me you are running Excel 2008. That is the only version of Excel that does not support VBA macros
Do you also have Excel 2004 Mac installed on your Mac? or do you have access to any other version of Excel version?
Or I will have to redo the work using conditional formatting instead of a macro
Ok, I think this should do it
All cells in the range F16:HS324 have now new conditional formatting rules which replace the old rules that were in place
In applying the conditional formatting I tried not override any of the cell colors that you already had in some cells.
The cell that has duplicate in the same column (except the first occurrence of the duplicate) gets highlighted in pink
Hope this is ok. Let me know if you have any questions, and thank you for clicking Accept if you are satisfied with the work (that is the only way I get credit for helping)
Are you sure you are looking at the conditional formatting highlights (e.g cell G20), and not your original fixed gray highlights (e.g. L23)?
Attachments are only available to registered users.
Ok, your Excel version is obviously interpreting this color differently. How about using a more standard color such as yellow or red?
Ok try this one and let me know if it is not showing the standard yellow color
Hope this is ok. Please feel free to reply if you need further clarification or click Accept if this answers your question fully.
Great! Thanks for the accept and bonus. Glad I could help