Filtering values by V-lookup based on multiple column values

Options

Hi, i'm trying to filter values without YES in Column2 of sheet Y by Vlookup from Column2, Column3 of sheet X where Column3 is tagged as YES. Here is the formula: =COLLECT([Column2]1:[Column2]8, {New Sheet Range 1}, @cell=Value<>Yes). I feel something wrong with my code or approach. Any help appreciated.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    HERE is a link to a published sheet that contains the solution for parsing down a column from a list on a different sheet. My initial JOIN/COLLECT in String1 is going to be different because I pulled the data based on different criteria. The rest of the sheet though, you should be able to replicate for your sheet. This will parse the first set of data down your first column. Then you would set up another Comma and String column for your next list column, duplicate for your third list, etc. etc. All you would need to do is adjust your JOIN/COLLECT to pull the appropriate data.

«1

Answers

  • prathap.krishnashetty69881
    edited 01/22/20
    Options

    I tried many ways, but unable to arrive for expected solution

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 01/22/20
    Options

    Hi @prathap.krishnashetty69881

    I'm not sure I quite understand what you are looking to do. Are you looking to build a formula in your second sheet that says if Column 3 in the other sheet does not have yes, then indicate this in the current sheet, based on the content in the Primary Column?

    If so, you would need to decide what value you want returned in this sheet, if it does not have a Yes in the other sheet. For example, maybe you want this second sheet to say "Not Approved", "No Content" or "No Yes"? Then you could build a cross-sheet reference that is an IF statement, using an INDEX(MATCH to match the content to this other sheet.


    An INDEX(MATCH formula works like this:

    =INDEX({Column that has the value you want returned}, MATCH([Value you’re looking to match]@row, {Column with the value you’re looking to match in the other sheet}))


    Try this, with the IF statement:

    =IF(INDEX({Column 3 in other sheet}, MATCH([Primary Column]@row, {Column 2 in other sheet})) = "Yes", "", "No Content")


    This formula will look to see if the number in the Primary Column has a "Yes" next to it in the other sheet. If it does, then it will return a blank cell (indicated with this ""). If it does not say "Yes" in the other sheet, then the current sheet will say "No Content".

    Here are some Help Center articles that may help: IF function / Index Function / Match Function / Cross Sheet References


    Let me know if I've understood what you are looking to do, and if this works for you.

    Cheers,

    Genevieve

  • prathap.krishnashetty69881
    Options

    Hi Gene, Thanks for the quick response. Actually i'm looking for some kind of vlookup values where the Column 2 in other sheet with values as Yes, should be filtered out in the Column2 of formula sheet. Bottomline, formula filled column should only have values without Yes matching to Column 2 in other sheet.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    If a report will not work, you will need to join all of the "Yes" values together on your new sheet and then parse that data out. In the sheet you want to display the data in, you will want to add in 2 columns. In my example, I will call them "String" and "Comma".


    [Primary Column]1 (dragfill down):

    =VALUE(LEFT(String@row, FIND(",", String@row) - 1))


    String1:

    =JOIN(COLLECT({Other Sheet Column 2}, {Other Sheet Column 3}, @cell <> "Yes"), ",") + ","


    String2 (dragfill down):

    =RIGHT(String$1, LEN(String$1) - Comma@row)


    Comma1:

    0 (manually entered number zero)


    Comma2:

    =LEN(LEFT(String1, FIND(",", String1)))


    Comma3 (dragfill down):

    =LEN(LEFT(String$1, FIND(",", String$1, Comma2 + 1)))


    As you dragfill these formulas down, it will parse out your list leaving out anything that has a "Yes" in [Column3] of the other sheet.

  • prathap.krishnashetty69881
    edited 02/04/20
    Options

    Hi Paul,

    i am not sure if i understood the logic here. let me put it in a simpler way. to be easier, i have also reentered the requirement snapshots below.

    As per the snapshot of Sheet1, under Column5 i have used below formula to list the non Yes values:

    =IF(Delivered1 = "yes", "", ProductID1)

    Now, i'm looking for some kind of vlookup formula in Sheet2 (second snapshot) where the columns Zone1, Zone2, Zone3, Zone4 should only have ProductID values as a list in reference to Column5 (from Sheet1) related to Column Zone values from Sheet1.

    Appreciate your help.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @prathap.krishnashetty69881

    VLOOKUP will only pull one value.


    You will need to join each of the values together that meets your criteria then parse it out. The solution I provided above is based on the screenshots in your original post where you had all "non-yes" values within the same column.


    To be able to break it down further like you have in your second screenshot, [Primary Column]1 would become [Zone1]1. You would then need to include the additional zone range/criteria in the JOIN/COLLECT in String1.


    This would give you the results for Zone 1. Then you would need to duplicate this solution for each of the additional zones.

  • prathap.krishnashetty69881
    edited 02/21/20
    Options

    Hi Paul, can you help me with the second screenshot. when i try using the above string and comma formulas, i am getting unparseable. But, when i tried JOIN/COLLECT, it showing as below concatenated with comma. Is there a way to split this joint into different rows.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to copy/paste both of the formulas from your sheet?

  • prathap.krishnashetty69881
    Options

    =JOIN(COLLECT({Sheet1 Range 1}, {Sheet1 Range 2}, "Zone1"), ",")

    this is the only formula, i was able to parse as per this snapshot


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    What about the one that was giving you the error?

  • prathap.krishnashetty69881
    edited 02/21/20
    Options

    =VALUE(LEFT([Zone1]1, FIND(",", [Zone1]1), - 1))


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    That formula should not be giving you an #UNPARSEABLE error. If anything you should be getting an #INVALID VALUE error.

  • prathap.krishnashetty69881
    Options

    you are right. help me, how to split this C2, JKU into

    C2

    JKU

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You should be able to follow the steps I posted above (and am re-posting here):


    .

    .


    You will need to join all of the "Yes" values together on your new sheet and then parse that data out. In the sheet you want to display the data in, you will want to add in 2 columns. In my example, I will call them "String" and "Comma".


    [Primary Column]1 (dragfill down):

    =VALUE(LEFT(String@row, FIND(",", String@row) - 1))


    String1:

    =JOIN(COLLECT({Other Sheet Column 2}, {Other Sheet Column 3}, @cell <> "Yes"), ",") + ","


    String2 (dragfill down):

    =RIGHT(String$1, LEN(String$1) - Comma@row)


    Comma1:

    0 (manually entered number zero)


    Comma2:

    =LEN(LEFT(String1, FIND(",", String1)))


    Comma3 (dragfill down):

    =LEN(LEFT(String$1, FIND(",", String$1, Comma2 + 1)))


    As you dragfill these formulas down, it will parse out your list leaving out anything that has a "Yes" in [Column3] of the other sheet.

  • prathap.krishnashetty69881
    Options

    Paul, i think i took different approach for this problem and had arrived to a different path, hence will close this problem and open a new one based on which i assume would get a solution. But, will try to look into your approach as well.

    Thanks a lot for the patience and all the reply's.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!