Results 1 to 3 of 3

Thread: Complex Index match or sumproduct?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2011
    Posts
    352
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Complex Index match or sumproduct?

    Hi

    I'm stuck. I have a table where I want to read the dates depending on country and row type.

    THEN - populate this on another table under the relevant date with a code.

    So here's my table I am reading from (sorry I can't paste it as a table for some reason?):

    Timeline Column1 ARGENTINA BRAZIL
    Imp Type IN IN
    Client Notification Date START 10-Jun-19 17-Jun-19
    Client Involvement Timeline Weeks 14 14
    Launch Date GO LIVE 02-Sep-19 09-Sep-19
    PLANNING Wk 1 - 4 From 10-Jun-19 To 08-Jul-19 From 17-Jun-19 To 15-Jul-19
    IMPLEMENTATION Wk 4 - 11 From 08-Jul-19 To 26-Aug-19 From 15-Jul-19 To 02-Sep-19
    OBT UAT Testing Wk 7 - 8 From 29-Jul-19 To 05-Aug-19 From 05-Aug-19 To 12-Aug-19
    Communication Phase 2 & 3 Wk 9 - 12 From 12-Aug-19 To 02-Sep-19 From 19-Aug-19 To 09-Sep-19
    POST-IMPLEMENTATION Wk 12 - 14 From 02-Sep-19 To 16-Sep-19 From 09-Sep-19 To 23-Sep-19


    Then I want it to appear here as shown (then I will apply conditional formatting to colour code the different parts of the plan):

    Country 10-Jun 17-Jun 24-Jun 01-Jul 08-Jul 15-Jul 22-Jul 29-Jul 05-Aug 12-Aug 19-Aug 26-Aug 02-Sep 09-Sep 16-Sep 23-Sep 30-Sep
    ARGENTINA START PLAN PLAN PLAN PLAN IMP IMP IMP IMP IMP IMP IMP GO POST POST
    BRAZIL START PLAN PLAN PLAN PLAN IMP IMP IMP IMP IMP IMP IMP GO POST POST

    I think I may need this as a helper table and lookup but since I have so many countries I would rather try and read from source.
    I've played around with INDEX MATCH but need it to find the week and write under it the code.

    Any help greatly appreciated!
    thank you!

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Posts
    352
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex Index match or sumproduct?

    OK so I've made a start but this works for the first row and then gives wrong results for following columns?
    I can't understand why as I fixed the range and columns to drag across?

    =IF(COUNTIFS(Table5[[#Headers],[ARGENTINA]:[VIETNAM]],Table2[@[Country]:[Country]],Table5[@[ARGENTINA]:[VIETNAM]],Table2[[#Headers],[10-Jun]])>0,"START","")

    this is what is in my second column
    =IF(COUNTIFS(Table5[[#Headers],[ARGENTINA]:[VIETNAM]],Table2[@[Country]:[Country]],Table5[@[ARGENTINA]:[VIETNAM]],Table2[[#Headers],[17-Jun]])>0,"START","")

    This is what I'm getting which is correct for Argentina but Brazil should have START under 17-Jun?
    Also another country has a start date of 2-Sep but it's appearing as START using above formula under 16-Sep? I can't figure out why?

    Country 10-Jun 17-Jun
    ARGENTINA START
    BRAZIL

    many thanks for any advice!

  3. #3
    Board Regular
    Join Date
    Oct 2011
    Posts
    352
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex Index match or sumproduct?

    Ok so I solved the previous issue and here's my formula:

    =IF(COUNTIFS(Table5[[#Headers],[ARGENTINA]:[VIETNAM]],Table2[@[Country]:[Country]],'Client Engagement Transition'!$D$4:$Z$4,Table2[[#Headers],[10-Jun]])>0,"START",IF(COUNTIFS(Table5[[#Headers],[ARGENTINA]:[VIETNAM]],Table2[@[Country]:[Country]],'Client Engagement Transition'!$D$6:$Z$6,Table2[[#Headers],[10-Jun]])>0,"GO",IF(COUNTIFS(Table5[[#Headers],[ARGENTINA]:[VIETNAM]],Table2[@[Country]:[Country]],'Client Engagement Transition'!$D$7:$Z$7,Table2[[#Headers],[10-Jun]])>0,"PLAN","")))

    This works for 'START', 'GO' however 'PLAN' date is contained within text.

    E.g.:
    From 10-Jun-19 To 08-Jul-19

    So it's not picking it up. I'm thinking to is number search but since I want to return all the weeks within 10-jun to 8-jul as 'PLAN' , perhaps it's better to say find the start date and mark all 4 following columns as 'PLAN'?

    can someone help with this piece? many thanks

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •