Move Data from one worksheet to another based on match

[fatnhappy]

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

 

[fatnhappy]

Bump

 

[swayp11]

Do you specifically want to do this with code? Because from reading your description, I think a formula would work just fine.

 

[fatnhappy]

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]

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]

"Workbook 1":

Excel Workbook
	A	B	C	D	E	F	G
1	*	*	*	*	*	*	*
2	*	*	*	*	*	*	*
3	*	*	WEEK ENDING:	*	2/13/2010
4	*	*	Office			Omaha	*
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
	A	B	C	D	E	F	G	H	I	J
1	Recruiters	1/2/2010	1/9/2010	1/16/2010	1/23/2010	1/30/2010	2/6/2010	2/13/2010	2/20/2010	2/27/2010
2	Scott	$ * * * * * * - *	$ * * * * * * - *	$ * * * * * * * - *	$ * * * * * * * *- *	$ * * * * * * * *- *	$ * * * * * * *- *	$ * * * * * * * *- *	$ * * * * * * * * - *	$ * * * * * * * * - *
3	Guy	$ * * * * * * - *	$ * * * * * * - *	$ * * * * * * * - *	$ * * * * * * * *- *	$ * * * * * * * *- *	$ * * * * * * *- *	$ * * * * * * * *- *	$ * * * * * * * * - *	$ * * * * * * * * - *
4	Jim	$ * * * * * * - *	$ * * * * * * - *	$ * * * * * * * - *	$ * * * * * * * *- *	$ * * * * * * * *- *	$ * * * * * * *- *	$ * * * * * * * *- *	$ * * * * * * * * - *	$ * * * * * * * * - *
5	Mary	$ * * * * * * - *	$ * * * * * * - *	$ * * * * * * * - *	$ * * * * * * * *- *	$ * * * * * * * *- *	$ * * * * * * *- *	$ * * * * * * * *- *	$ * * * * * * * * - *	$ * * * * * * * * - *
6	Ryan	$ * * * * * * - *	$ * * * * * * - *	$ * * * * * * * - *	$ * * * * * * * *- *	$ * * * * * * * *- *	$ * * * * * * *- *	$ * * * * * * * *- *	$ * * * * * * * * - *	$ * * * * * * * * - *
7	Russel	$ * * * * * * - *	$ * * * * * * - *	$ * * * * * * * - *	$ * * * * * * * *- *	$ * * * * * * * *- *	$ * * * * * * *- *	$ * * * * * * * *- *	$ * * * * * * * * - *	$ * * * * * * * * - *
8	Joe	$ * * * * * * - *	$ * * * * * * - *	$ * * * * * * * - *	$ * * * * * * * *- *	$ * * * * * * * *- *	$ * * * * * * *- *	$ * * * * * * * *- *	$ * * * * * * * * - *	$ * * * * * * * * - *
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]

You can use this formula:

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

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]

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.