Mac

Ask Mac Questions and Get Answers from Experts ASAP

Ask a Computer Expert,
Get an Answer ASAP!

Mac
This answer was rated:

I am making cold calls to big and small offices of individuals.

I can't call one office...
I am making cold calls to big and small offices of individuals. I can't call one office more than every 2 hours (60 calls) or I risk being screened out. The offices and individuals are ranked so I can sort them in order of priority. I take care of that before this process. This process would produce a prioritized list that I could call line-by-line without getting screened. I am using Excel 2008 for Mac.

1.     Insert first row from Data List to first row on New List (after Header Row)
a.     Delete first row from DL
2.     Check “Address” field of next row on Data List against last 59 row's “Address"es on New List
a.     If match exists, disregard and repeat Step 2 with next row
b.     If no match exists, add row to New List
   i.     Delete row from DL
   ii.     Repeat Step 2 with next row
3.     Stop when 60 lines have been filled on New List
4.     Repeat Steps 2&3 from top of Data List until 60 consecutive rows on New List cannot be filled with unique addresses
5.     Stop

The deletion prevents duplication. TY.
Show More
Show Less
Ask Your Own Mac Question
Answered in 6 minutes by:
7/18/2009
John D
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9,666
Experience: Worked on Macs and PC's for 20+ years.
Verified

Hi,

 

Are you looking for a way to automate this process, i.e. using a macro? If so Excel 2008 only supports Applescript code, would you consider using Excel 2004 or Excel on Windows?

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
Downloading 2004 now.
Customer reply replied 8 years ago
Wait. That was a stupid update. If that's what I need, I will get it.

Let me know when you're ready.

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
ok. what i've got is a dinosaur frrankenstein pc running 2003

Do you mean you also have Excel 2003 on another PC running Windows OS?

 

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
Yes.

Ok, do you have the file with the Data List (or sample data list) that I can use to base the macro on?

 

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
Yes, but I am at a restaurant right now. I will be home in around 1/2 an hour. Am I going to get the macro if it works? I have a lot of different lists that I want to be able to apply this to.

Sure. I will write the macro and send it to you once I get the data file. Here are the instructions for uploading you file.

 

1. go to http://fileqube.com
2. click ‘Browse' then select your file
3. click ‘agree and upload' button (when it finishes uploading a new page will be displayed)
4. click on the link next to 'Download link' and copy that link, then come back to this page and paste the download link in your reply.

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
Customer reply replied 8 years ago
"Branch Address" is the address in question.
Customer reply replied 8 years ago
I don't know much about implementing macros. A little guidance would be appreciated. Also, is there a way to tip? This will be a tremendous help to my business.
Customer reply replied 8 years ago
I'm sorry. It is actually "Firm Street". There will be additional columns in the data this will be applied to: "Firm Street" will be column "T" and total data range for columns will be (A:BD). Need anything else?

Got it, thanks.

 

Will start working on it shortly and will send it out as soon as I am done, If I find that I need more information I will let you know.

 

 

John D
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9,666
Experience: Worked on Macs and PC's for 20+ years.
Verified
John D and 87 other Mac Specialists are ready to help you
Ask your own question now

Thanks for the accept.

 

Question::

 

You wrote:

b. If no match exists, add row to New List
i. Delete row from DL

 

- To which row it should be added, and which data should the added row have?

 

- Which row should be deleted from Data List?

 

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
It should go in the next available row on the New List. The added row should have all data. The row just added to the New List should be deleted. Each time 60 rows have been added to the New List, I want it to start again from the top. Deleting the data from the Data List as it is added to the New List prevents duplication.
Customer reply replied 8 years ago
I meant to leave positive feedback for you. You deliver something that works as described and I'll pay you double.

Ok, here is the initial version for testing.

 

FILE

 

I tried to follow your outline literally but in the absence of real data in the New List it was not possible to fully understand the concept and hence to test it properly. Had to create some arbitrary data in the New List so that the macro can run.

 

