+ Reply to Thread
Results 1 to 5 of 5

Generate unique list of values based on condition in another table column with VBA

  1. #1
    Registered User
    Join Date
    02-15-2019
    Location
    Sacramento, CA
    MS-Off Ver
    O365
    Posts
    5

    Generate unique list of values based on condition in another table column with VBA

    I have an Excel file with the following:

    A sheet named "org" containing a table called "tblOrg"
    "tblOrg" has three columns: "Employee", "Supervisor", and "Manager"

    A sheet named "team" with a named range "managerSelection" and an empty table "tblTeam" with only one column: "Direct Reports"

    based on the manager's name typed into managerSelection, I need to populate tblTeam with a unique list of supervisors that report to that manager. Ideally, I would like the list to be sorted alphabetically when I write it. I can't pre-sort the table because I will need to repeat this process for employees reporting to supervisors based on the same source table. I need the list to dynamically change whenever a new manager name is selected.

    Here is a sample from tblOrg (sample file also attached):

    Employee Supervisor Manager
    Paul Jon Lori
    Bob Jon Lori
    Jane Denise Lori
    Lucy Denise Lori
    Ken Julie Alex
    Maria Julie Alex
    Bill Cynthia Alex
    Ethan Cynthia Alex
    Wallace Heidi Kim
    Trevor Heidi Kim
    Karina Dennis Kim
    Erin Dennis Kim
    Christine Will Kim
    Devon Will Kim

    Based on the sample, if I put "Kim" into managerSelection, I should get the list as:

    Dennis
    Heidi
    Will

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor unit285's Avatar
    Join Date
    10-29-2015
    Location
    TN
    MS-Off Ver
    Office 365
    Posts
    349

    Re: Generate unique list of values based on condition in another table column with VBA

    Maybe something like this? Place in Sheet "Team" Module
    Please Login or Register  to view this content.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,846

    Re: Generate unique list of values based on condition in another table column with VBA

    Hi dabasir,

    There are some very new functions in Excel called Dynamic Array Formulas with Spill Ranges. If you have the latest version of Excel then you don't need VBA to do this problem. See:
    https://www.excel-university.com/cre...-spill-ranges/
    and
    https://www.excelcampus.com/function...-spill-ranges/

    See my answer in workbook attached.

    Dynamic Array Spill Validation.xlsm
    Last edited by MarvinP; 02-16-2019 at 01:13 AM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,047

    Re: Generate unique list of values based on condition in another table column with VBA

    To "Team" sheet code module
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,047

    Re: Generate unique list of values based on condition in another table column with VBA

    Thnnks for the rep.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. List unique column values from table in new table
    By PeterBSR in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-25-2018, 09:39 AM
  2. Replies: 2
    Last Post: 07-04-2017, 03:20 PM
  3. Replies: 14
    Last Post: 08-04-2016, 02:18 PM
  4. [SOLVED] VBA Creating a list of unique values from one column based on criteria from another column
    By bilbo85 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-09-2016, 02:38 PM
  5. [SOLVED] Data validation - unique list of table column values
    By michellepace in forum Excel General
    Replies: 6
    Last Post: 11-26-2015, 04:02 PM
  6. Counting unique values based on condition in a different column
    By rafuk73 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2014, 02:59 PM
  7. Generate Unique List from Pivot Table
    By dkang2007 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-02-2013, 03:06 PM

Tags for this Thread

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