+ Reply to Thread
Results 1 to 10 of 10

Transforming a Schedule -- A Different Kind of Multiple Criteria Lookup?

  1. #1
    Registered User
    Join Date
    08-19-2014
    Location
    San Francisco Bay Area, California
    MS-Off Ver
    Excel 2010
    Posts
    13

    Transforming a Schedule -- A Different Kind of Multiple Criteria Lookup?

    I have a schedule of different events which occur throughout the year, over a number of years. Columns D through BD are the week numbers (1-52). The events are listed in Column C, and the year in Column A. A mock-up of this schedule is on Source sheet of the attached file.

    How can the schedule on the Target sheet of the attached file be populated from the Source sheet?

    What challenges me is that the year and event are on the same row with week numbers spread across the columns on the source schedule, but only one event is on a given row with year and week numbers spread across the columns on the target schedule.

    I've looked at 2-way lookup solutions (INDEX MATCH and VLOOKUP MATCH) and multi-condition vlookup, but these seem to depend on the criteria being on the same row.

    Note: I'm not sure I'm attaching the spreadsheet properly. The paperclip icon only opens/closes a narrow rectangle, and I'm not sure that Manage Attachments is attaching the file to this thread.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    08-19-2014
    Location
    San Francisco Bay Area, California
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Transforming a Schedule -- A Different Kind of Multiple Criteria Lookup?

    Manage Attachments worked!

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Transforming a Schedule -- A Different Kind of Multiple Criteria Lookup?

    I think this is what you are after, but as you gave no expected results, I'm not entirely sure.

    In B6 of the target sheet, copied down and across for 2016:

    =SUMIF(Source!$C$7:$C$39,Target!$A6,Source!D$7:D$39)

    It's not clear how the other years will be dealt with, but you can change the source sheet reference for additional years.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    08-19-2014
    Location
    San Francisco Bay Area, California
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Transforming a Schedule -- A Different Kind of Multiple Criteria Lookup?

    The desired result is to populate the Target sheet with which Events are happening in which Week of the corresponding Year based on the information in the Source sheet. See new attachment.

    Based on the sample schedule file I was provided I see there are 2 additional wrinkles:
    1. I replaced the 2's with 1's, because only 1 event at a time (Week) can occur in a given place.
    2. One exception is that Event 1 can occur in the same week at Place 1 and Place 2. To separate those, I'll take Event 1 at Place 1 and call it Event1a and rename Event 1 at Place 2 as Event1b.
    Please see the Target sheet in file Get Schedule 2.xlsx for desired results.

    Thank you!
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Transforming a Schedule -- A Different Kind of Multiple Criteria Lookup?

    In B6

    =IFERROR(INDEX(Source!$D$7:$BC$39,MATCH(1,(Target!$A6=Source!$C$7:$C$39)*(Target!B$4=Source!$A$7:$A$39),0),MOD((COLUMNS($B:B)-1),52)+1),"")

    Enter with Ctrl+shift+Enter

    Copy down and across

  6. #6
    Registered User
    Join Date
    08-19-2014
    Location
    San Francisco Bay Area, California
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Transforming a Schedule -- A Different Kind of Multiple Criteria Lookup?

    Splendid! Works like a charm and does exactly what's needed. Thank you, Mr. Topley!

    I will have to spend some time going through the formula so I understand how it works, but I do have one question: If there's no row for an event in a given year, the target cells are blank (which is perfect). If there IS a row, then the cells with no event for the week are populated with a '0' instead of being blank as I would expect from the empty quotes at the end. Of course, I can get rid of the 0's with a global replace, but any idea why the 0's? I'm thinking it's because the first part of the IFERROR is calculating a value and it's coming up with 0. Is that correct?

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Transforming a Schedule -- A Different Kind of Multiple Criteria Lookup?

    You can hide the 0s by formatting the cells as

    0;0;;
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-19-2014
    Location
    San Francisco Bay Area, California
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Transforming a Schedule -- A Different Kind of Multiple Criteria Lookup?

    VERY smooth.
    I found http://www.exceltactics.com/definiti...formats-excel/ to be a helpful explanation.

    Thanks again!

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Transforming a Schedule -- A Different Kind of Multiple Criteria Lookup?

    Assuming it is, could you please mark the thread as solved ("Thread Tools" at top of first post").

    Thanks again.

  10. #10
    Registered User
    Join Date
    08-19-2014
    Location
    San Francisco Bay Area, California
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Transforming a Schedule -- A Different Kind of Multiple Criteria Lookup?

    Absolutely - done!

+ 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] looking for kind a vlookup with multiple search and compare criteria
    By vba4ever in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-05-2016, 03:10 AM
  2. Replies: 8
    Last Post: 02-08-2015, 09:08 AM
  3. [SOLVED] Lookup Formula with 2 criteria to build schedule
    By Maglin in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-07-2014, 03:27 AM
  4. Schedule to count multiple lines and columns to meet two criteria
    By lsudecat04 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-28-2013, 05:41 AM
  5. Replies: 11
    Last Post: 02-07-2013, 02:22 AM
  6. Replies: 1
    Last Post: 09-19-2011, 08:07 PM
  7. [SOLVED] help using lookup and some kind of ranking criteria
    By my in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-18-2006, 12:45 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