To run the macro press Ctrl + Q.

 

Please make sure that your Excel options is set to allow macros to run, otherwise nothing will happen when you try to run the macro.

 

Go ahead and test it out and let me know the changes or adjustments that you need.

 

I have to leave now for a few hours. Will be back this evening to pick up any message you may have left and make the adjustments.

 

Thanks.

 

 

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
Here is a new workbook with the full data set in "Master". "New List" contains the result of running the macro with cell A1 selected.

http://www.fileqube.com/file/IxQPVi206236

In the new list, I hope for no repeating values in the "Firm Address" in 60 consecutive rows. As you can see, this is not true. If you need to fill the first 60 rows, you could use the "CRD Number" column to substitute for addresses as these are all unique.

Hi again,

 

I am not sure I understand where the duplicates should not be (between the New List and the Data List, or between the items within the New List, or between the items within the Data List,

 

To the best of my understanding the macro that I sent you does what you have outlined, step by step. I may have misinterpreted a step or two or you may have been unclear on some of the steps, that is why I would like to tell me which step in your instruction was not followed properly so that I can adjust that part of the macro,

 

 

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
The duplicates should not appear in column "T" ("Firm Address) in the 59 rows previous the one to be filled on the New List. Let's try this:

1.     Insert first row from Data List to first row on New List
a.     Delete first row from DL

2.     Check "T" column of next row on Data List against "T" column in previous 59 row's on New List
a.     If match exists, disregard and repeat Step 2 with next row
b.     If no match exists, add row to New List
   i.     Delete added row from DL
   ii.     Repeat Step 2 with next row

3.     Stop when 60 lines have been filled on New List

4.     Repeat Steps 2&3 from top of Data List until "T" column of 60 consecutive rows on New List cannot be filled with unique values

5.     Stop

Does that help?

In other words rows 3 through 24 should not be in the New List?

 

 

 

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
They should not appear in the first 60 rows. They should be disregarded as duplicates.

After filling 60 rows, it should start again from the top of the Data List and do it again.

So you should end up with Row 2 on the Data List as Row 2 on the New List and Row 3 on the Data List as Row 62 on the New List. And so on until you cannot fill 60 rows on the New List with unique column "T" values

Make sense?

Still something is missing!

 

 

1. Insert first row from Data List to first row on New List
a. Delete first row from DL

*** So by now New List has only one row of data


2. Check "T" column of next row on Data List against "T" column in previous 59 row's on New List

 

*** But at this point New List has only one row of data, so how does 'provisos 59 rows' apply

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
Can you create 60 rows and fill "T" with copied "A" ("CRD Numbers", which are unique to each row and will not be = to any value in "T") to subsitiute? Then Start at 61?

I can worry about the header row later.

So Row 2 in Data List becomes Row 61 in New List. Row 3 in Data List becomes Row 121 in New List.

I can remember to inert the substitute rows each time if necessary.

I am sorry we are jumping around from one issue to another. I asked a question and I got a confusing answer which does not specifically answer my question. The issue may be clear to you but it is not to me yet.

 

I can fix the macro to do exactly what you have outlined without going into the concept of the process, if you can clarify the points that I asked you about. If you do not think doing this will provide the solution let me know so that I can ask the moderator to refund you the amount that you have authorized to pay when you clicked the accept.

 

 

 

 

 

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
Okay. Don't worry. I'm sure I haven't been clear. It's hard to articulate. What points do you need clarified at this point?

Would it be helpful if I provided you with 59 or 60 rows of data to start the new list?
Customer reply replied 8 years ago
Is there a way we can talk? That way we can be sure to be understood.
(832) (NNN) NNN-NNNN

Yes I think that would help. I will then modify the macro to follow your outline exactly, then you can test it and tell me if there are changes needed. But please review your outline in view of these communications to make sure it includes all the points that you have mentioned as I will only be using the outline.

 

If you can provide the information tonight I will work on it in the morning and hope to send it out to you by noon time pacific.

 

 

Ask Your Own Mac Question

Re your question about talking on the phone, I am afraid communication by phone or personal email is not allowed on this site.

 

 

 

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
Great. I will reply with an up to date outline and a new .xls which contains a "Data List" sheet (for consistency) with the data to be manipulated and a "New List" Sheet containing 60 rows of data at the top. Thank you again.

Sounds good.

 

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
Here's the .xls

http://www.fileqube.com/file/HrTkyj206239

I have two versions of the outline. Use whatever helps you and works:

Here's the first version of the outline:

1.     Insert first row from Data List (Row 2) to first row on New List (Row 61)
a.     Delete first row from DL

2.     Check "T" column of next row on Data List against "T" column in previous 59 row's on New List
a.     If match exists, disregard and repeat Step 2 with next row
b.     If no match exists, add row to New List
     i.     Delete added row from Data List
     ii.     Repeat Step 2 with next row

3.     Stop when 60 lines have been filled on New List

4.     Repeat Steps 2&3 from top of Data List until "T" column of 60 consecutive rows on New List cannot be filled with unique values

5.     Stop

Here is the second version:

1.     Insert first row from Data List (Row 2) to first row on New List (Row 61)
a.     Delete first row from DL

2.     Check "T" column of next row on Data List against "T" column in the 59 row's previous the next row to be filled on New List
a.     If match exists, disregard and repeat Step 2 with next row
b.     If no match exists, add row to New List
     i.     Delete added row from Data List
     ii.     Repeat Step 2 with next row

3.     Stop when 60 lines have been added to new list.

4.     Start again with row 2 in the Data List (which should be different than the first time around due to the deletion) and repeat steps 1, 2 & 3, filling in the next 60 rows on the New List.

5.     Repeat Step 4 until it is not possible to fill 60 rows with unique values of "T"

6.     Stop

Ok here we go

 

This covers steps 1, 2 and 3

 

http://wikisend.com/download/531574/For%20John%201.xls

 

Once we get this part finalized then we will go to step 4.

 

I have added serial numbers in columns BF and colored the relevant columns for ease of tracking and testing. We can of course remove them later.

 

Note 1:

This statement "1- Insert first row from Data List (Row 2) to first row on New List (Row 61)" is understood to mean add to first available row on New List (Row 1) and not "first row" on the sheet which is row 1.

 

Note 2:

This statement " 2. Check "T" column of next row on Data List against "T" column in previous 59 row's on New List"" is understood to mean that the checking will always be done against the original 59 rows which do not include the ones that have been recently added.

 

Let me know how it goes.

 

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
We are close. Note 1 is right on.

Note 2:

This statement " 2. Check "T" column of next row on Data List against "T" column in previous 59 row's on New List"" is understood to mean that the checking will always be done against the original 59 rows which do not include the ones that have been recently added.

   - I most definitely want it to check the recently added rows.

Change the quoted text in Note 2 to:

   - 2. Check "T" column of next row on Data List against "T" column in the 59 rows previous the next available row on New List"

Hope that helps. It would be nice if I could italicize or bold or something for emphasis or to differentiate quotes.
Customer reply replied 8 years ago
I guess that should really be changed in the outline.

Here you go. Take 3!

 

http://wikisend.com/download/612446/For%20John%202.xls

 

I hope this one is ok.

 

 

 

John D
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9,666
Experience: Worked on Macs and PC's for 20+ years.
Verified
John D and 87 other Mac Specialists are ready to help you
Ask your own question now
Customer reply replied 8 years ago
Wow. Thank you. It is perfect. This will work and it's worth the payment. Now can you make it repeat through the Data List?
Customer reply replied 8 years ago
Wait a sec... Gotta deposit funds.

Glad to hear that. Thank you very much for the bonus and feedback.

 

The difficult part was to fully understud the requirement.

 

Yes I will work on the remaining part now and will get back to you as soon as I am done.

 

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
You know... With the right package, we could make a mint off this. Are you a programmer?

lol. Yes everything that has to do with excel. In fact this question should have been posted in the programming section, that is where I am mostly at.

 

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
Are you a junior?

I will need an accept to answer this one! lol.

 

Ask Your Own Mac Question

Don,

 

Please check if this one does the looping the way you want it. There was a slight discrepancy in item 4 between the two versions of the outline (i.e. repeat step 2, or repeat steps 1, 2, & 3). So i am not sure if I chose the intended interpretation.

 

http://wikisend.com/download/666260/For%20John%203.xls

 

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
It's perfect. Thank you, XXXXX XXXXX are a gentleman and a scholar. Thnak you for your patience and the great work!

You're very welcome. Hope to see you back if you have other questions in the future.

 

Best!

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
Is there a way to ask for you in the future?

Sure, just write "For John D" in the subject or the body of the question.

 

 

Ask Your Own Mac Question
Customer reply replied 8 years ago
Got it.

When I walked away from my previous firm last month, I walked away from $220K TYD. That was just my cut of the commissions paid my firm for doing just what I'll be doing now.

What you have done for me will increase my productivity when making cold calls by at least 2X; probably more like 3 or 4.

Here's the deal. I'm going to ask you a question at the beginning of 2011. I'll pay you at least .1% of 2010 gross for the answer. Don't plan your retirement or anything, but I will ask the question if you are still in the system.

I wish you and yours all the best in everyting you do.

Best Regards,

Don Palmer, President
Horizon Placement Partners, LLC

Interesting.

 

Thank you for your confidence and good luck with your new business.

 

 

 

 

 

 

Ask Your Own Mac Question
Ask John D Your Own Question
John D
John D
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9,666
9,666 Satisfied Customers
Experience: Worked on Macs and PC's for 20+ years.

John D is online now

A new question is answered every 9 seconds

How JustAnswer works:

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

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

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!

FrankCanada

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 CustomerNew 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!!!!

AlexLos 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.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, NC

< Previous | Next >

Meet the Experts:

Mike

Mike

Mac Medic

8,555 satisfied customers

Over 20 years IT experience with Apple computers in publishing, marketing and design.

Daniel

Daniel

Mac Genius

4,711 satisfied customers

Apple certified on desktop and portable, help desk qualified. Have owned and used Macs since 1989.

Vinod Menon

Vinod Menon

Support Specialist

2,394 satisfied customers

worked as a Tech support Associate for Apple products

John T. F.

John T. F.

Mac Druid

1,994 satisfied customers

20+ years in the computer/Mac industry

David

David

Mac Support Specialist

1,507 satisfied customers

BSc H.Dip Apple Certified Professional

Claws224

Claws224

IEEE Datacommunication Engineer

965 satisfied customers

IEEE, Microsoft

Mac Tech

Mac Tech

Apple Specialist

888 satisfied customers

7 + Years Supporting Apple Products

< Previous | Next >

Related Mac Questions
I have Microsoft office for apple (Mac). I deleted excel and
I have Microsoft office for apple (Mac). I deleted excel and power point, how do I get em back? … read more
Ashik
Ashik
Master\u0027s Degree
5,097 satisfied customers
I have an Excel workbook that opens extremely slow. What can
I have an Excel workbook that opens extremely slow. What can I do to speed it up? Thank you. … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,016 satisfied customers
I have a Mac with Microsoft ( word, powerpoint Excell and
I have a Mac with Microsoft ( word, powerpoint Excell and Outlook)I need to upgrade to outlook 2016. I am having problems as I don't remember my microsoft log in password Help … read more
Ashik
Ashik
Master\u0027s Degree
5,097 satisfied customers
I just purchased the excel-etal. I can't get excel to open.
I just purchased the excel-etal. I can't get excel to open … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,016 satisfied customers
I had to update my Word and Excel recently and I have had
I had to update my Word and Excel recently and I have had nothing but problems every since. I can't Save As two different Excel docs. I try to close them and I get a whirling wheel and "Refreshing" wh… read more
Josh
Josh
It Support Specialist
Associate Degree
5,138 satisfied customers
Microsoft office outlook and excel and word will not open, i
microsoft office outlook and excel and word will not open, i get the message this application will not open correctly with a code Oxc0000142. what must I do to correct ? … read more
Richard
Richard
IT Manager
IT Manager, Consultant
3,269 satisfied customers
I sent my wife and Excell file but we only have access to
I sent my wife and Excell file but we only have access to Mac computer so need to know how to convert it into pages to be able to open it on a MacBook pro … read more
Michelle
Michelle
Manager operations
Bachelor\u0027s Degree
1,275 satisfied customers
I have been using the microsoft one cloud/ excel and word
I have been using the microsoft one cloud/ excel and word apps on my apple Ipad. it is telling me (after two years use) that it can not connect with the internet. Meanwhile all my other apps can? … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,016 satisfied customers
Can I back out an upgrade to Excel without losing files? OS
Can I back out an upgrade to Excel without losing files? … read more
Ashik
Ashik
Master\u0027s Degree
5,097 satisfied customers
I am trying to understand why the Mail merge from Excel to
I am trying to understand why the Mail merge from Excel to Word will not work on my brand new computer??? The excel is saved in the format ".xlsx" but it still says I need an Excel Workbook Text Cover… read more
Richard
Richard
IT Manager
IT Manager, Consultant
3,269 satisfied customers
I just this afternoon updated microsoft office excel but I
I just this afternoon updated microsoft office excel but I don't want it now. I have a backup external hard drive. can i get rid of the update by downloading from time machine. if so, how? … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,016 satisfied customers
I am trying to save updates to an excel spreadsheet on my
I am trying to save updates to an excel spreadsheet on my new 27" Mac that I brought in from a 10 year old Mac. I get a "something went wrong" massage, which recommended that I contact you. … read more
Cabletech
Cabletech
High School or GED
140 satisfied customers
I've used Excel for years with no problems. I have a new
I've used Excel for years with no problems. I have a new iMac desktop with a more current OS and Excel version than I have been using. … read more
Richard
Richard
IT Manager
IT Manager, Consultant
3,269 satisfied customers
I guess I need to update my Excel but not sure how to do it.
I guess I need to update my Excel but not sure how to do it … read more
Richard
Richard
IT Manager
IT Manager, Consultant
3,269 satisfied customers
Excel for Mac opens to a blank Excel file, When I bought the
When Excel opens to a blank file, it also freezes my machine from doing other things, such as logging out, restarting, etc. I've done a forced shutdown (holding down the power button), but this hasn't helped. Same problem exists once I restart the machine. … read more
Russell H.
Russell H.
computer technology consultant
Vocational, Technical or Trade Scho
780 satisfied customers
Im on my moms computer trying to help her. Her excel program
Im on my moms computer trying to help her. Her excel program is not working and will not open at all. I have the original Office.mac dvd but dont know how to reinstall excel without losing whatever da… read more
Ashik
Ashik
Master\u0027s Degree
5,097 satisfied customers
Hanging with Word and Excel - just did updates. No idea. It
It is a Mac mini, i7 with 16 mb ram purchased in 2013, it should be running the latest Mac OS … read more
Ashik
Ashik
Master\u0027s Degree
5,097 satisfied customers
Excel for Mac. The tabs at the bottom of the page have
Excel for Mac. The tabs at the bottom of the page have disappeared. Tried changing preferences as suggested and no change.Thanks.… read more
Jon
Jon
Bachelors Degree
479 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x