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: 9658
Experience:  Worked on Macs and PC's for 20+ years.
53785
Type Your Mac Question Here...
John D is online now
A new question is answered every 9 seconds

I need to write a simple macro using a look-up spreadsheet

Customer Question

I need to write a simple macro using a look-up spreadsheet in Excel 08. Will this be possible on my iMac? Do I do it in excel or with AppleScript?
Submitted: 3 years ago.
Category: Mac
Expert:  John D replied 3 years ago.
Hi,

Excel 2008 is the only version of Excel that does not support vba macros. If you have access another version of Excel or if you intend to upgrade I will be happy to write that macro for you

Or we could try accomplishing this with formulas only.


Customer: replied 3 years ago.
I can upgrade to 2010 but I'll have to go to the Apple store or the university bookstore to purchase it. Can you wait a couple of hours?
Expert:  John D replied 3 years ago.

Sure, just bookmark this page so you can easily get beck to it

 

 

Customer: replied 3 years ago.
I've installed excel 2011. I havw the spreadsheets open. I'm ready
Expert:  John D replied 3 years ago.

Great!

 

Now please send me the file that you want to install the macro on, together with the instructions so I can create the macro for you.

 

To send the file go to http://www.wikisend.com/ and upload the file then copy the File ID that you will be given and paste it here in your reply.

 

If the file has sensitive information let me know before you upload it

 

John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9658
Experience: Worked on Macs and PC's for 20+ years.
John D and 9 other Mac Specialists are ready to help you
Customer: replied 3 years ago.
There are two files, SCCH&N19 and SCCH&N20. I need to write a macro on 20 using 19 as a lookup table. The link between the 2 is the accession numbers. I need the FoldChange value from 19 written into the FC column of 20 for each accession number common to both spreadsheets. About half pf them should link. I'm writing this on my iPad so it will take me a little while to plug the link into my iBook. Give me 10 min.
Customer: replied 3 years ago.
There are two files, SCCH&N19 and SCCH&N20. I need to write a macro on 20 using 19 as a lookup table. The link between the 2 is the accession numbers. I need the FoldChange value from 19 written into the FC column of 20 for each accession number common to both spreadsheets. About half pf them should link. I'm writing this on my iPad so it will take me a little while to plug the link into my iBook. Give me 10 min.
Http://wikisend.com/download/933528/SCC and 472380/SCC
Expert:  John D replied 3 years ago.

Ok I got the first file, i.e. SCCH&N19_2p.xls

 

Will wait for the second file..

 

 

Customer: replied 3 years ago.
Did you get the second file?
Expert:  John D replied 3 years ago.

No. You did not send me the File ID for the second file yet

 

 

 

Customer: replied 3 years ago.
Lost in cyberspace. Illswms it again.
608848/SCC
Let me know if it flew.
Expert:  John D replied 3 years ago.

Got it, thanks.

 

Will get back to you as soon as I am done

 

Expert:  John D replied 3 years ago.

Ok here you go

 

I manged to make it work with formulas only. This should simplify the process of enabling macros by all users and it will not require user intervention to run the macro as the formulas automatically return the updated results

 

Here are the two files. The formals are in the "SCCH&N20_2p 1.xls" (yellow cells)

 

http://wikisend.com/download/523944/SCCH&N19_2p%201.xls

 

http://wikisend.com/download/601376/SCCH&N20_2p%201.xls

 

When you download the files first save them in to the same folder (before opening them), then open the 19 file first, then the 20.

 

Hope this is ok. Let me know if you have any questions

 

 

Customer: replied 3 years ago.
OK.
I added a note yesterday about the accession numbers. Only the real numbers should be looked at and -R. The stuff in brackets is variable and should be disregarded. Perhaps we need to write a separate Macro to clean up the numbers.
Expert:  John D replied 3 years ago.

Ok have a look at this file. I modified the formula so it ignores any character that comes after the 11 digit accession number. The new formulas are 'array formulas'

 

http://wikisend.com/download/452694/SCCH&N20_2p%201a.xls

 

Let me know

 

 

Customer: replied 3 years ago.
OK, but I don't know what to do with it. When I open it I have a page -full of machine language.
Customer: replied 3 years ago.
OK
This weekend I spent about 6 hours going through both spreadsheets line-by-line. The formula worked. The transfers are 100% on. However I want to try it on a couple of other studies to make sure it works for me every time. It will take me a couple more days to work through these so I'll probably have some questions. Hang on.
Expert:  John D replied 3 years ago.
Great!.

Sure. Let me know if you have any questions
Customer: replied 3 years ago.
I tried the "array formula" on a second set of spreadsheets over the weekend but couldn't make it work. Obviously I don't understand how to apply it or how it should be modified for the new data. Can you help me?
Expert:  John D replied 3 years ago.

Hi again

 

Ok 'Array formulas' must be entered by pressing Ctrl+Shift+Enter (CMD + SHIFT + RETURN on the Mac) keys instead of just the Enter key. If you do it correctly then Excel will automatically place curly brackets around the formula { }. You cannot type these brackets yourself. When you edit the formula you must enter it again with Ctrl+Shift+Enter (CMD + SHIFT + RETURN on the Mac)

 

Hope this helps

 

 

Customer: replied 3 years ago.
Below is your formula followed by the one I wrote. Is it OK? It was hard to get it right and there are colons in yours which my computer wanted to do as forward slashes. Are these interchangeable?


