[ms office - 2003] Excel - Creating lists of data

[gmiller]

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

[LosOjos]

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

[limbo]

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.

[gmiller]

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

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.