Populate a list based on a condition

  • Hi all.


    I need a formula which will create a list of names based on another list of names with a condition. eg. I have the following list in sheet 2 from which I need to select all the males


    [TABLE="width: 128"]




    [tr]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Name[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Score[/TD]


    [/tr]


    [tr]


    [TD="class: xl63, bgcolor: transparent"][/TD]


    [TD="class: xl63, bgcolor: transparent"][/TD]


    [/tr]


    [tr]


    [TD="class: xl63, bgcolor: transparent"]Jack[/TD]


    [TD="class: xl63, bgcolor: transparent"]10[/TD]


    [/tr]


    [tr]


    [TD="class: xl63, bgcolor: transparent"]Sarah[/TD]


    [TD="class: xl63, bgcolor: transparent"]9[/TD]


    [/tr]


    [tr]


    [TD="class: xl63, bgcolor: transparent"]Sam[/TD]


    [TD="class: xl63, bgcolor: transparent"]7[/TD]


    [/tr]


    [tr]


    [TD="class: xl63, bgcolor: transparent"]Harry[/TD]


    [TD="class: xl63, bgcolor: transparent"]4[/TD]


    [/tr]


    [tr]


    [TD="class: xl63, bgcolor: transparent"]Charley[/TD]


    [TD="class: xl63, bgcolor: transparent"]2[/TD]


    [/tr]



    [/TABLE]



    The condition (male or female) needs to be picked up from sheet 1 where the data appears as follows:


    [TABLE="width: 128"]




    [tr]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Name[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Gender[/TD]


    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"][/TD]


    [TD="class: xl65, bgcolor: transparent"][/TD]


    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]Harry[/TD]


    [TD="class: xl65, bgcolor: transparent"]M[/TD]


    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]Sarah[/TD]


    [TD="class: xl65, bgcolor: transparent"]F[/TD]


    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]Sam[/TD]


    [TD="class: xl65, bgcolor: transparent"]F[/TD]


    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]Charley[/TD]


    [TD="class: xl65, bgcolor: transparent"]M[/TD]


    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]Jack[/TD]


    [TD="class: xl65, bgcolor: transparent"]M[/TD]


    [/tr]



    [/TABLE]



    Any ideas?

  • Re: Populate a list based on a condition


    Data Range
    [Table="class: grid"]

    [tr][td][/td][td]

    A

    [/td][td]

    B

    [/td][td]

    C

    [/td][/tr]


    [tr][td]

    1

    [/td][td]

    [COLOR="#333333"]

    Name

    [/COLOR]

    [/td][td]

    [COLOR="#333333"]

    Score

    [/COLOR]

    [/td][td]

    [COLOR="#000000"]

    Sex

    [/COLOR]

    [/td][/tr]



    [tr][td]

    2

    [/td][td]

    [COLOR="#333333"]

    [/COLOR]

    [/td][td]

    [COLOR="#333333"]

    [/COLOR]

    [/td][td]

    [COLOR="#000000"]

    [/COLOR]

    [/td][/tr]



    [tr][td]

    3

    [/td][td]

    [COLOR="#333333"]

    Jack

    [/COLOR]

    [/td][td]

    [COLOR="#333333"]

    10

    [/COLOR]

    [/td][td]

    [COLOR="#000000"]

    M

    [/COLOR]

    [/td][/tr]



    [tr][td]

    4

    [/td][td]

    [COLOR="#333333"]

    Sarah

    [/COLOR]

    [/td][td]

    [COLOR="#333333"]

    9

    [/COLOR]

    [/td][td]

    [COLOR="#000000"]

    F

    [/COLOR]

    [/td][/tr]



    [tr][td]

    5

    [/td][td]

    [COLOR="#333333"]

    Sam

    [/COLOR]

    [/td][td]

    [COLOR="#333333"]

    7

    [/COLOR]

    [/td][td]

    [COLOR="#000000"]

    F

    [/COLOR]

    [/td][/tr]



    [tr][td]

    6

    [/td][td]

    [COLOR="#333333"]

    Harry

    [/COLOR]

    [/td][td]

    [COLOR="#333333"]

    4

    [/COLOR]

    [/td][td]

    [COLOR="#000000"]

    M

    [/COLOR]

    [/td][/tr]



    [tr][td]

    7

    [/td][td]

    [COLOR="#333333"]

    Charley

    [/COLOR]

    [/td][td]

    [COLOR="#333333"]

    2

    [/COLOR]

    [/td][td]

    [COLOR="#000000"]

    M

    [/COLOR]

    [/td][/tr]


    [/table]


    Formula in C3 is =VLOOKUP(A3,Sheet1!$A$3:$B$7,2,FALSE). Copy Down

  • Re: Populate a list based on a condition


    Vlookup only works if all the information is picked up from the same sheet however the gender is on a different sheet and needs to be formulated as a condition from there. Hope that makes sense

  • Re: Populate a list based on a condition


    I am not understanding your post #3. In post #1, you indicated that the list with scores is on sheet2 and the list with sex is on sheet1. If you do the vlookup on sheet2 referencing sheet1, then I don't understand what your issue is. After doing the vlookup, you can set the autofilter to show either sex.

  • Re: Populate a list based on a condition


    Essentially what I require is a formula that populates the list (removing empty rows in between) on sheet 3 whilst not editing sheet 1 or 2 in any way. Yes, I am aware that I can do a vlookup on sheet 2 referencing sheet 1 but I don't want to do it this way. Can it be done by matching the gender directly to sheet 1, I.e. a formula for selecting males only from sheet 2

  • Re: Populate a list based on a condition


    Nothing is currently on sheet 3. The list I require needs to populate on this sheet. I'm specifically looking for a formula.

  • Re: Populate a list based on a condition


    I'm not understanding your issue at all. Perhaps you should upload a sample workbook showing the data you have and the result you are looking for (manually inserted) with a full explanation cell by cell. Your explanation is not clear to me and seeing the file may help to produce a viable solution.

  • Re: Populate a list based on a condition


    Quote from AlanSidman;728125

    I'm not understanding your issue at all. Perhaps you should upload a sample workbook showing the data you have and the result you are looking for (manually inserted) with a full explanation cell by cell. Your explanation is not clear to me and seeing the file may help to produce a viable solution.


    Sample workbook attached. Sheet 3 is what I am trying to achieve (without modifying sheet 1 and 2 in anyway, i.e. not adding a vlookup or other formulae to them).02-

    Files

    • Sample.xlsx

      (10.5 kB, downloaded 78 times, last: )
  • Re: Populate a list based on a condition


    Hi,


    In sheet3 cell A5


    =IFERROR(INDEX(Sheet1!$A$5:$A$9,SMALL(IF(FREQUENCY(IF(Sheet1!$B$5:$B$9="M",MATCH(Sheet1!$A$5:$A$9,Sheet1!$A$5:$A$9,0)),ROW(Sheet1!$A$5:$A$9)-ROW(Sheet1!$A$5)+1),ROW(Sheet1!$A$5:$A$9)-ROW(Sheet1!$A$5)+1),ROWS(Sheet3!$A$4:A4))),"")


    and drag down


    and in sheet3 cell B5


    =VLOOKUP(A5,Sheet2!$A$5:$B$9,2,0)


    and drag down.

  • Re: Populate a list based on a condition



    Thanks for this however there is one issue with the formula. I have amended the workbook to reflect real data I am working with and put in the formulae you suggested however there is one query which I have which is marked in amber in the attached file (updated version). Please could you kindly review. if you need any further info feel free to ask.


    Thanks

    Files

    • Sample 2.xlsx

      (20.32 kB, downloaded 72 times, last: )
  • Re: Populate a list based on a condition


    Well not that you asked at the beginning!
    In Assesment sheet, delete "Y" in column J, else formula will be complicate.
    What's the point to put "Y" to Harry if he then does not appear in sheet INTERVENTION ??

  • Re: Populate a list based on a condition


    Quote from Ingo_Ingo;728156

    Well not that you asked at the beginning!
    In Assesment sheet, delete "Y" in column J, else formula will be complicate.
    What's the point to put "Y" to Harry if he then does not appear in sheet INTERVENTION ??


    Intervention sheet is only for certain people, not everyone who is on the assessment sheet will be on the intervention one. The formula I need must pick up the names from the intervention sheet

  • Re: Populate a list based on a condition


    I'm sorry but I do not understand.


    So what is the criterion to bring names from "Intervention" sheet, in the sheet "list"?