Question about multiple IFs

[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.

 

[Pete_UK]

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

 

[Roger Govier]

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

 

[Steve Audus, Chaucer BEC, Sheffield UK]

Thanks for the help with this.

Are your staff names in column A and student names on row 1 ? How many
of each ?

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 ?

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 ?

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

 

[Steve Audus, Chaucer BEC, Sheffield UK]

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

 

[Roger Govier]

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.

 

[Steve Audus, Chaucer BEC, Sheffield UK]

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

 

[Steve Audus, Chaucer BEC, Sheffield UK]

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

 

[Steve Audus, Chaucer BEC, Sheffield UK]

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

 

[Don Guillett]

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"

 

[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

 

[Don Guillett]

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.

 

[Don Guillett]

Send your workbook to me

 

[Steve Audus, Chaucer BEC, Sheffield UK]

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

 

[Steve Audus, Chaucer BEC, Sheffield UK]

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

 

[Steve Audus, Chaucer BEC, Sheffield UK]

Roger,

Thanks
I added "," as the delimited in MulCat

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

but it returned.... FALSE

Any idea

Thanks
Steve

 

[Roger Govier]

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

 

[Steve Audus, Chaucer BEC, Sheffield UK]

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

 

[Roger Govier]

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.

 

[Steve Audus, Chaucer BEC, Sheffield UK]

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