+ Reply to Thread
Results 1 to 11 of 11

Creating a list of items selected "yes" without duplicates

  1. #1
    Registered User
    Join Date
    05-06-2012
    Location
    Limpop
    MS-Off Ver
    Excel 2007
    Posts
    19

    Creating a list of items selected "yes" without duplicates

    Hi there guys,

    I have attached a sample of what im trying to do here.

    A2:A10 i have items, B2:B10 they can selected "yes" or "no" and C2:C10 the values

    what i would like is, is "yes" was selected, i would like the item name added to a list. so basically:
    =IF(A2:A10="YES",A2:A10)

    and then i can just add the value to each item later (that part im fine with)

    but i need to remove duplicates and obv have this carried throughout the cells.

    any help??? please
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Creating a list of items selected "yes" without duplicates

    Getting out of the thread
    Last edited by Pepe Le Mokko; 01-19-2014 at 11:30 AM.

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Creating a list of items selected "yes" without duplicates

    Try this...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    1
    -----
    -----
    -----
    -----
    -----
    -----
    -----
    2
    Item
    Yes/No
    Value
    Yes
    Item
    Value
    3
    Item 1
    yes
    1
    Item 1
    1
    4
    Item 2
    no
    2
    Item 4
    4
    5
    Item 3
    no
    2
    Item 10
    1
    6
    Item 4
    yes
    3
    7
    Item 4
    yes
    1
    8
    Item 6
    no
    1
    9
    Item 7
    no
    1
    10
    Item 8
    no
    1
    11
    Item 9
    no
    2
    12
    Item 10
    yes
    1
    13


    This array formula** entered in F3:

    =IFERROR(INDEX(A$3:A$12,MATCH(0,IF(B$3:B$12=E$2,COUNTIF(F$2:F2,A$3:A$12)),0)),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    This formula entered in G3:

    =IF(F3="","",SUMIF(A$3:A$12,F3,C$3:C$12))

    Select F3:G3 and copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Creating a list of items selected "yes" without duplicates

    You can use this formula

    =IFERROR(INDEX($A$3:$A$12,SMALL(IF($B$3:$B$12="yes",ROW($B$3:$B$12)-2,""),ROWS($A$1:A1))),"")

    In C3 and drag it down

    **Array formulas must be entered with Ctrl+Shift+Enter key combination.

    A
    B
    C
    1
    2
    Item Yes/No Value
    3
    Item 1 yes Item 1
    4
    Item 2 no Item 4
    5
    Item 3 no Item 5
    6
    Item 4 yes Item 10
    7
    Item 5 yes
    8
    Item 6 no
    9
    Item 7 no
    10
    Item 8 no
    11
    Item 9 no
    12
    Item 10 yes
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Creating a list of items selected "yes" without duplicates

    Quote Originally Posted by AlKey View Post
    You can use this formula

    =IFERROR(INDEX($A$3:$A$12,SMALL(IF($B$3:$B$12="yes",ROW($B$3:$B$12)-2,""),ROWS($A$1:A1))),"")
    That won't exclude duplicates:

    i need to remove duplicates

  6. #6
    Registered User
    Join Date
    05-06-2012
    Location
    Limpop
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Creating a list of items selected "yes" without duplicates

    Hi Tony,

    Your formula works to an extent, my problem is that my cells run accross no down (C3:C6 is actually C3:L3)
    does the code change then?

    Thanks otherwise everyone for the fast response

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Creating a list of items selected "yes" without duplicates

    If your actual data goes across rows then why didn't you do it that way in your sample file!

    How about posting a new sample file that accurately demonstrates your data structure.

    Also, tell/show us where you want the results to appear.

  8. #8
    Registered User
    Join Date
    05-06-2012
    Location
    Limpop
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Creating a list of items selected "yes" without duplicates

    Hay so sorry, i changed it and i wasnt aware that it would play so much on the formula,

    in case you wondering why it runs across, there will be % graphs going above it. (sorry i cant post the actual file as it is sensitive company info)

    Self teaching yourself excel can easily drive you crazy!!

    Thanks again Tony
    Attached Files Attached Files

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Creating a list of items selected "yes" without duplicates

    Ok, so want the results to go across a row...

    No problem, just need a minor tweak.

    Data Range
    A
    B
    C
    D
    E
    1
    -----
    -----
    -----
    -----
    -----
    2
    Item
    Yes/No
    Value
    3
    Item 1
    yes
    1
    4
    Item 2
    no
    2
    5
    Item 3
    no
    2
    6
    Item 4
    yes
    3
    7
    Item 4
    yes
    1
    8
    Item 6
    no
    1
    9
    Item 7
    no
    1
    10
    Item 8
    no
    1
    11
    Item 10
    yes
    2
    12
    Item 10
    yes
    1
    13
    14
    15
    Yes
    16
    Item
    Item 1
    Item 4
    Item 10
    17
    Value
    1
    4
    3


    This array formula** entered in B16:

    =IFERROR(INDEX($A3:$A12,MATCH(0,IF($B3:$B12=$A15,COUNTIF($A16:A16,$A3:$A12)),0)),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    This formula entered in B17:

    =IF(B16="","",SUMIF($A3:$A12,B16,$C3:$C12))

    Select B16:B17 and copy across until you get blanks.

  10. #10
    Registered User
    Join Date
    05-06-2012
    Location
    Limpop
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Creating a list of items selected "yes" without duplicates

    Hi,

    i figured the tweaks would be just that, moving the $, but now it carries duplicates for some reason?? Any idea why?

    I have removed all details and just added the sheet so you can actually see what im busy with. Cause i know how confusing i can be when trying to explain something.

    Thanks again
    Attached Files Attached Files

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Creating a list of items selected "yes" without duplicates

    Ugh! Merged cells!

    Unmerge the cells from C2:C7.

    Then, enter this array formula** in C2:

    =IFERROR(INDEX($N2:$N33,MATCH(0,IF($O2:$O33=$B18,COUNTIF($B2:B2,$N2:$N33)),0)),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy across to L2

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 13
    Last Post: 10-22-2019, 02:14 AM
  2. Replies: 1
    Last Post: 05-09-2013, 02:05 AM
  3. Replies: 2
    Last Post: 10-08-2012, 05:17 PM
  4. [SOLVED] Creating a grocery list in a new worksheet based on selected items in a master list
    By jacolli4 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-28-2012, 07:53 AM
  5. Replies: 1
    Last Post: 07-16-2010, 02:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1