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.

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

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)

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.

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'

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.

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?

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)

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?

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

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)

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.

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-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, BillBill M.Schenectady, New York

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, BillBill 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

Meet The Experts:

Mike

Mac Medic

Satisfied Customers:

6256

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