+ Reply to Thread
Results 1 to 3 of 3

Grouping "like values" into an array

  1. #1
    Registered User
    Join Date
    09-03-2009
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003/2007
    Posts
    2

    Grouping "like values" into an array

    Here is my problem:

    I'm creating a spreadsheet that takes a report of student transcripts and populates the data into a formatted workbook, with each student having their own sheet. I have all the info in columns, but now I need to "parse"(?) the "student name" column and isolate the grades for each student. I have it working by manually setting a range of values for the first student, but I want to make it more dynamic. If the entire "grade" column is used, there will be redundant values and it mixes up who's grades match to who.

    Is there a way to run through the entire "Student Name" column and put every Name A into array[0], Name B into array[1], Name C into array[2], etc.? That way when I want to pull Student A's grades, I only need to match the course number to the grade for THAT student, then move on to the next student (array index). This sounds like an array of arrays...is that possible? Here is the problem in a nutshell:

    name - course# - grade
    bill - CD101 - A
    bill - CD121 - B
    bill - GR240 - C+
    john - CD101 - B
    john - CD121 - D
    John - GR240 - W

    I have my formulas working to look up CD101 and find the corresponding grade if I manually set the range to only look at the first student, but I need to separate each match by student name without manually setting hundreds of ranges.

    any thoughts? I'm a noob to this excel programming so even a keyword I could look up would be hugely appreciated.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Grouping "like values" into an array

    I'd keep all the data on a single sheet and then use some combination of AutoFilter, Advanced Filter, database functions (DSUM, DAVERAGE, ...) and pivot tables to extract the infoprmation you need. That's how Excel was designed to work.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    09-03-2009
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003/2007
    Posts
    2

    Re: Grouping "like values" into an array

    Thanks for your quick reply! I already have the autofilter set up and working with the columns and it is great for isolating each student's info from the one big sheet. I just have a nicely formatted division sheet that shows each class and which quarter they are offered and I just want to link the raw data from this spreadsheet into the "visually pleasing" formatted document.

    I also like to print copies and give them to students each quarter to track their own progress. It's possibly a futile exercise in trying to learn some excel programming and trying to find a way to make hundreds of rows of data easier on the eyes by jamming that data into a pretty layout. (I'm a designer, not a programmer.) Maybe that's why excel and I don't get along.

    Thanks again and I'm still open for ideas...(I'll look into pivot tables as I've never used them either)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1