1. Combining data (XP)

Hello again -

A co-worker has 2 workbooks - 1 worksheet in each. Each workbook has student #, student last name, student first name, test date and score. Book 1 has a test date of 1/15/04 for all students. Book 2 has a test date of 10/23/03 for all students. Obviously each student has a different score for the 03 date than the 03 date. The other hitch is that by 1/15/04 students had enrolled and taken the test for the first time, and students had dropped out and not taken the test the 2nd time. We need a single sheet showing student #, last name, first name, 10/23/03 and each student's score and 1/15/04 and each student's score.

I'm sure there is an easy way to combine this data to show the results I need - but it isn't coming to me!!! Of course we need this ASAP - thanks again!

2. Re: Combining data (XP)

Could you upload a small workbook that shows the two worksheets with some dummy data?

3. Re: Combining data (XP)

For an absolutely low-tech solution - so long as the number of students is not huge - e.g. 500 or so
Do it by hand <img src=/S/duck.gif border=0 alt=duck width=23 height=23>

* Sort each sheet in order by student
* Copy sheet B to the RHS of sheet A
* Inspect by eye, and keep rows synchronised by using insert cells to cause either Block A or Block B to move down (not hard to link a learnt Macro to a hotkey for A vs B (and if you're a VBA wizz could be coded as a loop to insert where needed)
* When finished. delete strange columns and tidy-up

10 minutes tops, and possibly quicker than most alternatives (but not as much fun) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

4. Re: Combining data (XP)

Combine them into 1 workbook with the columns:
student #
student last name
student first name
test date
score

They can be in any order, though you can sort if you want or even add an autofilter

Then use a pivot table to summarize. First/last name as row field, date as column field, average of score as data

You can just add more rows as new tests are done. and just update the pivot. This will give the individual and the overall average score.

Steve

5. Re: Combining data (XP)

Nice <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

6. Re: Combining data (XP)

Here 'tis a small sample - there are over 1400 entries on each actual worksheet.

Thanks - again.

Linda

7. Re: Combining data (XP)

See if the code below does what you want. You will need to modify the Set oWS1 = and the Set oWS2 = statements for the correct workbook and worksheet names.

<pre>Public Sub CombineSheets()
Dim oWS1 As Worksheet, oWS2 As Worksheet
Dim I As Long, j As Long, k As Long
Set oWS1 = Workbooks("~~Combinedocumenttest1.xls").Worksheets ("Sheet4")
Set oWS2 = Workbooks("~~Combinedocumenttest1.xls").Worksheets ("Sheet1")
For I = 1 To oWS2.Range("A65536").End(xlUp).Row - 1
For j = 1 To oWS1.Range("A65536").End(xlUp).Row - 1
If oWS2.Range("A1").Offset(I, 0).Value = oWS1.Range("A1").Offset(j, 0).Value Then
Exit For
End If
Next j
If j > oWS1.Range("A65536").End(xlUp).Row - 1 Then
Range(oWS2.Range("A1").Offset(I, 0), oWS2.Range("I1").Offset(I, 0)).Copy
oWS1.Paste Destination:=oWS1.Range("A1").Offset(j, 0)
Else
Range(oWS2.Range("F1").Offset(I, 0), oWS2.Range("I1").Offset(I, 0)).Copy
oWS1.Paste Destination:=oWS1.Range("J1").Offset(j, 0)
End If
Next I
End Sub
</pre>

8. Re: Combining data (XP)

I'm going to save this for someday when I have a while 'cause I really want to learn how to write stuff like this - right now I'm so swamped it's hard to see daylight.

Thanks sooo much again for trying to help me - like I said B4, I took the long road and got her something she could use - actually not too bad, considering. I did the copy, paste, sort - then used conditional formatting to make the alternate test date stand out and put the seond score in a column adjacent to the first score.

Unless I'm missing something, there's no way to make a pivot table with 1400 separate and distinct fields?????

Again - my gratitude

Aunt Linda

9. Re: Combining data (XP)

In 2002 I just did a pivot of 2000 unique elements - I am unaware of a limit

10. Re: Combining data (XP)

I apologize - I know you CAN do it - how do you ever display that many elements in a summarized format?

Linda

11. Re: Combining data (XP)

You only have 9 distinct fields. Pivots in XL97 allow 8000 unique items. Other (later versions) extended this. Do you have over 8,000 students?

If you don't want a table of studint info and then the scores, I guess I am confused about what you are after.

I took your example, combined the data into 1 table and created a pivot for it.

Steve

12. Re: Combining data (XP)

I agree with Steve's confusion. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> As I understood you problem definition - there were a number of students (n) with scores for one date, and another number (m) with scores for another date in another spreadsheet.

What I understand from your most recent answer is that n or m are numbers somewhere near 1400. If that is the case, your sheets already show roughly 1400 rows - one per student.

Steve's solution was to report exactly that same number (give or take a few for overlaps)

Perhaps the attached example where n & m are small will clarify how I interpretted it.

13. Re: Combining data (XP)

It's all fine - she got what she needed and I'm done messing with it. Thanks for all the input. Just because I know it'll bug ya'll, here's the result I needed:

1-15-04 10-13-03
Doe, John 72 90
Drew, Nancy 92 64
Blow, Jane 92
Vator, Ele 95

I know you can make a pivot table out of lots and lots of entries - the school panel needed to see ALL 1400 kids' names AT ONE TIME - that was the point of the pivot table problem. Anyway - everybody's happy now - they're all graduating in 3 weeks. We'll worry about this again next year because the person making this list will do it the same dumb way again.

Again - thanks for trying to understand - Have a great day.

Linda

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•