Merging new data into tracking spreadsheet

Microsoft Excel 2010
October 1, 2012 at 08:48:34
Specs: Windows 7
Hello there,
I work in a college and track student attendance. I keep a central tracker and each week i get a new report of student data. Currently i paste this onto the end of my spread sheet and then do a sort and go through one by one merging the records together. But i am starting to wonder if there might be an easier way and was wondering if anyone can help me?

The spreadsheet holds at lot of different information but pre merge would be something like:

student week 1 week 2
anna 45%
anna 78%
paul 35%
paul 99%
chris 12%
chris 49%

I can't get this to show properly here, but imagine three columns with the name in the first, and 2 records for each student, 1 percentage appears in the week1 column and the other in the week 2 column. but i need them to appear side by side rather than on duplicate rows.

I am aiming for this:

student week 1 week 2
anna 45% 78%
paul 35% 99%
chris 12% 49%

but i need just one row for each student. I get about 700 each week so if there is a shortcut it would save me so much time. i have found the highlight duplicates in conditional formatting which does help me identify them, but if they can also be merged automatically that would be so great. I have been trying to find answers using excel help but hope you don't mind me asking you here.

Or if you know of a good place i can look for some help that would be equally great.

Emily. x

See More: Merging new data into tracking spreadsheet

Report •

October 1, 2012 at 10:55:21
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post example data in this forum. Those instructions will show you how to line up your data in columns when you post.

As for your question, I think the VLOOKUP function would work for you. Read the Help files on VLOOKUP since I'm sure you'll need to modify these instructions to fit your exact needs. This just a concept, so feel free to just use the ideas to get you thinking.

Let's say you start with this in Sheet1:

        A         B        C       D           etc.
1   Student     Week 1   Week 2   Week 3     etc.
2    anna  
3    paul 
4    chris

Enter Week 1 in Sheet2!A1

Paste your Week 1 data into Sheet2!A2 so that you have this:

      A      B
1  Week 1
2   anna     45% 
3   paul     35%
4   chris    12% 

In Sheet1!B2 enter this formula:

=IF(Sheet2!$A$1 = B$1,VLOOKUP($A2,Sheet2!$A:$B,2,0),"")

Drag it down to the bottom of your list of names and over to Week 52. This should fill in the values for each student for Week 1 and leave empty cells in all the other weeks.

Now do an Edit...Copy...PasteSpecial - Values on Sheet1 Column B to eliminate the formulas and leave the actual values.

Next week (Week 2) paste your new data over the old data starting in Sheet2!A2 and change Sheet2!A1 to read Week 2. This should fill in the Week 2 data on Sheet1 since the Sheet2!A1 now matches Sheet1!C1.

Now do an Edit...Copy...PasteSpecial - Values on Sheet1 Column C to eliminate the formulas and leave the actual values.

Give it a try and let me know what you think.

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

Report •
Related Solutions

Ask Question