+ Reply to Thread
Results 1 to 9 of 9

Building subarrays with multiple criteria

  1. #1
    Registered User
    Join Date
    05-28-2014
    Posts
    4

    Building subarrays with multiple criteria

    I have a spreadsheet which keeps track of all staff, clients, and related data. In the 'Attendance' tab, there is a list of clients that are seperated by their appropriate ratio (e.g. 1:1, 2:1, 4:1). In addition, the clients' weekly schedule varies from one day to the next (e.g. M, T, W, R, F). I am attempting to build a subarray based on both the ratio (e.g. "4:1") and daily schedule (e.g. "R"). I have had success with using just the ratio, and just the schedule, but when I combine the two, it doesn't produce the desired results. Both the formula and array are listed below:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Client Data.png

    Note: The "R" that I was using today to check for Thursday will eventually be substituted with the following formula so that the list updates automatically each day.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Thank you in advance for your assistance.

    jbalan

  2. #2
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Building subarrays with multiple criteria

    Hi,

    Welcome to the Forum.

    Can you upload a sample workbook with insensitive data instead of uploading screenshots ?
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  3. #3
    Registered User
    Join Date
    05-28-2014
    Posts
    4

    Re: Building subarrays with multiple criteria

    Hello Saarang84,

    Thank you for such a speedy reply, and I will remember to upload a sample workbook in the future. While there are more tabs that reference the client data, I have included the two I spoke about in the previous post (Clients & Attendance). In the 'Clients' tab, I have the two subarrays (Ratio & Weekdays) off to the left. In the 'Attendance' tab, I have populated the appropriate Client boxes with the 'Weekdays' formula. I would like to have these three boxes populated based upon both the appropriate ratio and the weekday that each client attends (e.g. '4.1' & 'R' Thursday). I have included a sample workbook.

    Sample Data.xlsx

    Thank you for your help

    jbalan

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Building subarrays with multiple criteria

    Like this then?
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  5. #5
    Registered User
    Join Date
    05-28-2014
    Posts
    4

    Re: Building subarrays with multiple criteria

    Thank you very much! That is exactly what I was looking for. The formula will now take the confusion out of which client and staff are supposed to be showing up on any given day.

  6. #6
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Building subarrays with multiple criteria

    Glad to hear it worked!

    Here is a updated version where I implemented your "IF" formula. Normally a VLOOKUP would be a better option but here it's enough with an INDEX.

  7. #7
    Registered User
    Join Date
    05-28-2014
    Posts
    4

    Re: Building subarrays with multiple criteria

    Thanks again Jacc, this is more succinct in determining the day of the week, and shortens my formula considerably.

  8. #8
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Building subarrays with multiple criteria


    '''''''''''

  9. #9
    Registered User
    Join Date
    10-27-2014
    Location
    NJ
    MS-Off Ver
    2007
    Posts
    2

    Re: Building subarrays with multiple criteria

    Hi, altered and truncatedTest Data.xlsx


    Need help finding a way to automatically rank from least to most expensive of the latest price only without deleting data. I will be inputing data all the time so it has to always change. Detailsdate is "timeline"), but compare the data with the criteria falls into the same category: type (usually electric or gas), utilities (many different ones), state, supplier (many different ones), usage bracket, and company.

+ 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. Building list from database based on a date criteria
    By Tuppie in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-18-2014, 01:43 AM
  2. Replies: 2
    Last Post: 10-08-2012, 12:49 PM
  3. Replies: 12
    Last Post: 09-23-2012, 06:41 PM
  4. Building upon an INDEX and MATCH search with expanded criteria.
    By parky58 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2012, 03:17 AM
  5. [SOLVED] Building AutoFilteer Macro with 3 criteria
    By arersando in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-26-2012, 02:13 AM

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