Question about multiple IFs

  • Thread starter Thread starter Steve Audus, Chaucer BEC, Sheffield UK
  • Start date Start date
S

Steve Audus, Chaucer BEC, Sheffield UK

Thank you for any suggestions with this quick problem.

I am trying to do some analysis on some school data,
I have a row for each member staff, and a column each student.

I'd like excel to look along a staff row, which contains 0, 1, 2, 3 or more
in each cell in each column.
If it finds a cell with 3 or more, I'd like it to return the name of the
student at the top of the column.

I hope that makes sense.

Thanks for any help.


I hope that makes sense.
 
Are your staff names in column A and student names on row 1 ? How many
of each ?

Where do you want the student name with a mark >=3 to be placed ? Do
you have a separate list of staff names and want the student names to
appear on the same row for this list ?

Have you thought about arranging the data the other way round, so you
have staff names (maybe tens of them) going across and student names
(maybe hundreds) going down the sheet ?

Pete
 
Hi Steve
With your data on Sheet1, copy your list of Staff members from A2 downward,
and paste to A2 of Sheet2
In cell B2 of Sheet2
=IF(Sheet1!B2<3,"",INDEX(Sheet1!$A$1:$K$1,COLUMN()))
Adjust range $A$1:$K$1 to match the number of columns on Sheet1

Copy Across and down as far as required
 
Thanks for the help with this.
Are your staff names in column A and student names on row 1 ? How many
of each ?
Click to expand...

Sort of,

Staff are listed in Column A,
students list is then in the following columns.
Example:

Teacher Name | Student 1 | Student 2 | Student 3 | Student 4
Teacher 1 | 0 | 1 | 3 | 1
Teacher 2 | 0 | 3 | 3 | 0
Thacher 3 | 0 | 1 | 1 | 3

Where do you want the student name with a mark >=3 to be placed ? Do
you have a separate list of staff names and want the student names to
appear on the same row for this list ?
Click to expand...

On a sperate sheet. So...

Teacher 1 | Student 3
Teacher 2 | Student 2 Student 3
Teacher 3 | Student 4
Have you thought about arranging the data the other way round, so you
have staff names (maybe tens of them) going across and student names
(maybe hundreds) going down the sheet ?
Click to expand...

I can arrange them either way, which ever is easier.

But for this report the focus is on the staff.

Thank you again for any help.

Steve
 
Roger Thanks for the help, but I can't get the suggestion to work,
as I don't know the INDEX or Column commands.

Have a llok at the replay I put to pete, that I think expains the problem
more clearly.

Thank you
Steve
 
Hi Steve

Did you try what I suggested, copying my formula to Cell B2 on Sheet2 then
copying it across through C2:K2 and then copying B2:K2 down far as many rows
of Staff as you have?

It does exactly what you described as your layout in your second post to
Pete.
The data will be spread out across the page, not in adjacent columns.

If you want it in adjacent columns you would need to use a VBA solution.
Post back if VBA is acceptable to you.
 
Roger,

This is the formula as edited I am doing something wrong as I don't appear
to be getting any results

=IF('Staff Analysis'!4:4<3,"",INDEX('Staff Analysis'!$B$3:$GD$3,COLUMN()))

Then it goes down

=IF('Staff Analysis'!5:5<3,"",INDEX('Staff Analysis'!$B$3:$GD$3,COLUMN()))
=IF('Staff Analysis'!6:6<3,"",INDEX('Staff Analysis'!$B$3:$GD$3,COLUMN()))
etc.

Thank you for your help on this.

Steve
 
Roger, I have relised where I was going wrong,
redid the formula

=IF('Staff Analysis'!B4<3,"",INDEX('Staff Analysis'!$A$3:$GD$3,COLUMN()))

and dragged it across the sheet.

But it appears to be returning the wrong column heading.

Eg.
=IF('Staff Analysis'!H12<3,"",INDEX('Staff Analysis'!$A$3:$GD$3,COLUMN()))

'Staff Analysis'!H12 Does equal 3

But it returning the column heading from

'Staff Analysis'!J3, instead of H3

Please advise, and thank you again for your help.

Steve
 
Roger Roger Roger,

I shouldn't post so quickly, and try to work it out.

I think I've got...
I have used this

=IF('Staff Analysis'!B4<3,"",INDEX('Staff Analysis'!$A$3:$GD$3,COLUMN('Staff
Analysis'!B$3)))

which appears to work

Then I was going to use =CONCATENATE(B4:GZ4) to bring all the names together
in
one cell, but the formula has to be written =CONCATENATE(B4,C4,D4,E4,F4 etc)

unless you know of another way?

Thank you again.

Steve
 
Sub listteachers()
With Sheets("Staff Analysis")
For i = 2 To 14
ms = ""
For ii = 2 To 5
If .Cells(i, ii) > 2 Then ms = ms & " " & .Cells(1, ii)
Next ii
'MsgBox ms
Sheets("DestinationSheetNameHere").Cells(i, "a") = ms
Next i
End With
End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"Steve Audus, Chaucer BEC, Sheffield UK"
 
Don,

Sorry Don you have confused me, what it that?

Thanks for the suggestion, I am sorry I don't understand it.

Steve
 
It is a macro that will put the appropriate students names in each row in
ONE cell for each row in the destination sheet. I can send you a file if you
give me your email address.
 
Thanks Don please send it though.

(e-mail address removed)

I am still not a 100% on what it does but I'll give it a try.

Cheers
Steve
 
Don and Roger,

This is brilliant great support thank you thank you.

I have used the MulCat Code

And it works great, except I can't use a delimiter
as it sticks one in for every cell, even empty ones.

Any ideas.

Thanks again

Steve
 
Roger,

Thanks
I added "," as the delimited in MulCat

used this formula
=SUBSTITUTE(TRIM(SUBSTITUTE(E8,","," "))," ",",")

but it returned.... FALSE

Any idea

Thanks
Steve
 
Hi Steve

can't understand why it would Return FALSE, unless there was a compariosn.
Worked perfectly for me in testing.
If you are still having problems, then send me a copy of your workbook to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address
 
Sorry Roger, it does work but..
But where

MultiCal returns without delimiter
Luke Chambers 9MGSheldon Radford 7CW

MultiCal returns with delimiter
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Luke Chambers
9MG,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Sheldon Radford
7CW,,,,,,,,,,,,,,,,,,,,,,,,

SUBSTITUTE returns
Luke,Chambers,9MG,Sheldon,Radford,7CW

I really need
Luke Chambers 9MG,Sheldon Radford 7CW

Please free feel to tell me to sod off if am being to picky.
You have been a great help aready.

Thanks
Steve
 
Hi Steve

I hadn't realized it was a multi word value being returned.
no easy way around that.

As I said previously, much better to go with Don's code (adjusted for the
number of columns and rows you use).
It does the whole job in one, and you will not end up with the problem
outlined.
 
OK I will leave it there, Thank Roger and to Don for all your excellent
support,
hopefully the reports will have a positive impact on our school, and as such
our students thank you as well.

THANK YOU

Steve Audus
Chaucer BEC
www.chaucer.sheffield.sch.uk
 
Back
Top