Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Dynamic unique lists
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamic unique lists

    Hi all
    I am trying to create a series of dynamic lists with data from a table searching on 1 criteria to return a second set of data

    Example data set

    Make Item
    200mm ICP module
    300mm DEP module
    200mm DSiv module
    200mm DEP module
    300mm Synapse module
    200mm DEP module
    300mm ISOPOD module

    The lists would then need to look like this

    200mm 300mm
    ICP module DEP module
    DSiv module Synapse module
    DEP module ISOPOD module

    The source data is in a table that as it gets added to the 2 lists need to auto update. The search field for each list needs to be the Make data in the table.

    Prefer if it isn’t in a CSE array format

    Regards
    Stephen
    Last edited by sadavies2906; Oct 21st, 2019 at 03:39 PM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,112
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Dynamic unique lists

    Something like

    ABCFG
    1MakeItem200mm300mm
    2200mmICP moduleICP moduleDEP module
    3300mmDEP moduleDSiv moduleSynapse module
    4200mmDSiv moduleDEP moduleISOPOD module
    5200mmDEP moduleDEP module
    6300mmSynapse module
    7200mmDEP module
    8300mmISOPOD module

    Master



    Worksheet Formulas
    CellFormula
    F2=IFERROR(INDEX($B$2:$B$8,AGGREGATE(15,6,(ROW($A$2:$A$8)-ROW($A$2)+1)/($A$2:$A$8=F$1),ROWS(F$2:F2))),"")



    Formula filled down & across.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Feb 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic unique lists

    Thank you this gives me almost everything I am after, is there anyway once and occurrence of the item is recorded it doesn't show it again?

    in the 200mm list "DEP Module" appears twice and I only need to see it once?

    cheers
    stephen

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,873
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Dynamic unique lists

    Try this macro:

    Code:
    Sub Dynamic_unique_lists()
      Dim c As Range, f As Range, lc As Long, lr As Long
      Range("D1", Cells(Rows.Count, Columns.Count)).ClearContents
      For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        Set f = Range("D1", Cells(1, Columns.Count)).Find(c, , xlValues, xlWhole)
        If Not f Is Nothing Then
          lr = Cells(Rows.Count, f.Column).End(xlUp).Row + 1
          lc = f.Column
          Set f = Range(Cells(1, lc), Cells(lr, lc)).Find(c.Offset(, 1), , xlValues, xlWhole)
          If f Is Nothing Then
            Cells(lr, lc) = c.Offset(, 1)
          End If
        Else
          lc = Cells(1, Columns.Count).End(xlToLeft).Column + 1
          If lc < 4 Then lc = 4
          Cells(1, lc) = c
          Cells(2, lc) = c.Offset(, 1)
        End If
      Next
    End Sub
    result example
     ABCDEF
    1MakeItem 200mm300mm400mm
    2200mmICP module ICP moduleDEP moduleICP module
    3300mmDEP module DSiv moduleSynapse module 
    4200mmDSiv module DEP moduleISOPOD module 
    5200mmDEP module    
    6300mmSynapse module    
    7200mmDEP module    
    8300mmISOPOD module    
    9400mmICP module    



    HOW TO INSTALL MACROs

    ------------------------------------
    If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Dynamic_unique_lists) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
    Regards Dante Amor

  5. #5
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,722
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Dynamic unique lists

    Hi

    Another formula solution, without repeating values. In D2 (using a table with DanteAmor's layout):

    =IFERROR(INDEX($B$2:$B$20,MATCH(TRUE,IF($A$2:$A$20=D$1,ISERROR(MATCH($B$2:$B$20,D$1:D1,0))),0)),"")
    Confirmed with cse
    Copy down and across
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  6. #6
    New Member
    Join Date
    Feb 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic unique lists

    Thank you very much this works perfectly. Would it be easy to change if you wanted to add a 2nd criteria.

    ie if a QUARTER column was added with fields such as Q3-19 or Q4-19



    Regards
    Stephen

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,873
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Dynamic unique lists

    I don't know what solution worked for you. I don't understand the fourth column, maybe if you give examples of what you have and what you expect as a result.

    Upload an excel image:
    https://www.mrexcel.com/forum/board-...forum-use.html
    Regards Dante Amor

  8. #8
    New Member
    Join Date
    Feb 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic unique lists

    Sorry Dante I went with PGC’s solution as I didn’t want to go down the macro route. I’m designing a spreadsheet that other users need to be able to update and the levels of excel skill vary so trying to keep it as simple as possible. I will try to upload a screenshot of what I’m after thought I’d done it earlier.
    regards
    stephen

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,873
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Dynamic unique lists

    Don't worry, I'm here to help you.
    You can also upload a file or image to the cloud:

    You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
    Regards Dante Amor

  10. #10
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,722
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Dynamic unique lists

    Hi
    If I understood correctly you just have to add another IF().

    Ex., in F3:

    =IFERROR(INDEX($C$2:$C$20,MATCH(TRUE,IF($A$2:$A$20=F$1,IF($B$2:$B$20=F$2,ISERROR(MATCH($C$2:$C$20,F$2:F2,0)))),0)),"")

    confirmed with cse
    copy down and across



     ABCDEFGHIJ
    1MakeQuarterItem Make200mm200mm300mm300mm 
    2200mmQ3-19ICP module QuarterQ3-19Q4-19Q3-19Q4-19 
    3300mmQ3-19DEP module  ICP moduleDEP moduleDEP moduleISOPOD module 
    4200mmQ3-19ICP module  DEP module Synapse moduleICP module 
    5200mmQ3-19DEP module  Synapse module    
    6300mmQ3-19Synapse module       
    7200mmQ4-19DEP module       
    8300mmQ4-19ISOPOD module       
    9300mmQ4-19ICP module       
    10200mmQ3-19ICP module       
    11200mmQ3-19DEP module       
    12200mmQ3-19Synapse module       
    13          
    14          
    [Book2]Sheet2
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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