+ Post New Thread
Results 1 to 4 of 4
Office Software Thread, Excel - Creating lists of data in Technical; Hi I can do this manually but wondering if there is a macro or some other automatic way to do ...
  1. #1

    Join Date
    Oct 2007
    Location
    scotland
    Posts
    45
    Thank Post
    15
    Thanked 1 Time in 1 Post
    Rep Power
    0

    Excel - Creating lists of data

    Hi

    I can do this manually but wondering if there is a macro or some other automatic way to do this.

    I have two columns of data:

    Pupil ID | Set ID
    8007 P6CEA
    8011 P6CED
    8014 P6CEM
    8016 P6CFR
    8026 P6CHB
    8027 P6CHE
    8031 P6CHG
    8057 P6CLE
    8206 P6CLS
    8131 P6CMN
    8083 P6CRE
    8089 P6CSC
    8304 P6CSG
    8230 P6CTE
    8181
    8246
    8470
    8454
    8664
    8611

    I want to take this and change it so that each pupil id is listed against each individual set id once.
    Eg:
    8007 P6CEA
    8007 P6CED
    8007 P6CEM
    8007 P6CFR
    8007 P6CHB
    8007 P6CHE
    8007 P6CHG
    8007 P6CLE
    8007 P6CLS
    8007 P6CMN
    8007 P6CRE
    8007 P6CSC
    8007 P6CSG
    8007 P6CTE
    Then do the same for the next pupil id, and so on until each pupil is associated once with each set id.
    I can do this manually but it takes too long.
    Is there an easier way to sort the data this way?
    Thanks
    Miller

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,452
    Thank Post
    1,439
    Thanked 1,170 Times in 798 Posts
    Rep Power
    707
    I'm not sure what you're trying to achieve, are you just sorting the list by Pupil ID? If so, just use Excel's sort function...
    If the problem is that the pupil number is being listed against the same Set ID multiple times, in Excel 2007 onwards you can sort them by Pupil ID then use the Remove Duplicates tool...

    Sorry I'm just not sure what you're trying to do here

  3. #3
    limbo's Avatar
    Join Date
    Aug 2005
    Location
    Birmingham
    Posts
    460
    Thank Post
    2
    Thanked 41 Times in 36 Posts
    Rep Power
    25
    Not sure about whether it is possible in Excel, but I would go about this in MSAccess - two tables, each containing one of each of the two columns of data, then a query taking the two tables in would combine them with every possible combination, this can then be exported back to Excel.

    Likely to take just a few minutes.

  4. Thanks to limbo from:

    gmiller (1st March 2011)

  5. #4

    Join Date
    Oct 2007
    Location
    scotland
    Posts
    45
    Thank Post
    15
    Thanked 1 Time in 1 Post
    Rep Power
    0
    Hey Limbo

    Thanks a lot, that worked a treat. Two tables, join them and then a query, export into excel, bingo. That will save me a lot of time.
    Cheers
    Miller
    Quote Originally Posted by limbo View Post
    Not sure about whether it is possible in Excel, but I would go about this in MSAccess - two tables, each containing one of each of the two columns of data, then a query taking the two tables in would combine them with every possible combination, this can then be exported back to Excel.

    Likely to take just a few minutes.

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 1
    Last Post: 11th June 2010, 09:23 PM
  2. excel transpose feature for creating moodle class list
    By projector1 in forum How do you do....it?
    Replies: 0
    Last Post: 24th September 2009, 01:15 PM
  3. Creating Custion Excel Templates in SIMS
    By Stuart_C in forum MIS Systems
    Replies: 1
    Last Post: 14th September 2009, 04:35 PM
  4. Merging data in excel
    By Mr_M_Cox in forum Windows
    Replies: 10
    Last Post: 31st October 2008, 11:43 AM
  5. Excel Assesment Data
    By danIT in forum General Chat
    Replies: 2
    Last Post: 14th February 2008, 02:14 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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