Move Data from one worksheet to another based on match

fatnhappy

Board Regular
Joined
Nov 8, 2003
Messages
130
Hello fellow Excelers -

I am looking for a place to get started with my code.....I have a workbook ("Workbook 1") that has data that I need to move to another workbook. Workbook 1 has a date in Cell E3 with names in Column B and the data that needs to be moved in Column E.

I have another sheet "Workbook 2" that has dates in row 2 starting in column B and Names (the same names as workbook 1) in column A. I want to move the data from workbook 1 column E to workbook 2 in the column that has the date match with Cell E3 from Workbook 1 and the data should be put in the row that has a name match in column A with Column B from Workbook 1.

I am learning VBA, and this code will help me get started for what I am looking to do. Please let me know if I can clarify anything.

Thanks for all help.....
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

swayp11

Board Regular
Joined
Apr 27, 2009
Messages
107
Office Version
  1. 365
Platform
  1. Windows
Do you specifically want to do this with code? Because from reading your description, I think a formula would work just fine.
 

fatnhappy

Board Regular
Joined
Nov 8, 2003
Messages
130
Hi Swayp11,

I plan to integrate this code into a larger macro to take the final product and export it to another sheet....I hope to build and integrate the rest of the code myself, I just don't have the knowhow to start the code.

On another note, I would be interested in knowing a function that would work effectively, even though I don't want a function. I am fairly adept to functions and am always looking for other ways of manipulating data. I am not sure how to do a match to find a date based on a particular cell, then shift down a cell and perform a vlookup with a function.....

Thanks
 

swayp11

Board Regular
Joined
Apr 27, 2009
Messages
107
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

fatnhappy,

I can show you both but I don't completely understand the layout of your data. If you could post it or provide a clearer explanation. From what I gather:

Workbook 1: Cell E3 has a date, and Column B has names (let's say from row 1 to 10). Now you also say column E has "data". Is it from row 1 to 10, meaning it matches column B. If that is true, then what about the date in cell E3?

I think what I understand is that on Workbook 1 you have two columns of data: a Names column, and a corresponding data column. Let's call these Column A and B. Then In cell E3 you have a date. Correct?
 

fatnhappy

Board Regular
Joined
Nov 8, 2003
Messages
130
"Workbook 1":

Excel Workbook
ABCDEFG
1*******
2*******
3**WEEK ENDING:*2/13/2010
4**OfficeOmaha*
5*******
6*Name**Amount**
7*Jim*$31,004**
8*Mary*$32,000**
9*Scott*$150,463**
10*Guy*$4,103**
11*Joe*$71,258**
12*Russel*$39,810**
13*Ryan*$32,239**
Sheet1


"Workbook 2":

Excel Workbook
ABCDEFGHIJ
1Recruiters1/2/20101/9/20101/16/20101/23/20101/30/20102/6/20102/13/20102/20/20102/27/2010
2Scott$ * * * * * * - *$ * * * * * * - *$ * * * * * * * - *$ * * * * * * * *- *$ * * * * * * * *- *$ * * * * * * *- *$ * * * * * * * *- *$ * * * * * * * * - *$ * * * * * * * * - *
3Guy$ * * * * * * - *$ * * * * * * - *$ * * * * * * * - *$ * * * * * * * *- *$ * * * * * * * *- *$ * * * * * * *- *$ * * * * * * * *- *$ * * * * * * * * - *$ * * * * * * * * - *
4Jim$ * * * * * * - *$ * * * * * * - *$ * * * * * * * - *$ * * * * * * * *- *$ * * * * * * * *- *$ * * * * * * *- *$ * * * * * * * *- *$ * * * * * * * * - *$ * * * * * * * * - *
5Mary$ * * * * * * - *$ * * * * * * - *$ * * * * * * * - *$ * * * * * * * *- *$ * * * * * * * *- *$ * * * * * * *- *$ * * * * * * * *- *$ * * * * * * * * - *$ * * * * * * * * - *
6Ryan$ * * * * * * - *$ * * * * * * - *$ * * * * * * * - *$ * * * * * * * *- *$ * * * * * * * *- *$ * * * * * * *- *$ * * * * * * * *- *$ * * * * * * * * - *$ * * * * * * * * - *
7Russel$ * * * * * * - *$ * * * * * * - *$ * * * * * * * - *$ * * * * * * * *- *$ * * * * * * * *- *$ * * * * * * *- *$ * * * * * * * *- *$ * * * * * * * * - *$ * * * * * * * * - *
8Joe$ * * * * * * - *$ * * * * * * - *$ * * * * * * * - *$ * * * * * * * *- *$ * * * * * * * *- *$ * * * * * * *- *$ * * * * * * * *- *$ * * * * * * * * - *$ * * * * * * * * - *
Sheet1


The code I am looking for will take the date in workbook 1 (2/13/2010) and compare it to row 1 of Workbook 2. When it finds a match it will offset by one row and perform a vlookup on all the names in workbook 2 and enter the data from workbook 1 that corresponds or 0 if there is no matching name.

Thanks again
 

swayp11

Board Regular
Joined
Apr 27, 2009
Messages
107
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You can use this formula:

Code:
=IF(B$1=[Book1]Sheet1!$E$3:$E$3,IFERROR(INDEX([Book1]Sheet1!$B$7:$E$12,MATCH($A2,[Book1]Sheet1!$B$7:$B$12,0),4),0),"")
That is put into B2 of the Workbook2 (referenced as Book2 in my formula - you would have to replace whatever names you have).

This formula will check if the date matches the date on workbook 1. If it does not match, it puts "" (blank). If the date does match, it then finds the amount for each corresponding name. If the name isn't found, it puts 0.

You can drag this formula across as you add more dates, as well as dragging down for as many names as you want.
 

fatnhappy

Board Regular
Joined
Nov 8, 2003
Messages
130
Thanks for the reply Swayp11,

I forgot to mention that I am using Excel 2003 and I am fairly certain that "iferror" is a 2007 function.

In addition, You probably noticed that "workbook 2" will be used to collect data for the whole year as evident by the dates. what I forgot to mention is that Workbook 1 will be a different workbook every week. Workbook 1 is a download from database in .xls format that is saved after download. So if I used a formula I would have to manipulate the formula in every column. I also think that I would have to open every workbook to calculate each week, so when I got to the 50th week, would this would get a little cumbersome?

Thanks so much for your help, It is much appreciated. I thought a macro would be easier and cleaner. With this new information, would you agree, or do you see a better way?
 

swayp11

Board Regular
Joined
Apr 27, 2009
Messages
107
Office Version
  1. 365
Platform
  1. Windows
Honestly, I think that a remake of how you organize the data will make it the most easy for you.

You can have a macro prompt you which workbook to search for and then use reference formulas to retrieve whichever data you want, but I think this is getting a little more complicated (and more prone to error) than you want.

I think you should have a workbook that contains ALL your data. Each week you can add to the file and save it. Then, all of your formulas can reference this one master workbook (or you can do the formulas in the same workbook and reference the sheet that contains all the data - so simply working with one workbook only, and putting your tables and formulas on one sheet).

Setting it up like such makes it easy to use all reference formulas, and simply use a pivot table to look at the data in any way you want.

PM me if you need more help on the above topics.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,305
Messages
5,600,867
Members
414,410
Latest member
4610

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top