HELP - Trying to build a Roadmap in Excel

[PaulFerris]

Hi all,

New to the forum and a novice-to-medium user of excel. I have been asked by my boss to create a Roadmap in excel and I need help!

There are two sheets - 'Roadmap' (to display the pretty formatted table) and 'Data' (to hold the exported data)

The way I want the end result to look like on the 'Roadmap' sheet something like this http://i.imgur.com/XE8bRub.png (although if there was someway to automatically

The raw values on the 'Data' tab looks something like this (it is always sorted by Portfolio and then by Launch Week - but if it would work a better way happy to change it)

[TABLE="width: 380"]
<tbody>[TR]
[TD]Launch Week[/TD]
[TD]Portfolio[/TD]
[TD]Project Name[/TD]
[/TR]
[TR]
[TD]20-Feb-17[/TD]
[TD]Apps[/TD]
[TD]Item 1[/TD]
[/TR]
[TR]
[TD]27-Feb-17[/TD]
[TD]Apps[/TD]
[TD]Item 2[/TD]
[/TR]
[TR]
[TD]27-Feb-17[/TD]
[TD]Apps[/TD]
[TD]Item 3[/TD]
[/TR]
[TR]
[TD]6-Mar-17[/TD]
[TD]Apps[/TD]
[TD]Item 6[/TD]
[/TR]
[TR]
[TD]6-Mar-17[/TD]
[TD]Apps[/TD]
[TD]Item 7[/TD]
[/TR]
[TR]
[TD]6-Mar-17[/TD]
[TD]Apps[/TD]
[TD]Item 9[/TD]
[/TR]
[TR]
[TD]13-Mar-17[/TD]
[TD]Apps[/TD]
[TD]Item 5[/TD]
[/TR]
[TR]
[TD]13-Mar-17[/TD]
[TD]Billing & Credit[/TD]
[TD]Item 8[/TD]
[/TR]
[TR]
[TD]8-May-17[/TD]
[TD]Billing & Credit[/TD]
[TD]Item 10[/TD]
[/TR]
</tbody>[/TABLE]

So what I need to happen is cell C5 on the 'Roadmap' would go and query the 'Data" sheet and retrieve the first Project it finds that is happening in the Launch Week of C4 and has a Portfolio of "Apps".

C6 would then be the same, except I want it to ignore the returned result in C5
C7 would then be the same, except I want it to ignore the returned results in C5 and C6..

etc

I hope that makes sense.

I just dont know where to begin in how to write the formula to populate the cells on the 'Roadmap' sheet. And if I need a macro I dont even know how to create those suckers

If any kind soul out there could help I would be very grateful

Thanks

 

[PaulFerris]

Just bumping this in the hopes someone can help

 

[Eric W]

Welcome to the forum!

Assuming your data on the Data tab is columns A:C, try this on the Roadmap tab:

	C	D
Apps
Item 2
Item 3

