Merging data sources help needed

[ZeDoctor]

Hi Guys,

I've attached an example workbook to help explain what I need.

I've basically got two set of data

1/ the left data source will be a long list of transactions per company, so there will be multiple rows of data per company.
2/ the right data source shows the budget per company, so there will only be one row per company.

Ultimately I need a master list of data with one line per company, showing total rev, gp and budget rev and GP.

I managed to set up a formula that will lookup and enter the budget only once per company, which allows me to pivot my data successfully.

Heres where my problem lies - there will be companies in the right hand data source (Company A and Company B) that don't appear in the left hand data source and I want these companies to appear in the new list/pivot (with 0 rev & GP)

I hope that made sense, i've tried to explain it the best I could.

Any help is much appreciated.

Thanks,

[Bernie Deitrick]

Try using SUMIFS formulas to return rev, gp and budget rev and GP to the 'right data source'

[ZeDoctor]

Thanks for the reply Bernie,

I should have added that there will be companies in the 'left data source' that won't appear in the right. I need these included as well.

Sorry to be a pain, I just really can't get my head around this.

[Bernie Deitrick]

Make a list of the unique names in a new, third table - advanced filtering will allow you to do that - and use formulas to extract the information from the other two table.