need to transpose or something ...

[Bill]

I have a spreadsheet of student data.
Each student is listed 5 to 8 or 9 times rather once each
because the course numbers each student is scheduled into
show up each on one line.

So instead of 600 or so total records I have nearly 5000
records all identical except for the course numbers.

I want to transpose these course numbers for each student
and have no idea how to do this in excel or access except
manually.

Anyone game to take a closer look?

 

[Ken Wright]

Can you give us a small example of how your data looks now, and how you would like it to look -
Just type it in though, no attachments please.

 

[Max]

A pivot table might quickly accomplish what you're after.

Assume your data looks like the sample set below:

Course Stud_Name
123 ABC
234 XYZ
345 ABC
345 XYZ
123 XYZ

(Stud_Name = Student Name)

Click inside the data

Click Data > Pivot table report

Click Next > Next

In Step 3 of 4 of the PT wizard:

Drag Stud_Name and drop under ROW area

Drag Course and drop under COLUMN area

Drag Course and drop under DATA area
(it will appear as 'Sum of Course', which
needs to be changed to 'Count of Course')

Double click on 'Sum of Course'

In the PivotTable Field dialog box
Select 'Count' under 'Summarize by:'
Click OK
(It will appear as 'Count of Course')

Click Finish

The pivot table will appear in a new sheet
to the immediate left, viz:

Count of Course Course
Name 123 234 345 Grand Total
ABC 1 1 2
XYZ 1 1 1 3
Grand Total 2 1 2 5

The pivot table will give you a listing
of the student names (once line each) and their courses