+ Reply to Thread
Results 1 to 4 of 4

Generate list from column A for all B cells containing number C

  1. #1
    Registered User
    Join Date
    10-22-2012
    Location
    Great Barrington, MA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Generate list from column A for all B cells containing number C

    I have a sheet with a list of names (A), followed by a cell (B) which contains one or more years separated by commas.

    I want to generate a list on a separate sheet of all names from A when B contains the number in D:

    "Data" sheet:
    A B
    1 John Doe 1998, 2005, 2014
    2 Jane Doe 2012, 2014, 2017
    3 George Spelvin 2005, 2012, 2017

    "List" sheet:
    A B
    1 2005 2012
    2 John Doe Jane Doe
    3 George Spelvin George Spelvin

    I received a suggestion of the following formula, in cell A2 of the list table:
    IF(A$1="","",IFERROR(INDEX(Data!$A$1:$A$3,AGGREGATE(15,6,(ROW(Data!$B$1:$B$3)-ROW(Data!B$1)+1)/ISNUMBER(FIND(A$1,Data!$B$1:$B$3)),ROWS(A$2:A2))),""))

    Unfortunately it did not work. I'm new at this and unable to diagnose what would need fixing. Much obliged for any suggestions.
    Last edited by jnelson522; 11-01-2017 at 02:22 PM. Reason: add grid to table

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Generate list from column A for all B cells containing number C

    Hello and welcome to the forum.

    One important thing to note is your use of multiple values in the same cell. In Excel, there should only be one value per cell in your data.

    While it is possible to work with poorly formatted data, it complicates things unnecessarily.

    That being said, you can try this in A2 of the 'List' worksheet:

    =IFERROR(INDEX(Data!$A$1:$A$3,SMALL(IF(ISNUMBER(SEARCH(A$1,Data!$B$1:$B$3)),ROW(Data!$A$1:$A$3)-(ROW(Data!$A$1)-1)),ROWS($A$1:$A1))),"") Ctrl Shift Enter

    Drag the formula to the right and down as far as needed.

  3. #3
    Registered User
    Join Date
    10-22-2012
    Location
    Great Barrington, MA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Generate list from column A for all B cells containing number C

    Thank you - that worked!

    Much obliged,
    John

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Generate list from column A for all B cells containing number C

    You're welcome. Thanks for the rep!
    Last edited by 63falcondude; 11-01-2017 at 04:08 PM. Reason: Rep added

+ 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. Auto-Generate List from reference cells based on data validation list selection
    By BoundCustomDesign in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-13-2014, 11:06 AM
  2. [SOLVED] Generate a list number of visits at different times in a day
    By TPS in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-24-2014, 11:16 PM
  3. Replies: 8
    Last Post: 11-18-2013, 04:24 PM
  4. Generate multiple lists without the same last and first number of each list...
    By Dreamweaver8882004 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-06-2013, 08:49 PM
  5. Replies: 2
    Last Post: 03-01-2013, 02:01 AM
  6. Replies: 0
    Last Post: 06-25-2012, 07:21 AM
  7. Generate a unique number of list
    By Terence Chan in forum Excel General
    Replies: 3
    Last Post: 09-14-2005, 02:48 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