Complex Index match or sumproduct?

Melimob

Active Member
Joined
Oct 16, 2011
Messages
363
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?):

TimelineColumn1ARGENTINABRAZIL
Imp Type ININ
Client Notification DateSTART10-Jun-1917-Jun-19
Client Involvement TimelineWeeks1414
Launch DateGO LIVE02-Sep-1909-Sep-19
PLANNINGWk 1 - 4From 10-Jun-19 To 08-Jul-19From 17-Jun-19 To 15-Jul-19
IMPLEMENTATIONWk 4 - 11From 08-Jul-19 To 26-Aug-19From 15-Jul-19 To 02-Sep-19
OBT UAT TestingWk 7 - 8From 29-Jul-19 To 05-Aug-19From 05-Aug-19 To 12-Aug-19
Communication Phase 2 & 3Wk 9 - 12From 12-Aug-19 To 02-Sep-19From 19-Aug-19 To 09-Sep-19
POST-IMPLEMENTATIONWk 12 - 14From 02-Sep-19 To 16-Sep-19From 09-Sep-19 To 23-Sep-19

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>


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

Country10-Jun17-Jun24-Jun01-Jul08-Jul15-Jul22-Jul29-Jul05-Aug12-Aug19-Aug26-Aug02-Sep09-Sep16-Sep23-Sep30-Sep
ARGENTINASTARTPLANPLANPLANPLANIMPIMPIMPIMPIMPIMPIMPGOPOSTPOST
BRAZIL STARTPLANPLANPLANPLANIMPIMPIMPIMPIMPIMPIMPGOPOSTPOST

<colgroup><col><col><col><col><col span="2"><col span="3"><col span="3"><col><col span="5"></colgroup><tbody>
</tbody>

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!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Melimob

Active Member
Joined
Oct 16, 2011
Messages
363
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?

Country10-Jun17-Jun
ARGENTINASTART
BRAZIL

<colgroup><col><col><col></colgroup><tbody>
</tbody>

many thanks for any advice!
 

Melimob

Active Member
Joined
Oct 16, 2011
Messages
363
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

<tbody>
</tbody>

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
 

Forum statistics

Threads
1,089,296
Messages
5,407,431
Members
403,143
Latest member
CTremblay

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top