Excel Array Guide

Russinating · 18 Sep 2008 at 12:52

I'm looking for a decent guide on using arrays in excel - and advice on whether that's actually what I need!

Say I've got a list of students in column A and then a selection of 5 course choices in column B, I want to create a new column with the heading "Course 1" that then lists all the students who've chosen that course.

Cheers for your help

SimonCHere · 18 Sep 2008 at 14:20

check out the sumif() function

Russinating · 18 Sep 2008 at 14:41

Based on a few things I've just read wont that try to add up the names as a calculation, rather than storing all the names seperately and then entering them each into a different cell?

SimonCHere · 18 Sep 2008 at 14:54

countif() then

show us a little bit of data and expected results and I'll see if I can help.

Russinating · 19 Sep 2008 at 22:20

Column A............Column B........Maths..............Science.........English
Bobby Blue............Maths.............Bobby Blue........ Roger Red.......Penny Pink
Roger Red.............Science...........Gary Green
Gary Green............Maths
Penny Pink............English

SimonCHere · 19 Sep 2008 at 23:19

OK, got nothing, sorry.

You could put the following in columns and copy the formula down the range.
C= =IF($B2="Maths";$A2;"")
D= =IF($B2="Science";$A2;"")
E= =IF($B2="English";$A2;"")

Or you could set up an auto or advanced filter with copy to range to move the correct data around to a different area of the sheet.

Or you could sort column B and write a bit of VBA code that loops through all the cells in column B writing names and column headings to the sheet as and when they change.
I don't have MSExcel at home so can't write it for you.

suarve · 20 Sep 2008 at 14:19

Can't you make some sort of fomula using the vlookup and countif functions?