Populate other cells based on specific criteria

  • 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]

  • 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

  • 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 :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!