+ Reply to Thread
Results 1 to 10 of 10

Find items in a table having multiple matched values in another column

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

    Find items in a table having multiple matched values in another column

    In the attached workbook, I have a list of employees, supervisors, managers, and directors. I need to create a procedure to determine which teams an end user has access to. The security model works like this:
    • For access to be granted on a Supervisor team, an end user must have access to all employees under that Supervisor.
    • For access to be granted on a Manager team, an end user must have access to all employees under that Manager.
    • For access to be granted on a Director team, an end user must have access to all employees under that Director.

    In the sample file, the permissions granted to EndUser1 include all employees that report up to:
    • Supervisors Sina Loadholt, Amos Trueheart, Toya Hulsey, and Marry Dutcher
    • Managers Booker Wilham and Madeline Donnis
    • Director Santo Treloar

    Also in the sample file, I have a table in H1:I4 that contains static values right now. Using the Director level as an example, this is the result I want to create programmatically and generally would not be available in the source data. I'm thinking I need to generate the list of Directors using VBA since Directors may be added, removed, or changed at any time and the number of directors at a given time can also change. The values in the EndUser1 Access column could be obtained with VBA or an Excel formula. So to determine that EndUser1 has access to Santo Treloar's team, that user is required to have a permission to match every employee reporting up to Santo Treloar. When that condition is met, the value should be set to "Granted." If even one employee was missing from the EndUser1 Permissions list, the value would be "Denied."

    I would very much prefer not to employ helper columns or array formulas.

    Thank you in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,878

    Re: Find items in a table having multiple matched values in another column

    Hi, let's see if I understand your question.

    The organization table contains all employees in column A and next to each are the managers, supervisors and directors

    So what you want is a dynamic list that when you select an end user you see directly under which Manager, Supervisor an Director this employee or end user falls
    Does this more or less sum up your request?

    The table in H:I is to be populated so if I change the column I header for the Employee or Endusername the rows below show granted or denied. Is this it?
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,878

    Re: Find items in a table having multiple matched values in another column

    Maybe too much but try this using vlookup
    Attached Files Attached Files

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

    Re: Find items in a table having multiple matched values in another column

    Thank you for the reply, Keebellah. I was afraid I might not have explained it well enough.
    Let me try to clarify what I'm after. In the workbook I'm using, the organization table and the list of permissions are already provided. It's true that the username in that list may change and the list of permissions along with it, but the header in column I shouldn't be factored in. Also, the end user will not be one of the employees in the list. Rather an end user will have been granted access to one or more employee records. In my original example, the current user has access to several employees, all of which report up to Santo Treloar. This user is granted access to Santo Treloar at the Director level only because she has been granted access to every employee under Santo. If we removed one employee, say Lulu Gately from the permissions list, the user would no longer have access to Santo's data. She would also lose access to Booker Wilham and Amos Truehart.

    However, she would still have access to data for Manager Madeline Donis (because she has permissions to ALL employees on Madeline's team). Similarly, she would still have access to Supervisors Sina Loadholt, Marry Dutcher, and Toya Hulsey. To provide one very specific example of the formula/ or VBA function I'm after, if we are trying to determine whether a user has access to manager Booker Wilham, we need to first determine ALL employees that report to Booker (Lulu Gately, Madie Lemay, Jeffrey Locke, Kayleigh Forgione, Nathaniel Goll) and check to see whether or not they are all in the permissions list. Only when we find every of them in the permissions list, do we return a value of "Granted."

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,878

    Re: Find items in a table having multiple matched values in another column

    Maybe it's clear to you, but not for me.
    Who is the EndUSer? is that the person logged in?
    I will have to take the time to read and re-read what you've written and see if it sinks in.

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

    Re: Find items in a table having multiple matched values in another column

    Yes, the end user is the person logged in, and that value comes from the VBA function: Environ$("username")
    From there, the list of permissions is queried from a table in the database for that user. All of that is already in place in my project, although those things are not clear in the sample file I provided.

    So my question is really only focused on how to determine whether or not all of a leader's employees appear in the list of permissions or not. As one more example, say we want to determine access at the supervisor level. Here are the requirements for these supervisors:

    Sina Loadholt: Return "Granted" if Kayleight Forgione appears in the permissions list AND Nathaniel Goll appears in the permissions list AND Jeffrey Locke appears in the permissions list.
    Amos Truehart: Return "Granted" if Madie Lemay appears in the permissions list AND Lulu Gately appears in the permissions list.
    Toya Hulsey: Return "Granted" if Lashawna Preston appears in the permissions list AND Frederick Saylor appears in the permissions list.

    So one thought I had that might use VBA would look something like:
    • Find all employees that report to Sina Loadholt and add them to an array
    • Iterate through that array and check for a match in the permissions list
    • Return a value of "Granted" only if a match was found for all items in the array

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,878

    Re: Find items in a table having multiple matched values in another column

    It's a bit cleare now, will take a look and see what I can do for you

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,878

    Re: Find items in a table having multiple matched values in another column

    Well, sorry, nor clear after all: what is a sample Username you have?
    I do not understand what Sina Loadholt has to do with it since there are more supervisors.
    I think the thoughts behind the entire matrix are too complicated for me.
    Where dos the Username come in the tblOrganization?

    Set a couple of sample names and maybe then I might understand.
    Remember you also have the option Application.User which is in most case not the same as the Evironment Username

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

    Re: Find items in a table having multiple matched values in another column

    The username really makes no difference for the question I'm asking. It will not be found in tblOrganization and wouldn't be reference in any formula or function. Let me try to simplify the example.

    In the new attachment, I've included only Employees and Supervisors, and fewer records. We now only have Supervisor teams for Sina Loadholt and Amos Truehart. The permissions list contains ALL of Sina's employees but only one of Amos'. Therefore, access is Granted to Sina Loadholt but not Amos Truehart. In order to have access (return "Granted"), you need access to every employee that reports to a supervisor. This condition is met for Sina Loadholt because all three of her employees appear in the Permissions list. The condition is not fully met for Amos Truehart because only one of his employees appears in the Permissions list, but one (Lulu Gately) is missing.

    I appreciate the effort you're putting in to ask questions and understand, hopefully this will make things a little easier.
    Attached Files Attached Files

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,878

    Re: Find items in a table having multiple matched values in another column

    Who is granted what? It's too abstract for me.
    Another thing, check your list of names some have at least 2 blanks behind them, therefore nor formula will be able to compare them.
    Will have to think what you are trying to ask.

+ 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. [SOLVED] Extract subarray from table column based on criteria matched - text values
    By jaryszek in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 08-31-2018, 03:35 AM
  2. Replies: 40
    Last Post: 08-29-2018, 01:36 PM
  3. Replies: 6
    Last Post: 05-21-2018, 04:58 AM
  4. Replies: 10
    Last Post: 09-16-2015, 11:25 PM
  5. Find matched values and put as comment
    By hasanlianar in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-14-2015, 02:04 AM
  6. Replies: 12
    Last Post: 12-28-2012, 07:49 PM
  7. [SOLVED] List unmatched items in 3rd column & sum matched items in 4th column
    By sharonvining in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-12-2012, 03:24 PM

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