Solved How can I copy matching data and to a new sheet format

Microsoft Excel 2003 (full product)
July 5, 2012 at 13:35:46
Specs: Windows XP, 2Ghz / 4GB
I currently have a data sheet listing all staff, dates and hours they worked. The sheet averages about 1000 rows of data relating to 60 staff and looks like this:

A B C D E
1 StaffNo Name Date Type Time
2 1234 Smith 6 Jan 12 A 1
3 1234 Smith 8 Jan 12 A 0.25
4 1234 Smith 19 Jan 12 A 0.75
5 6700 James 8 Jan 12 A 1
6 5478 Walton 6 Jan 12 A 0.5
7 5478 Walton 19 Jan 12 A 1

I need to have a second sheet showing the data almost as a diary so it would have 60 rows, I per employee but with numerous columns each headed with a given date. I have created the 2nd sheet and listed all the employees and the format looks like the table below:

A B C D E F G
1 StaffNo Name 6 Jan 12 8 Jan 12 9 Jan 12 16 Jan 12 19 Jan 12
2 1234 Smith
3 6700 James
4 5478 Walton
5 6592 Able
6 2465 Wilson
7 7829 Skinner

What I would like to know is how do I get excel to look for matching employee numbers from sheet2 to sheet1 and also matching dates so that Sheet 2 cells C2 to XX1000 will be populated with the data from Sheet1 Column E if the date matches the date in the row header of sheet2. The end result looking like this:

A B C D E F G
1 StaffNo Name 6 Jan 12 8 Jan 12 9 Jan 12 16 Jan 12 19 Jan 12
2 1234 Smith 1 0.25 0.75
3 6700 James 1
4 5478 Walton 0.5 1
5 6592 Able
6 2465 Wilson
7 7829 Skinner

I have tried using the Match and Isna but can't workout how to do the match for 2 different items cells and only then take the data from the source.


See More: How can I copy matching data and to a new sheet format

Report •

#1
July 5, 2012 at 14:13:11
Please click on the blue line at the end of this post and read the instructions on how to post data in this forum.

Then repost your data so we can how the columns are supposed to line up.

Thanks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 6, 2012 at 01:09:12
How can I copy matching data and associated data cells to new sheet format
Moving data from 1 sheet to another in a different format
I currently have a data sheet listing all staff, dates and hours they worked.  The sheet averages about 1000 rows of data relating to 60 staff and looks like this:
	A	B	C	        D	E
1	StaffNo	Name	Date	        Type    Time
2	1234	Smith	6 Jan 12	A	1
3	1234	Smith	8 Jan 12	A	0.25
4	1234	Smith	19 Jan 12	A	0.75
5	6700	James	8 Jan 12	A	1
6	5478	Walton	6 Jan 12	A	0.5
7	5478	Walton	19 Jan 12	A	1

I need to have a second sheet showing the data almost as a diary so it would have 60 rows, I per employee but with numerous columns each headed with a given date.  I have created the 2nd sheet and listed all the employees and the format looks like the table below:
    A	    B	        C	  D	    E          F    	  G
1  StaffNo  Name     6 Jan 12  8 Jan 12  9 Jan 12  16 Jan 12  19 Jan 12
2  1234	    Smith					
3  6700	    James					
4  5478	    Walton					
5  6592	    Able					
6  2465	    Wilson					
7  7829	    Skinner					

What I would like to know is how do I get excel to look for matching employee numbers from sheet2 to sheet1 and also matching dates so that Sheet 2 cells C2 to XX1000 will be populated with the data from Sheet1 Column E if the date matches the date in the row header of sheet2.  The end result looking like this:
   A	     B	        C   	   D	      E	          F	      G
1  StaffNo  Name     6 Jan 12   8 Jan 12   9 Jan 12   16 Jan 12   19 Jan 12
2  1234	    Smith	1	  0.25			             0.75
3  6700	    James		  1			
4  5478	    Walton	0.5				             1
5  6592	    Able					
6  2465	    Wilson					
7  7829	    Skinner					


Report •

#3
July 6, 2012 at 08:26:58
✔ Best Answer
First, another posting tip:

When you use the pre tags to post data, please do not include the text of your post within the tags.

As you can see from your post, you end up with long lines of text that make the post hard to read.

In the future, please only include the example data within the pre tags.

One solution to your task is to use a "helper column" and the VLOOKUP function.

1 - On Sheet1, insert a new Column A.

2 - In A2 enter this formula, which should return Smith40914. The goal is to combine the Name and the Date in one cell for use by the VLOOKUP function later.

=C2&D2

3 - Drag this down to A7.

4 - Hide Column A if you wish.

5 - In Sheet2, enter this formula in C2, which should return 1, the value at the intersection of Smith and Jan 6, 2012.

=VLOOKUP($B2&C$1,Sheet1!$A$2:$F$7,6,0)

This works by combining the name in B2 with the date in C1 and then using VLOOKUP to return the value from Column 6 of the Sheet1!$A$2:$F$7 array.

6 - If you drag the formula down to B7 and then over to G7, you should get the values you are looking for along with a bunch of #N/A errors wherever VLOOKUP couldn't find a match in Column A.

7 - To eliminate the #N/A errors, use an IF(ISNA... function as follows:

Put this in Sheet2!C2, then drag it down and across to G7:

=IF(ISNA(VLOOKUP($B2&C$1,Sheet1!$A$2:$F$7,6,0)),"",
VLOOKUP($B2&C$1,Sheet1!$A$2:$F$7,6,0))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 24, 2012 at 10:07:08
Sorry for late response. TY for the advice on formating. The solution you offered worked a treat.

Report •

Ask Question