+ Reply to Thread
Results 1 to 6 of 6

Return a list of names in a column based on an 'x' in one of several other columns

  1. #1
    Registered User
    Join Date
    06-15-2008
    Posts
    11

    Return a list of names in a column based on an 'x' in one of several other columns

    I spent the past few hours attempting to figure this out, but VLOOKUP doesn't seem to be the solution, nor does INDEX, so I'm reaching out for help, and would greatly appreciate any assistance that can be offered:

    As an example, I've included an attachment.

    I have a list of individuals in column A and a list of traits in columns B through F, at least one of these traits applying to each individual.

    I wonder if it is possible to list somewhere in the workbook (on the same sheet or not, no preference) of the names that have an 'x' for each respective trait without having blanks where this isn't a match. So, for instance, for 'Male', i would like "Homer, Bart, SantasLittleHelper, Barney, Moe" to list either under that heading or somewhere else under a 'Male' headling.

    I thought initially to simply use =IF(B2="x",A2,"") and copy down and over, but then I would end up with a lot of blank lines, which I would like to avoid. (Alternately, if there's a way to take those =IF results and eliminate the spaces to get a fluid list, that would work too).

    I then tried =CONCATENATE(IF(B2="x",A2,""),CHAR(10),IF(B3="x",A3,""),CHAR(10) ... in a set of merged, text-wrapped rows in each column, but that didn't seem to be the most efficient way.

    Any help, again, would be greatly appreciated--I really want to learn the trick to this if it exists.

    Thank you!
    Attached Files Attached Files
    Last edited by needsomehelpher; 01-19-2012 at 09:29 AM.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Return a list of names in a column based on an 'x' in one of several other column

    you can do it like this
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Return a list of names in a column based on an 'x' in one of several other column

    See the attached.
    Attached Files Attached Files
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Return a list of names in a column based on an 'x' in one of several other column

    probably best not to use the array type formula unless really neccesary,
    its not easy to maintain,its not easy to reproduce if you dont know what you are doing,
    it can be expensive in terms of calculation time.
    helpers are nearly always easier to understand/reproduce for the less experienced user. i very rarely use an array if it can be avoided,(well except maxif) ok sometimes a user seems to think helpers are untidy! but they can always be hidden away on another sheet.

  5. #5
    Registered User
    Join Date
    06-15-2008
    Posts
    11

    Re: Return a list of names in a column based on an 'x' in one of several other column

    I thank you both for your help. While I ultimately used Haseeb's suggested method, I will keep your comment in mind, Martin, as I (hopefully) progress with more complex Excel formula building. I chose Haseeb's because of its simplicity (although apparently not simple enough for me to arrive at that conclusion myself), and also because I could understand the formula.

    As it is hard enough for me to give up figuring a solution out for myself and asking another to help, it's infinitely harder for me to simply take another's work/answer and copy/paste. I wanted to understand how to do this as well as return the completed spreadsheet to a coworker in a timely manner.

    Martin I will review your sheet again when I have some time today to better understand the method you employed and think about how I could use this in the future for other things. I assume "helper" is a term you use for a separate sheet or space that adds, calculates, or converts text to numbers so that the primary sheet can use it for formulas, right?

    Thanks again you two.

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Return a list of names in a column based on an 'x' in one of several other column

    yes i do mean helper as a seperate step that usually completes one step of a complex calculation then use another basic formula to get the final result. when i first used formulas to return multiple results from a range i used the array functions an have loads of examples with small(if blah blah. but imo its easier/quicker to use helpers

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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