+ Reply to Thread
Results 1 to 21 of 21

Making Individual Schedules Based on Master Matrix

  1. #1
    Registered User
    Join Date
    08-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    10

    Making Individual Schedules Based on Master Matrix

    Hello, thanks in advance for your help.

    What I have: a table with the Row Headings being names of employees, Column Headings are Dates, and the table data is a letter representing location they are working that day.
    Schedule example image input.png

    What I want: a separate sheet where I can type the name of the person into a cell (similar to vlookup) and the output of typing that is the dates that they work and corresponding location
    Schedule example image output.png

    I have tried VLOOKUP, but I can't get it to work with the blank spaces and having more than one output, one of which being the column heading.
    Thanks!

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Making Individual Schedules Based on Master Matrix

    I1 = Alfie

    I2 =01-01

    J2 =INDEX($A$1:$E$7,MATCH($H$1,$A$1:$A$7,0),MATCH($H2,$A$1:$E$1,0)) and drag down.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    08-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    10

    Re: Making Individual Schedules Based on Master Matrix

    Well, the dates are actually not 01-01, they are varied and 60 dates per sheet, so 60 columns. Since each person only works 21 of those, I don't want to type in every date that they work. I would like it to look up each date and corresponding letter.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Making Individual Schedules Based on Master Matrix

    You get better help on your question if you add a small excel file, without confidential information.

    Please also add manualy the expected result in your file.

    To Attach a File:

    1. Scroll down to the window below your post Additional Options
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.

  5. #5
    Registered User
    Join Date
    08-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    10

    Re: Making Individual Schedules Based on Master Matrix

    I've attached it. The sheet1 is the example of the schedule that we currently have, names changed.
    The sheet2 is a shortened example of what I would like it to look like. I would ideally like to input a name into the box and have the dates and locations populate.

    Thanks!
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Making Individual Schedules Based on Master Matrix

    In your sheet you can't see in row 2 which data it is I2 and AN2 are both 1.

    which is from september and which is from oktober?

  7. #7
    Registered User
    Join Date
    08-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    10

    Re: Making Individual Schedules Based on Master Matrix

    September is first (I2) and October is second (AN2).

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Making Individual Schedules Based on Master Matrix

    with Pivot Query.

    now you can filter on the name in column A (in sheet Oeldere)

    See the attached file.

    Power querry is probably an add in for Excel 2013 (but is available in Excel 2016).

    See if this suits your question.
    Last edited by oeldere; 08-16-2017 at 05:38 PM. Reason: an add inn

  9. #9
    Registered User
    Join Date
    08-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    10

    Re: Making Individual Schedules Based on Master Matrix

    Yes, that will do nicely! Can you show me how you did that?

  10. #10
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Making Individual Schedules Based on Master Matrix

    I googled for Power query excel 2013 and found this link

    https://www.microsoft.com/nl-NL/down....aspx?id=39379

    Power Query is an add in that needs to be installed to use this functionality.

    Have you Power Quary add in installed?

  11. #11
    Registered User
    Join Date
    08-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    10

    Re: Making Individual Schedules Based on Master Matrix

    Also, is there a way to do it so that it looks up data on multiple sheets? I have the same sheet multiple times, just with different numbers at the top.

  12. #12
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Making Individual Schedules Based on Master Matrix

    if you have power query I believe you can use that also on multiple worksheets (but I am not sure).

    But if not, you can make such a sheet and combine it for all data in a master sheet.

  13. #13
    Registered User
    Join Date
    08-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    10

    Re: Making Individual Schedules Based on Master Matrix

    I am actually on a work computer and forbidden from installing outside programs. I could do it from home, but will it work on my work computer if I don't have the program? Is there a way to do it without the program?

  14. #14
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Making Individual Schedules Based on Master Matrix

    Selemat,

    you may try to use array formula,

    it look like what oeldere's workbook, but array formula may cause the lag.

    in A2, paste this array formula,

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


    it change the table cell that not blank to row number (B5, C5, B6, "",F3 > 5,5,6,"",3) and use SMALL function to sort the row (3,5,5,6,"") and use INDEX to display the name. (Kel, Ash, Ash, Fish,"")

    in B2, paste this array formula,

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


    similar to above but the small(array,K) is change by repeat of current name (1,2,3,1,2,1,2,3,4,5,...)

    in C2 paste this formula,
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    normal index function, (=index([array], match(),match())

    PS: array formula is need to confirm by Ctrl + Shift + Enter instead of Enter only

    If not understand, please follow below step:

    1, Paste the formula to the cell
    2, Double click on the cell
    3, press Ctrl + Shift + Enter
    It show { sign in front of formula. (ie {=index(.....)
    Attached Files Attached Files
    Hope you can learn every time you visit here.

    If you still confuse on how it work, kindly ask or go to
    i) Formula - Formula (Ribbon) > Formula Auditing (Section) > Evaluate Formula > Evaluate; or
    ii) VBA/Code - Click F8 to see how it work step by step.

    It it take care of your question, Please:
    Mark tread as [Solved] [Thread Tools->Mark thread as Solved]
    ;and
    Click *Add Reputation to thank anyone solved your question.

  15. #15
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Making Individual Schedules Based on Master Matrix

    a bit of out of topic...

    If you want to sort the table, I encourage you to copy and paste to new sheet first.

    To avoid lag due to recalculation of formula.

  16. #16
    Registered User
    Join Date
    08-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    10

    Re: Making Individual Schedules Based on Master Matrix

    Quote Originally Posted by BoredWorker View Post
    Selemat,

    you may try to use array formula,

    it look like what oeldere's workbook, but array formula may cause the lag.

    in A2, paste this array formula,

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


    it change the table cell that not blank to row number (B5, C5, B6, "",F3 > 5,5,6,"",3) and use SMALL function to sort the row (3,5,5,6,"") and use INDEX to display the name. (Kel, Ash, Ash, Fish,"")

    in B2, paste this array formula,

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


    similar to above but the small(array,K) is change by repeat of current name (1,2,3,1,2,1,2,3,4,5,...)

    in C2 paste this formula,
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    normal index function, (=index([array], match(),match())

    PS: array formula is need to confirm by Ctrl + Shift + Enter instead of Enter only

    If not understand, please follow below step:

    1, Paste the formula to the cell
    2, Double click on the cell
    3, press Ctrl + Shift + Enter
    It show { sign in front of formula. (ie {=index(.....)


    Thanks for your reply! I'm having trouble when I try to apply this to my real document rather than the example. All I've done is to change the reference cells/sheets, and when I do that, I get a reference error. Any idea what I'm doing wrong?

  17. #17
    Registered User
    Join Date
    08-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    10

    Re: Making Individual Schedules Based on Master Matrix

    Hey all, still confused. The method that BoredWorker suggested worked in the sample but not when I tried to transfer over to my real worksheet. I changed all the reference values and got a reference error.

    Any other suggestions?

  18. #18
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,865

    Re: Making Individual Schedules Based on Master Matrix

    Not to confuse matters more, but here's another array formula solution.

    In the summary sheet C1, next to the Name put the start month from the data sheet ... 9 in this case.

    Then array enter (Ctrl + Shift + Enter) this in A2 and fill down @45 rows. It returns the relevant dates.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then for the locations array enter this in B2 and fill down the same.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    There is also a drop down in B1.
    Dave

  19. #19
    Registered User
    Join Date
    08-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    10

    Re: Making Individual Schedules Based on Master Matrix

    Thanks Dave!

    I still can't get it to work, what am I doing wrong? I'm doing the following (to my real sheet):
    1. Making new sheet next to the sheet I want the data from.
    2. Copying your formula into the new sheet.
    3. Changing the references to the sheet I want the data from, the values in the table.

    What am I doing wrong?

  20. #20
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,865

    Re: Making Individual Schedules Based on Master Matrix

    I can't say without seeing the workbook in question.

    Are you array entering the formulas?

    Edit Twice now you've mentioned changing all the references (BoreWorker's and mine) and it still doesn't work. You also mentioned #REF! errors. Are changing the sheet references as well?
    Last edited by FlameRetired; 08-18-2017 at 06:32 PM.

  21. #21
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Making Individual Schedules Based on Master Matrix

    Maybe the sheet name problem?

    Did your sheet name got space between?
    (ie Pro and Cons)

    then the formula should be
    'Pro and Cons'!A1

    instead of

    Pro and Cons!A1

+ 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. Making a matrix out of a master data table
    By AlexNorman100 in forum Excel General
    Replies: 2
    Last Post: 06-01-2017, 12:41 PM
  2. Copy a cell value from a Master to individual sheets based on Sheet Name
    By melgra13 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-09-2017, 05:55 PM
  3. Replies: 1
    Last Post: 07-14-2015, 01:02 AM
  4. Pickleball tournament bracket - macro to create individual player schedules
    By dhhume in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-23-2015, 12:36 PM
  5. Making a MASTER-FILE that collects individual file information
    By InterstateRentals in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-19-2013, 09:10 AM
  6. move data from master list to individual lists based on value in 'Unit' cell
    By nforgey in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-28-2013, 07:26 PM
  7. Making a Bubble Chart based on n-values matrix
    By Haydar in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-08-2005, 07:05 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