Consolidation to track data over time

[paulrockliffe]

I have monthly reports for projects that tell me how much has been spent on a project to the end of the month. I have 12 tables, one for each month with a list of projects and an amount. Each month as projects start and finish the list of projects will change and they may not be in the same order. I want to produce a summary sheet that automatically gives me a list of all projects contained within the 12 monthly tables, with a column for each month against each project and the spending at the end of each month shown, so I can examine the profile of spending on projects and a look at a few other things.

What's the best way to build this table?

Thanks!

 

[estevaoba]

Hi!

It would help if you could make a mock-up workbook with a tab for one month, with fictitious data, and another tab populated manually with the data you expect for a summary sheet.

 

[paulrockliffe]

I can't post attachments unfortunately, but I'll try to show you below.

The summary table looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Cost[/TD]
[TD]To[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project Number[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]40[/TD]
[TD]55[/TD]
[TD]60[/TD]
[TD]65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD]35[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The Monthly tables look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[/TR]
[TR]
[TD]Project Number[/TD]
[TD]Cost to Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

Each monthly table would only include rows for projects with a Cost to Date in that month and not necessarily sorted in numerical order.

Thanks!

 

[estevaoba]

Assuming your project numbers and cost values are all in columns A and B respectively in each month tab, and that cost data in month columns in your summary tab start from cell B3, try this formula in that cell and drag down and across.

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IFERROR(INDEX(INDIRECT(B$2&"!B$3:B$100"),MATCH($A3,INDIRECT(B$2&"!A$3:A$100"),0)),"")[/TD]
[/TR]
</tbody>[/TABLE]

Make sure your month tabs are all named exactly as the month columns in summary tab.

Godspeed!

 

[paulrockliffe]

Thanks that's really neat and very helpful.

I'm just picking this up now and working through what you've come up with and making it fit exactly my spreadsheet. I've got it all working by the looks of things, but I need to consolidate all of the Project Numbers that are in the monthly tables into the summary table. There could be any number of project numbers in each month and obviously appending the lists would introduce lots of duplicates, so I need some sort of formula which will merge all 12 monthly columns together and remove duplicates.

I also simplified my example above, as well as a project number there's a 'Owner' that I need to add in another column, that information is also in the monthly tables and matched to the Project Number.

I feel like I'm straying towards needing to write a VBA script to build the list, but hoping there's a formula-based option available?

Thanks again!

 

[paulrockliffe]

I managed to butcher a formula found elsewhere to work with 12 columns, it's an array formula so I was expecting it to refuse to run over any reasonable number of data points - There are 1,500 projects in each months data and probably 3,000 unique projects over the course of the year - and 1,500^12 is a big number of calculations! It didn't run of course, spun for about 30 minutes before crashing Excel. Oh well. If anyone has anything better than:

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX(Apr!$A$2:$A$999999, MATCH(0, COUNTIF($B$3:B4, Apr!$A$2:$A$999999)+(Apr!$A$2:$A$999999=""), 0)), INDEX(May!$A$2:$A$999999, MATCH(0, COUNTIF($B$3:B4, May!$A$2:$A$999999)+(May!$A$2:$A$999999=""), 0))), INDEX(Jun!$A$2:$A$999999, MATCH(0, COUNTIF($B$3:B4, Jun!$A$2:$A$999999)+(Jun!$A$2:$A$999999=""), 0))), INDEX(Jul!$A$2:$A$999999, MATCH(0, COUNTIF($B$3:B4, Jul!$A$2:$A$999999)+(Jul!$A$2:$A$999999=""), 0))), INDEX(Aug!$A$2:$A$999999, MATCH(0, COUNTIF($B$3:B4, Aug!$A$2:$A$999999)+(Aug!$A$2:$A$999999=""), 0))), INDEX(Sep!$A$2:$A$999999, MATCH(0, COUNTIF($B$3:B4, Sep!$A$2:$A$999999)+(Sep!$A$2:$A$999999=""), 0))),INDEX(Oct!$A$2:$A$999999, MATCH(0, COUNTIF($B$3:B4, Oct!$A$2:$A$999999)+(Oct!$A$2:$A$999999=""), 0))), INDEX(Nov!$A$2:$A$999999, MATCH(0, COUNTIF($B$3:B4, Nov!$A$2:$A$999999)+(Nov!$A$2:$A$999999=""), 0))),INDEX(Dec!$A$2:$A$999999, MATCH(0, COUNTIF($B$3:B4, Dec!$A$2:$A$999999)+(Dec!$A$2:$A$999999=""), 0))),INDEX(Jan!$A$2:$A$999999, MATCH(0, COUNTIF($B$3:B4, Jan!$A$2:$A$999999)+(Jan!$A$2:$A$999999=""), 0))),INDEX(Feb!$A$2:$A$999999, MATCH(0, COUNTIF($B$3:B4, Feb!$A$2:$A$999999)+(Feb!$A$2:$A$999999=""), 0))),INDEX(Mar!$A$2:$A$999999, MATCH(0, COUNTIF($B$3:B4, Mar!$A$2:$A$999999)+(Mar!$A$2:$A$999999=""), 0))),"")

I'm all ears!

 

[estevaoba]

Hi.
Unfortunately, I am not familiar with VBA.
Maybe someone else will try and help you on that.

As for your array formula, assuming it is pasted at B4, the correct syntax to merge columns without duplicates would be:

=INDEX(Apr!$A$2:$A$999999,MATCH(1,(COUNTIF($B$3:B3,Apr!$A$2:$A$999999)=0)*(Apr!$A$2:$A$999999<>""),0))

Try copying it down just a few rows and see what happens.

Godspeed!

 

[estevaoba]

Check this out. I hope it helps.

https://www.excelforum.com/excel-fo...om-multiple-worksheets-to-a-master-sheet.html