Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find and paste formula
Alright everyone, I know very very little about programing a function in Excel. Now that we have that out of the way this is what I'm looking at. My boss gave me two excel sheets one is 626 rows and the other is 13000 rows and 256 columns. My job is to take a gene ID(listed in document 1), search for it in document two, then once it is found it needs to identify the cell in column A of what ever row it was found in and paste it back in document 2. Document 1's set up isn't important, but document 2 is setup as such: Every cell in column A is the name that I need pasted, and in the rest of the cells in that row is where I will find the gene ID from document 1. Let me write an example. ___A________B______C_______D_______ TC38123 CB61619 CD78923 DG909288 TC90123 CD99203 JR18239 JX920347 And document 2 continues on in this manner for a very long time. So lets say I'm searching for CD78923 it will be found in C1, then I will look in column A of that same row to find the ID TC38123, then I paste that back in document 2. That is what I need a formula for and so I ask you all for your help, because if I can't get a formula then I have to do it all by hand... And I already posted this is Programming but I think that was the wrong place for it. -- Taru ------------------------------------------------------------------------ Taru's Profile: http://www.excelforum.com/member.php...o&userid=35494 View this thread: http://www.excelforum.com/showthread...hreadid=552684 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find and paste formula
I think this might work for you. In document 1, Column B, enter this formula
(change file and sheet names as needed): =INDEX([Document2.xls]Sheet1!$A$1:$A$13000,MAX(([Document2.xls]Sheet1!B$1:$D$13000=A1)*(ROW([Document2.xls]Sheet1!B$1:$D$13000)))) Enter this as an array formula. Use CTRL-SHIFT-ENTER instead of just Enter. Copy the formula down Column B for each value in Column A. HTH, Elkar "Taru" wrote: Alright everyone, I know very very little about programing a function in Excel. Now that we have that out of the way this is what I'm looking at. My boss gave me two excel sheets one is 626 rows and the other is 13000 rows and 256 columns. My job is to take a gene ID(listed in document 1), search for it in document two, then once it is found it needs to identify the cell in column A of what ever row it was found in and paste it back in document 2. Document 1's set up isn't important, but document 2 is setup as such: Every cell in column A is the name that I need pasted, and in the rest of the cells in that row is where I will find the gene ID from document 1. Let me write an example. ___A________B______C_______D_______ TC38123 CB61619 CD78923 DG909288 TC90123 CD99203 JR18239 JX920347 And document 2 continues on in this manner for a very long time. So lets say I'm searching for CD78923 it will be found in C1, then I will look in column A of that same row to find the ID TC38123, then I paste that back in document 2. That is what I need a formula for and so I ask you all for your help, because if I can't get a formula then I have to do it all by hand... And I already posted this is Programming but I think that was the wrong place for it. -- Taru ------------------------------------------------------------------------ Taru's Profile: http://www.excelforum.com/member.php...o&userid=35494 View this thread: http://www.excelforum.com/showthread...hreadid=552684 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find and paste formula
Hi Taru,
If you always want to look for the number in column C then you can do a Vlookup. Try reading about that and if you are still stuck. let me know. ~Jessica "Taru" wrote: Alright everyone, I know very very little about programing a function in Excel. Now that we have that out of the way this is what I'm looking at. My boss gave me two excel sheets one is 626 rows and the other is 13000 rows and 256 columns. My job is to take a gene ID(listed in document 1), search for it in document two, then once it is found it needs to identify the cell in column A of what ever row it was found in and paste it back in document 2. Document 1's set up isn't important, but document 2 is setup as such: Every cell in column A is the name that I need pasted, and in the rest of the cells in that row is where I will find the gene ID from document 1. Let me write an example. ___A________B______C_______D_______ TC38123 CB61619 CD78923 DG909288 TC90123 CD99203 JR18239 JX920347 And document 2 continues on in this manner for a very long time. So lets say I'm searching for CD78923 it will be found in C1, then I will look in column A of that same row to find the ID TC38123, then I paste that back in document 2. That is what I need a formula for and so I ask you all for your help, because if I can't get a formula then I have to do it all by hand... And I already posted this is Programming but I think that was the wrong place for it. -- Taru ------------------------------------------------------------------------ Taru's Profile: http://www.excelforum.com/member.php...o&userid=35494 View this thread: http://www.excelforum.com/showthread...hreadid=552684 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Effective method to paste array formula | Excel Worksheet Functions | |||
Macro to find, copy, and paste until value change | Excel Worksheet Functions | |||
convert formula to its value w/out copy and paste | Excel Discussion (Misc queries) | |||
Find text in another workbook and paste if found match - VBA | Excel Discussion (Misc queries) | |||
Cannat paste that macro formula onto a worksheet | Excel Discussion (Misc queries) |