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!
 

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,078,546
Messages
5,341,093
Members
399,418
Latest member
joterde

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top