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

jbanks,

Please read the Forum Rules about thread titles before starting your next thread.

Couldn't ask for more, thank you.

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

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

=IF(COLUMN()-COLUMN(Sheet1!$C2:$V2)+1>COLUMNS(Sheet1!$C$2:$V$2)-COUNTBLANK(Sheet1!$C2:$V2),"",INDIRECT("SHEET1!"&ADDRESS(1,SMALL((IF(Sheet1!$C2:$V2<>"",COLUMN(Sheet1!$C2:$V2),COLUMN()+COLUMNS(Sheet1!$C2:$V2))),COLUMN()-COLUMN(Sheet1!$C$2:$V$2)+1),4)))

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.