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: 9653
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 am making cold calls to big and small offices of individuals.

Customer Question

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.
Submitted: 4 years ago.
Category: Mac
Expert:  John D replied 4 years ago.

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?

 

Customer: replied 4 years ago.
Downloading 2004 now.
Customer: replied 4 years ago.
Wait. That was a stupid update. If that's what I need, I will get it.
Expert:  John D replied 4 years ago.

Let me know when you're ready.

 

Customer: replied 4 years ago.
ok. what i've got is a dinosaur frrankenstein pc running 2003
Expert:  John D replied 4 years ago.

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

 

 

Customer: replied 4 years ago.
Yes.
Expert:  John D replied 4 years ago.

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

 

 

Customer: replied 4 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.
Expert:  John D replied 4 years ago.

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.

 

Customer: replied 4 years ago.
Customer: replied 4 years ago.
"Branch Address" is the address in question.
Customer: replied 4 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: replied 4 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?
Expert:  John D replied 4 years ago.

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, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9653
Experience: Worked on Macs and PC's for 20+ years.
John D and other Mac Specialists are ready to help you
Expert:  John D replied 4 years ago.

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?

 

 

Customer: replied 4 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: replied 4 years ago.
I meant to leave positive feedback for you. You deliver something that works as described and I'll pay you double.
Expert:  John D replied 4 years ago.

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.

 

 

 

Customer: replied 4 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.
Expert:  John D replied 4 years ago.

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,

 

 

 

Customer: replied 4 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?
Expert:  John D replied 4 years ago.

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

 

 

 

 

Customer: replied 4 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?
Expert:  John D replied 4 years ago.

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

 

Customer: replied 4 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.
Expert:  John D replied 4 years ago.

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.

 

 

 

 

 

 

Customer: replied 4 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: replied 4 years ago.
Is there a way we can talk? That way we can be sure to be understood.
(832) (NNN) NNN-NNNN
Expert:  John D replied 4 years ago.

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.

 

 

Expert:  John D replied 4 years ago.

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

 

 

 

 

Customer: replied 4 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.
Expert:  John D replied 4 years ago.

Sounds good.

 

 

Customer: replied 4 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

Expert:  John D replied 4 years ago.

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.

 

 

Customer: replied 4 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: replied 4 years ago.
I guess that should really be changed in the outline.
Expert:  John D replied 4 years ago.

Here you go. Take 3!

 

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

 

I hope this one is ok.

 

 

 

John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9653
Experience: Worked on Macs and PC's for 20+ years.
John D and other Mac Specialists are ready to help you
Customer: replied 4 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: replied 4 years ago.
Wait a sec... Gotta deposit funds.
Expert:  John D replied 4 years ago.

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.

 

 

Customer: replied 4 years ago.
You know... With the right package, we could make a mint off this. Are you a programmer?
Expert:  John D replied 4 years ago.

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.

 

 

Customer: replied 4 years ago.
Are you a junior?
Expert:  John D replied 4 years ago.

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

 

Expert:  John D replied 4 years ago.

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

 

 

Customer: replied 4 years ago.
It's perfect. Thank you, XXXXX XXXXX are a gentleman and a scholar. Thnak you for your patience and the great work!
Expert:  John D replied 4 years ago.

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

 

Best!

 

Customer: replied 4 years ago.
Is there a way to ask for you in the future?
Expert:  John D replied 4 years ago.

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

 

 

Customer: replied 4 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
Expert:  John D replied 4 years ago.

Interesting.

 

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

 

 

 

 

 

 

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's Avatar

    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