+ Reply to Thread
Results 1 to 7 of 7

Picking Values from a row with blanks?

  1. #1
    Registered User
    Join Date
    11-11-2007
    Posts
    16

    Picking Values from a row with blanks?

    Hi

    I work in school which for example offers 20 courses for every student. Therefore I have a spreadsheet which has students name in A and B then the 20 courses spread from C to V.

    Each student will study 3, 4 or 5 of the courses and will have value in the corresponding column (their teaching group) the rest of their row will be blank.

    For each student create in columns X, Y , Z, AA and AB I would like to be able to pull from the 20 courses those which the student studies. Therefore finishing up with C to V hidden and just the student names and the 3, 4 or 5 courses they study.

    Any ideas? if one method is to get them into one cell how do I get them into the 5 columns; i know DATA : Text to Columns but won't have anything to Delimit it with and can't use Fixed width as students will study subjects with different length charactors.

    Any help would be great!!

    Thanks

    J Banks
    Last edited by jbanks; 10-06-2008 at 05:51 PM. Reason: thread title

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    jbanks,

    Please read the Forum Rules about thread titles before starting your next thread.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    11-11-2007
    Posts
    16
    Hi and Sorry!

    Have now read. Will sure I avoid that in future.

    Jbanks

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    Couldn't ask for more, thank you.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582
    I'm looking at this but have a quick question. What is their teaching group; a simple number, text, a combination of both (in which case, what's the pattern)?

    Thanks
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582
    Okay, the formula is not a pretty one but it works. I modified one that I found for removing blanks from columns and adapted it to return the value from the first row (your Course Titles) and removing blanks from rows. It is an array formula which means that after you enter it into your first cell, then you (while still in the formula bar) need to confirm it with CNTRL SHFT ENTER. Then you can drag it down and across. Here is the formula based on the information that you gave us
    Please Login or Register  to view this content.
    The array basically looks column by column and subtracts out the blanks. Sheet1! is the sheet where your original table is. $C2:$V2 is where your first student information is. See if you can modify it for your data (if it needs modification). Let us know if it worked.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-11-2007
    Posts
    16
    Thanks Chemist B!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Userform's listbox values depending on values on certain matrix
    By Juhanen II in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-19-2008, 08:01 AM
  2. Listing values while ignoring blanks
    By DamienC in forum Excel General
    Replies: 4
    Last Post: 07-10-2008, 09:18 AM
  3. Replies: 1
    Last Post: 04-26-2008, 08:00 AM
  4. Import and export a text document
    By Odysseus in forum Excel General
    Replies: 0
    Last Post: 01-10-2008, 10:41 AM
  5. Adding Row Values depending on Corresponding Rows
    By chr5648 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-13-2007, 05:26 PM

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