<tbody>
[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]27-Feb-17[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

</tbody>

Roadmap​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]{=IF(C4="","",IFERROR(INDEX(Data!$C$2:$C$20,SMALL(IF((Data!$A$2:$A$20=$C$4)*(Data!$B$2:$B$20=$D$4),ROW($A$2:$A$20)-ROW($A$2)+1),ROWS($C$5:$C5))),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Put that formula in C5, change the ranges to match your sheet, then confirm with Control+Shift+Enter. If you want, you can hardcode Apps in it, or move the cell it's in somewhere else. Then drag the formula down as far as needed.

 

[PaulFerris]

Hi Eric,

Thanks for the reply.... just trying to understand the formula so I can adjust the ranges to match my sheet.

Could I be a pest and ask for an explanation of what each part of the formula is doing so I understand what is going on here?

Thanks

 

[Eric W]

Sure. First keep in mind that this is an array formula, meaning that it works on an array (range) of cells instead of just one cell. Somewhere in the formula it will have 1 or more arrays, and the rest of the formula processing will end up getting a single value out of the array.

=IF(C4="","",IFERROR(INDEX(Data!$C$2:$C$20,SMALL(IF((Data!$A$2:$A$20=$C$4)*(Data!$B$2:$B$20=$D$4),ROW($A$2:$A$20)-ROW($A$2)+1),ROWS($C$5:$C5))),""))

The part in red looks at the range Data!$A$2:$A$20 and sees which ones match what's in $C$4 (27-Feb-17) of the current sheet. Using your example above, it returns this array:

{FALSE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

=IF(C4="","",IFERROR(INDEX(Data!$C$2:$C$20,SMALL(IF((Data!$A$2:$A$20=$C$4)*(Data!$B$2:$B$20=$D$4),ROW($A$2:$A$20)-ROW($A$2)+1),ROWS($C$5:$C5))),""))

The part in red looks at the range Data!$B$2:$B$20 and sees which ones match what's in $D$4 (Apps) of the current sheet. Using your example above, it returns this array:

{FALSE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

=IF(C4="","",IFERROR(INDEX(Data!$C$2:$C$20,SMALL(IF((Data!$A$2:$A$20=$C$4)*(Data!$B$2:$B$20=$D$4),ROW($A$2:$A$20)-ROW($A$2)+1),ROWS($C$5:$C5))),""))

The times function works like an AND for arrays, and basically checks to see if both the arrays are TRUE in the same positions, giving

{FALSE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

=IF(C4="","",IFERROR(INDEX(Data!$C$2:$C$20,SMALL(IF((Data!$A$2:$A$20=$C$4)*(Data!$B$2:$B$20=$D$4),ROW($A$2:$A$20)-ROW($A$2)+1),ROWS($C$5:$C5))),""))

The part in red looks at the row number of A2:A20, subtracts the starting row number and adds 1. This gives a list of offsets into the C2:C20 range

{1,2,3,4,5,6,7,8,9}

Now the IF uses the two arrays we've come up with like this:

IF({FALSE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE},{1,2,3,4,5,6,7,8,9})

Since we didn't define a value if the argument if false, it just defaults to FALSE, so the IF resolves the arrays to:

{FALSE,2,3,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

so you now have a list of the matching entries, and a bunch of FALSEs for the rest of the rows.

Now look at the ROWS function. ROWS returns the number of rows in the range. In C5 it is ROWS($C$5:$C5), which is 1 row. But as you drag it down the second row reference is not locked, so it becomes ROWS($C$5:$C6), which has 2 rows in it. So you can see that as you drag the whole formula down, the counters go: 1,2,3, etc.

The SMALL function takes the latest array, and finds the nth smallest entry, ignoring non-numeric entries. n comes from the ROWS function. So in C5, the counter is 1, so it finds the smallest entry in that array, which is 2.

Whew! Finally done with the arrays! Now the INDEX looks at Data!$C$2:$C$20 and gets the second element from it which is Item 2. If you ask SMALL for the 3rd smallest number and there are only 2, it will generate an error. So the IFERROR checks for an error and displays "" if it finds one. And finally, the IF at the beginning checks to see if the row above is empty or not. If it is, like the first time IFERROR is triggered, then we know that there are no more matches. In that case, we can put in a "" immediately and skip the time-consuming array processing, so your sheet calculates faster.

The main thing to keep in mind as you change the references to match your sheet is that I used 20 as the bottom row. But if your data goes down to 1000 rows, change the 20s to 1000s.

Hope that helps!

 

[PaulFerris]

Eric thanks so much - that was an incredible explanation AND I HAVE IT WORKING!!!!!

People like you restore my faith in humanity....

Again thank you so very much

 

[Eric W]

Sweet!! Nice way to go into the weekend! Have a great one!

Happy to help!

 

[PaulFerris]

OK looks like I spoke too soon.... As I have been going through the' Roadmap' sheet adding the formula it has been working perfectly up until row 45

So the formula I have H45 is (C45:AA54 is the swimlane where "Business Mobility" projects will be displayed)

{=IF(H$4="","",IFERROR(INDEX(Data!$C$2:$C$500,SMALL(IF((Data!$A$2:$A$500=H$4)*(Data!$B$2:$B$500=$A$45),ROW($A$2:$A$20)-ROW($A$2)+1),ROWS(H$45:H45))),""))}

$A$45 is where the value to compare against is located (in this case the word "Business Mobility" is there.
H$4 is still the launch week date (in this case it equals 27-Mar-17)

Now in the 'Data' sheet there is

[TABLE="width: 781"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]27-Mar-17[/TD]
[TD]Business Mobility[/TD]
[TD]Q4 Small Business MHH Offers- Phase 1[/TD]
[/TR]
[TR]
[TD]27-Mar-17[/TD]
[TD]Business Mobility[/TD]
[TD]Q4 Small Business MBB & Tablet Offers[/TD]
[/TR]
[TR]
[TD]8-May-17[/TD]
[TD]Business Mobility[/TD]
[TD]Q4 Small Business Mobility Hero Offer[/TD]
[/TR]
[TR]
[TD]8-May-17[/TD]
[TD]Business Mobility[/TD]
[TD]Q4 Small Business MHH Offers - Phase 2[/TD]
[/TR]
</tbody>[/TABLE]

So it should be finding a value and inserting it, but nothing is being populated.

Up until this point the whole thing has been working perfectly.

Any ideas?

 

[PaulFerris]

Just a follow up as I try to decode what is going wrong.... nothing under row 45 is working.

The odd thing is if I copy the value of A35 (which is Bundle) and place that at A45 that works. I see an exact copy of C35:AA44

I have checked the 'Data' sheet and the word "Business Mobility" is spelt the same on both sheets.

For the life of me I dont understand what is going on here

 

[PaulFerris]

Its official - I am an idiot

Got it to work.... I forgot to alter the bit hightlight in red

=IF(C$4="","",IFERROR(INDEX(Data!$C$2:$C$500,SMALL(IF((Data!$A$2:$A$500=C$4)*(Data!$B$2:$B$500=$A$5),ROW($A$2:$A$500)-ROW($A$2)+1),ROWS(C$5:C5))),""))

So I changed this $A$2:$A$500 to fixed my issue

IF I have made any other glaring mistakes let me know

Again thanks for the help Eric