I have a huge database in Excel, basically a matrix format. In a column I have 1400 names (of which some features up to 15 times) in a second column next to the name I have course codes. I want to extract the data so I have each name appear only once in a column and in rows next to it list all the course coded the name has attended.
Any ideas?
This is very much possible.
Do you have a sample that you can upload?
To Attach a File:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
If I'm reading your post correctly try this,
Select one cell in the data then on the insert tab select pivot table. A window will popup just click OK.
in the pivot table field list click the name and it should show up on the row labels box at the bottom.
Then click the course and put it in both the columns labels box and the values box.
This will give you what I think you're looking for very quickly.
Not sure about Pivots as you suggest you want the data listed across the page.
It can be done with Macros, but how about the attached non-macro solution which does a look-up based on concatenation?
Benefit of concatination is that you can do it on 1, 2, 3, etc but it will start getting messy maybe after 3!!
Stephen
Nice one stephen. But i guess it will be much simpler with a macro.
Just waiting for mclassen to update a sample file so we can assist.
Attached is and example of the pivot table. You could move the data around however you want, hence the name "pivot"
Sorry guys, I get an error trying to upload a sample, any idea why. The umplease file only shows a ! an IOERROR of sorts
Mclaassen, will get it checked.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks