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

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

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