Mac

Ask Mac Questions and Get Answers from Experts ASAP

Ask a Computer Expert,
Get an Answer ASAP!

Mac

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

Customer Question
in Excel 08. Will this...
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: 6 years ago.Category: Mac
Show More
Show Less
Ask Your Own Mac Question
Answered in 3 minutes by:
5/3/2011
Mac Support Specialist: John D, BS Degree in Engineering replied 6 years ago
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,

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.


Ask Your Own Mac Question
Customer reply replied 6 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?
Mac Support Specialist: John D, BS Degree in Engineering replied 6 years ago

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

 

 

Ask Your Own Mac Question
Customer reply replied 6 years ago
I've installed excel 2011. I havw the spreadsheets open. I'm ready
Mac Support Specialist: John D, BS Degree in Engineering replied 6 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
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 6 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 reply replied 6 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
Mac Support Specialist: John D, BS Degree in Engineering replied 6 years ago

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

 

Will wait for the second file..

 

 

Ask Your Own Mac Question
Customer reply replied 6 years ago
Did you get the second file?
Mac Support Specialist: John D, BS Degree in Engineering replied 6 years ago

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

 

 

 

Ask Your Own Mac Question
Customer reply replied 6 years ago
Lost in cyberspace. Illswms it again.
608848/SCC
Let me know if it flew.
Mac Support Specialist: John D, BS Degree in Engineering replied 6 years ago

Got it, thanks.

 

Will get back to you as soon as I am done

 

Ask Your Own Mac Question
Mac Support Specialist: John D, BS Degree in Engineering replied 6 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

 

 

Ask Your Own Mac Question
Customer reply replied 6 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.
Mac Support Specialist: John D, BS Degree in Engineering replied 6 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

 

 

Ask Your Own Mac Question
Customer reply replied 6 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.
Mac Support Specialist: John D, BS Degree in Engineering replied 6 years ago
Ask Your Own Mac Question
Customer reply replied 6 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.
Mac Support Specialist: John D, BS Degree in Engineering replied 6 years ago
Great!.

Sure. Let me know if you have any questions
Ask Your Own Mac Question
Customer reply replied 6 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?
Mac Support Specialist: John D, BS Degree in Engineering replied 6 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

 

 

Ask Your Own Mac Question
Customer reply replied 6 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 reply replied 6 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.
Mac Support Specialist: John D, BS Degree in Engineering replied 6 years ago

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

 

Ask Your Own Mac Question
Customer reply replied 6 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 reply replied 6 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 reply replied 6 years ago
2 files sent. Ok?
Mac Support Specialist: John D, BS Degree in Engineering replied 6 years ago

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

 

 

Ask Your Own Mac Question
Customer reply replied 6 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.......
Mac Support Specialist: John D, BS Degree in Engineering replied 6 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

 

 

 

Ask Your Own Mac Question
Customer reply replied 6 years ago
Ok. I'll send them again and watch for the ID.
Customer reply replied 6 years ago
Ok. I'll send them again and watch for the ID.

FLT_GRD......ID 947606

STS-_118.....ID 372574
Mac Support Specialist: John D, BS Degree in Engineering replied 6 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?

 

 

 

 

 

Ask Your Own Mac Question
Customer reply replied 6 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 reply replied 6 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 reply replied 6 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.
Mac Support Specialist: John D, BS Degree in Engineering replied 6 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

 

 

Ask Your Own Mac Question
Mac Support Specialist: John D, BS Degree in Engineering replied 6 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

 

 

Ask Your Own Mac Question
Mac Support Specialist: John D, BS Degree in Engineering replied 6 years ago

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

 

 

 

Ask Your Own Mac Question
Customer reply replied 6 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.
Mac Support Specialist: John D, BS Degree in Engineering replied 6 years ago

No problem. Take your time

 

Ask Your Own Mac Question
Mac Support Specialist: John D, BS Degree in Engineering replied 6 years ago

Hi,

 

Any progress on this?

 

 

 

Ask Your Own Mac Question
Customer reply replied 6 years ago
Still working on it. I need couple more days.
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
My iBook will not turn on. Just tried both no go. Yellow
My iBook will not turn on JA: Have you tried a PRAM reset on your iBook (rebooting and holding down Command, Option, P, and R)? Or rebooting in Recovery Mode (holding down Command and R)? Customer: Ju… read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,088 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,088 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,088 satisfied customers
Suddenly my password for purchasing iBooks, installing
Suddenly my password ***** purchasing iBooks, installing Ttube, etc. is not being accepted. … read more
John T. F.
John T. F.
Mac Druid
Two Decades Field Experience
1,994 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,275 satisfied customers
I can't find iBooks on my iPad. The latest one. I know it
I know it was here before but I have no idea when iBooks disappears but … read more
Richard
Richard
IT Manager
IT Manager, Consultant
3,275 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
781 satisfied customers
I got into IBooks on my iPad, i have no idea how to get out
I got into IBooks on my iPad, i have no idea how to get out of it. … read more
Kamil Anwar
Kamil Anwar
IT Consultant / Systems Engineer
Bachelor\u0027s Degree
433 satisfied customers
Installed IOS11 now iBook most often does not open the books
Installed IOS11 now iBook most often does not open the books or does it very slow. Sometimes it can open up and then the whole app closes. … read more
Richard
Richard
IT Manager
IT Manager, Consultant
3,275 satisfied customers
I cannot get an ibooks purchase approved and I am entering
I cannot get an ibooks purchase approved and I am entering the correct Apple account password. On one try a message indicated that I should enter both the id and password ***** there is only space in … read more
Kris R
Kris R
IT Manager
Computing
2,648 satisfied customers
The ibooks screen is frozen. How do I reset the device? IPad
The ibooks screen is frozen. How do I reset the device? … read more
D.Sandlin
D.Sandlin
Support Technician
6,708 satisfied customers
I have over 1000 books on my iBooks, when I go to read a
I have over 1000 books on my iBooks, when I go to read a book I've already purchased it comes up with no contents started about two days ago. Please help! … read more
Mr.Med
Mr.Med
942 satisfied customers
It started with an Ibook download that seemed to affect my
It started with an Ibook download that seemed to affect my Apple Imac all our appointments in calender are wiped on the Imac but not on the Ibook. … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,088 satisfied customers
I purchased in ibooks, last friday (the 14th), 2 books I
Hi, I purchased in ibooks, last friday (the 14th), 2 books I intended to offer to a brother of mine. I managed wrong the app since I thought I could send the link to him by email. A nd so I did send t… read more
JJesus Perez Gonzale
JJesus Perez Gonzale
Bachelors of Science, Computer Engineering
37 satisfied customers
I ordered a book from iBook that will not down load. I have
I ordered a book from iBook that will not down load. I have the "read" message, but only … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,088 satisfied customers
IBooks is frozen, cannot use, iPad don't know OS, Don't know
iBooks is frozen, cannot use JA: What's the model and Operating System (OS) of your device? Customer: iPad don't know OS JA: When did you purchase your iPad? What troubleshooting have you tried? Custo… read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,088 satisfied customers
I have just started an Ibook having been frustrated with
I have just started an Ibook having been frustrated with Blurb. How do I proceed to turn the pages and continue. I have just done the cover only but there is no easy way to go on to the next page. Tha… read more
Jay D
Jay D
Bachelor of Science
501 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