Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining data (XP)

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

  3. #3
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining data (XP)

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

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining data (XP)

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

    Thanks - again.

    Linda

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining data (XP)

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

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #12
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •