How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9655
Experience:  Worked on Macs and PC's for 20+ years.
Type Your Mac Question Here...
John D is online now
A new question is answered every 9 seconds

I have document 160 columns by 150 rows. How can I find any

Customer Question

I have document 160 columns by 150 rows. How can I find any duplicate cells within each single column. The duplicate cells would be a part of 120 unique entries. The columns are calendar days, the rows are 120 film characters and the cells within are the actors playing that role. I want to check if we have used the same actor twice or more on one calendar day.
Submitted: 3 years ago.
Category: Mac
Expert:  John D replied 3 years ago.

Hi,

 

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

 

 

 

Customer: replied 3 years ago.
I have not uploaded before.
Happy to send the file in strict confidence.

Peter
Expert:  John D replied 3 years ago.
Sure, go to www.wikisend.com and upload the file there (no need to sign up). You will then get a page that has the File ID and Download Link. Copy either the File ID or the Download Link and come back here and paste it in your reply.

 

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/

 

 

Customer: replied 3 years ago.
http://wikisend.com/download/573546/IN CONFIDENCE.xlsb
Expert:  John D replied 3 years ago.

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?

 

 

Customer: replied 3 years ago.
It is the hero sheet I want to check.
Column F to Column HS
Row 16 to 324.
My 160 x 150 was an approx - I didn't think it would effect the actual formula you would come up with. Sorry if that has been misleading.
I want to identify duplicates witinh each column.ie date.
The W Then Number in the cells within the grid represent a person.
The names in cloumn D represent a character.
I want to chek that I have not scheduled a person to play more than one chracter on the same day. Where the W exists on it's own means that I have not designated a perosn for that character so those will come up as duplicates in the short term.
Ideally i would like to retain the * but if this is not possible they can be found and deleted. Duplictes in PINK would be best but i don't want to loose the current conditional formatting highlight ing SW W and WF.
Thanks
Expert:  John D replied 3 years ago.

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

 

 

 

 

 

Customer: replied 3 years ago.
I think the * will be too subtle. I'd rather switch off the conditional fomatting to allow the colour option.
Thanks
Expert:  John D replied 3 years ago.

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.

 

Expert:  John D replied 3 years ago.

Ok, here is a sample file for your review

 

http://www.sendspace.com/file/45xbru

(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

 

 

 

Customer: replied 3 years ago.
I do need help in allowingthe macro to run please.
Excel 2008 12.2.4
Expert:  John D replied 3 years ago.

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

 

 



Edited by John D on 10/18/2010 at 6:55 PM EST
Customer: replied 3 years ago.
If conditional formatting will work I think it is better to go down that route.
There is macro in the drop down menu but I really wouldn't know how it does or doesn't work.
Expert:  John D replied 3 years ago.

Ok, I think this should do it

 

http://www.sendspace.com/file/zgyki6

(click on the file name at the bottom of the page)

 

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)

 

 

Customer: replied 3 years ago.
It looks close but the highlights are in grey.
Is there an easy way to make them pink?
Expert:  John D replied 3 years ago.

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.

Register Here

 

 

 



Edited by John D on 10/19/2010 at 12:31 AM EST
Customer: replied 3 years ago.
I have uploaded a screen shot of the same
http://wikisend.com/download/584246/Screenshot.tiff
Expert:  John D replied 3 years ago.

Ok, your Excel version is obviously interpreting this color differently. How about using a more standard color such as yellow or red?

 

 

Customer: replied 3 years ago.
Yellow will be fine
Expert:  John D replied 3 years ago.

Ok try this one and let me know if it is not showing the standard yellow color

 

http://www.sendspace.com/file/px8qr9

(click on the file name at the bottom of the page)

 

Hope this is ok. Please feel free to reply if you need further clarification or click Accept if this answers your question fully.

 

 

 

 

 

 



Edited by John D on 10/19/2010 at 12:53 AM EST
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9655
Experience: Worked on Macs and PC's for 20+ years.
John D and 3 other Mac Specialists are ready to help you
Customer: replied 3 years ago.
Thanks - Yellow works fine.
Brilliant service.
Expert:  John D replied 3 years ago.

Great! Thanks for the accept and bonus. Glad I could help

 

 

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill Bill M. Schenectady, New York
< Last | Next >
  • Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill Bill M. Schenectady, New York
  • The Expert answered my Mac question and was patient. He answered in a thorough and timely manner, keeping the response on a level that could understand. Thank you! Frank Canada
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
 
 
 

Meet The Experts:

 
 
 
  • Mike

    Mac Medic

    Satisfied Customers:

    6256
    Over 20 years IT experience with Apple computers in publishing, marketing and design.
< Last | Next >
  • http://ww2.justanswer.com/uploads/macthelife/2009-10-20_1899_mikesebaharsquare64.jpg Mike's Avatar

    Mike

    Mac Medic

    Satisfied Customers:

    6256
    Over 20 years IT experience with Apple computers in publishing, marketing and design.
  • http://ww2.justanswer.com/uploads/AS/ashiknasameen/2012-5-15_141836_final2.64x64.jpg Ashik's Avatar

    Ashik

    Mac Helper

    Satisfied Customers:

    5282
    7+ Years of Experience in troubleshooting Macs, iPhone, iPad, iPod etc
  • http://ww2.justanswer.com/uploads/DP/dpean/2012-6-6_172828_avatorme1.64x64.JPG Daniel's Avatar

    Daniel

    Mac Genius

    Satisfied Customers:

    4670
    Apple certified on desktop and portable, help desk qualified. Have owned and used Macs since 1989.
  • http://ww2.justanswer.com/uploads/VI/vinodvmenon2005/1.64x64.jpg Vinod Menon's Avatar

    Vinod Menon

    Support Specialist

    Satisfied Customers:

    2068
    worked as a Tech support Associate for Apple products
  • http://ww2.justanswer.com/uploads/BE/beboo/2011-1-14_201648_n5063313142021801763.64x64.jpg Brandon M.'s Avatar

    Brandon M.

    Mac Support Specialist

    Satisfied Customers:

    1501
    10+ Years Mac Support as contractor and currently an IT Manager for law firm
  • http://ww2.justanswer.com/uploads/MA/MacDruid/IMG_0232.64x64.JPG John T. F.'s Avatar

    John T. F.

    Mac Druid

    Satisfied Customers:

    1408
    20+ years in the computer/Mac industry
  • http://ww2.justanswer.com/uploads/MA/MacHelpdesk/1d2d506.64x64.jpg David's Avatar

    David

    Mac Support Specialist

    Satisfied Customers:

    1236
    BSc, H.Dip, Apple Certified