=IF(ISERROR(VLOOKUP(LEFT(C2,11),LEFT('Macintosh HD:Users:Lawrence:Desktop:SCCH&N:[SCCH&N19_2p 1.xls]SCCH&N19_2p.xls'!$D$2:$E$303,11),2,0)),"",VALUE(VLOOKUP(LEFT(C2,11),LEFT('Macintosh HD:Users:Lawrence:Desktop:SCCH&N:[SCCH&N19_2p 1.xls]SCCH&N19_2p.xls'!$D$2:$E$303,11),2,0)))
 
=IF(ISERROR(VLOOKUP(LEFT(C2,11),LEFT(‘Macintosh HD:Users:Lawrence:Desktop:COMPARISON SF2_SF3:STS_118_2BIOS_2p.xls'!$D$2:$E$303,11),2,0)),"",VALUE(VLOOKUP(LEFT(C2,11),LEFT(‘Macintosh HD:Users:Lawrence:Desktop:COMPARISON SF2_SF3:STS_118_2BIOS_2p.xls'!$D$2:$E$303,11),2,0)))
 
Customer: replied 3 years ago.
So, assuming I've got the formula right, I copied it, pasted it in the E2 cell, and hit command-shift-return. Nothing happened.
Expert:  John D replied 3 years ago.

Could you please send me the file with the two formulas so I can check them for you on your data

 

Customer: replied 3 years ago.
I'll upload the two spreadsheets as before. They're set up the same as the SCCH&N ones bit this time they're called FLT_GRD_10bios_2p_20110309 (this is the home one) and STS-_118_2BIOS_2p (this is the lookup). It will take me a few minutes because I have to put them on iDisk to move them to my iPad.
Customer: replied 3 years ago.
I'll upload the two spreadsheets as before. They're set up the same as the SCCH&N ones bit this time they're called FLT_GRD_10bios_2p_20110309 (this is the home one) and STS-_118_2BIOS_2p (this is the lookup). It will take me a few minutes because I have to put them on iDisk to move them to my iPad.

I can't make Wikisend work
Customer: replied 3 years ago.
2 files sent. Ok?
Expert:  John D replied 3 years ago.

Where are the download links or File ID's so I can download the files?

 

 

Customer: replied 3 years ago.
I'm not sure what you need. I sent them through Wikisend using Lsandberg as the user name. The file names are as I've listed them above. FLT_GRD..... and STS_118.......
Expert:  John D replied 3 years ago.

No we can't use file names to locate the file on that site. There are millions of files hosted there and that is why they require a file id, as such

 

Attachments are only available to registered users.

Register Here

Attachments are only available to registered users.

Register Here

Attachments are only available to registered users.

Register Here
View Full Image

Attachments are only available to registered users.

Register Here

 

 

Customer: replied 3 years ago.
Ok. I'll send them again and watch for the ID.
Customer: replied 3 years ago.
Ok. I'll send them again and watch for the ID.

FLT_GRD......ID 947606

STS-_118.....ID 372574
Expert:  John D replied 3 years ago.

Ok I downloaded the files but I do not see any instructions or highlighted cells to look at. I am not sure what needs to be done. It has been a while since we worked on this question and I can't quite remember the issue (I work on 100's of files every week)

 

Is this meant to be a new question?

 

 

 

 

 

Customer: replied 3 years ago.
It's the same manipulation you did on the first two files. FLT_GRD has an empty column called FC. STS_118 has a FC column (fold change) with data. It is the look-up for supplying the FC data to FLT_GRD. Because these are new spreadsheets and because I can't make your first formula apply to these, this might be considered a new question. But you must show me how to make the formula work on future spreadsheets because I will have many of them.
Customer: replied 3 years ago.
It's the same manipulation you did on the first two files. FLT_GRD has an empty column called FC. STS_118 has a FC column (fold change) with data. It is the look-up for supplying the FC data to FLT_GRD. Because these are new spreadsheets and because I can't make your first formula apply to these, this might be considered a new question. But you must show me how to make the formula work on future spreadsheets because I will have many of them.
If you did this with macros I think it would be easier for me to understand.
Customer: replied 3 years ago.
To refresh your memory, I've uploaded the two files you worked on originally so you can see the formula: IDs 881800 and 305498.
Also, as you may recall, the 11 digit accession # is the link between the 2. What follows in brackets, etc. Is to be ignored because these are variable. Thanks for your patience with a novice like myself.
Expert:  John D replied 3 years ago.

Got it. Thanks

 

Will work on it a bit later in the day and will get back to you as soon as possible

 

 

Expert:  John D replied 3 years ago.

Ok here you go

 

http://www.2shared.com/file/8qSDlwKI/2_files.html

 

Again please make sure to place the 2 extracted files in the same folder, then open the STS_118_2BIOS_2p.xls file first so the other file gets updated properly

 

Hope this is ok. Let me know if you need further help with these formulas or if you have any questions, and thank you for clicking Accept if you are satisfied with the solution

 

 

Expert:  John D replied 3 years ago.

Were you able to download the file. Waiting for your feedback

 

 

 

Customer: replied 3 years ago.
I'm out of town for the weekend so I didn't get a chance to try it on my big Mac. I'll get back to you Monday.
Expert:  John D replied 3 years ago.

No problem. Take your time

 

Expert:  John D replied 3 years ago.

Hi,

 

Any progress on this?

 

 

 

Customer: replied 3 years ago.
Still working on it. I need couple more days.

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