Populate other cells based on specific criteria

jacjas · Oct 7th 2014

Is there a formula that can do the following for me:

I have 270 students with four humanities subjects, Humanities, Geography, History and Beliefs, Philosophy & Ethics. For each student there has been a target grade entered for the subject Humanities , I want to use a formula that will look at the Student Id, then look at the humanities grade entered and then populate the target grade field for Geography, History and Beliefs, Philosophy and Ethics for that student with the same target grade entered for Humanities. see tables below and I have attached an example file

[TABLE="width: 568"]

[tr]

[td]

cid

[/td]

[td]

year

[/td]

[td]

tg

[/td]

[td]

house

[/td]

[td]

subject

[/td]

[td]

tgroup

[/td]

[td]

target

[/td]

[/tr]

[tr]

[td]

A09314

[/td]

[td]

9

[/td]

[td]

9T2

[/td]

[td]

Thoresby

[/td]

[td]

Beliefs, Philosophy & Ethics

[/td]

[td]

9y/Bp1

[/td]

[td]

[/td]

[/tr]

[tr]

[td]

A09314

[/td]

[td]

9

[/td]

[td]

9T2

[/td]

[td]

Thoresby

[/td]

[td]

Geography

[/td]

[td]

9y/Gg1

[/td]

[td]

[/td]

[/tr]

[tr]

[td]

A09314

[/td]

[td]

9

[/td]

[td]

9T2

[/td]

[td]

Thoresby

[/td]

[td]

History

[/td]

[td]

9y/Hi1

[/td]

[td]

[/td]

[/tr]

[tr]

[td]

A09314

[/td]

[td]

9

[/td]

[td]

9T2

[/td]

[td]

Thoresby

[/td]

[td]

Humanities

[/td]

[td]

9y/Hu1

[/td]

[td]

7c

[/td]

[/tr]

[/TABLE]

Look at theCID as that’s unique to that student, then look for the Humanities subject andits relevant target grade, then populate the three blank humanities subjects withthe same target grade that has been entered for Humanities, end result seebelow.

[TABLE="width: 560"]

[tr]

[td]

cid

[/td]

[td]

year

[/td]

[td]

tg

[/td]

[td]

house

[/td]

[td]

subject

[/td]

[td]

tgroup

[/td]

[td]

target

[/td]

[/tr]

[tr]

[td]

A09314

[/td]

[td]

9

[/td]

[td]

9T2

[/td]

[td]

Thoresby

[/td]

[td]

Beliefs, Philosophy & Ethics

[/td]

[td]

9y/Bp1

[/td]

[td]

7c

[/td]

[/tr]

[tr]

[td]

A09314

[/td]

[td]

9

[/td]

[td]

9T2

[/td]

[td]

Thoresby

[/td]

[td]

Geography

[/td]

[td]

9y/Gg1

[/td]

[td]

7c

[/td]

[/tr]

[tr]

[td]

A09314

[/td]

[td]

9

[/td]

[td]

9T2

[/td]

[td]

Thoresby

[/td]

[td]

History

[/td]

[td]

9y/Hi1

[/td]

[td]

7c

[/td]

[/tr]

[tr]

[td]

A09314

[/td]

[td]

9

[/td]

[td]

9T2

[/td]

[td]

Thoresby

[/td]

[td]

Humanities

[/td]

[td]

9y/Hu1

[/td]

[td]

7c

[/td]

[/tr]

[/TABLE]

pangolin · Oct 7th 2014

Re: Populate other cells based on specific criteria

use this formula

=INDEX($G$2:$G$297,LARGE(ROW($A$2:$A$297)*(($A$2:$A$297)=A2)*(($E$2:$E$297)=$E$9),1)-ROW($F$2)+1)

note this is an array based formula and needs to be conformed by Ctrl+Shift+Enter

also note that do not use this formula in Column G as it will replace the manual entries in that column....therefore use it in another column lets say H

jacjas · Oct 7th 2014

Re: Populate other cells based on specific criteria

That's brilliant thank you, I've had to change it around a little to fit the master I have but I got there in the end I'm in awe I have no idea what that formula is or how it works or was built but it does what I need so once again thank you very much

Populate other cells based on specific criteria

Participate now!

Share