Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need to join 2 tables into 1 combined table in Excel that summarises the data in 1 & 2. Table 3 is noted below as it describes the relationship between 1 and 2.

I have also described a combined table to describe what I would like final data set to look like.

Which Excel functions should be used to join these datasets together?

Table 1

|Campaign|Clicks|Spend|
|Campaign 1|1,000|$50.00|
|Campaign 2|50|$0.90|

Table 2

|Group|Revenue|
|Group 1|$75.00|
|Group 2|$2.50|
|Group 3|$1.00|

Table 3

|Group|Campaign|
|Group 1|Campaign 1|
|Group 2|Campaign 2|
|Group 3|Campaign 1|

Combined Table

|Campaign|Clicks|Spend|Revenue|
|Campaign 1|1,000|$50.00|$76.00|
|Campaign 2|50|$0.90|$2.50|
share|improve this question
1  
Your question is a little bit unclear. What is the relationship in Table 3? If it's just alternating you could copy and fill down, but I assume it's more complicated than that? –  Raystafarian Feb 28 '12 at 11:08
    
I agree with Raystafarian. Plus, how do you want to concatenate the tables? With formulas? VBA? One shot? Every day? And furthermore, What have you tried? –  JMax Feb 28 '12 at 14:41
    
Sorry to be so vague. I've added some more detail and an example of how the combined table should look. I've tried to use Pivot Tables, but that doesn't seem suitable. I would like a pure Excel solution without any VBA scripting. –  jnthnclrk Feb 28 '12 at 15:04
    
Is there a reason Table 2 and Table 3 are separate? This would be pretty straightforward if you combined those two tables first. –  Excellll Feb 28 '12 at 15:19
    
I'm afraid so. This is the structure of the data I'm working with. :( –  jnthnclrk Feb 28 '12 at 15:22

2 Answers 2

up vote 3 down vote accepted

This problem pledges for the usage of some kind of database. Especially, if you may run into more complex data constallations in the future. Consider managing your data in Access and export / import to Excel, which should be supported quite well.

In SQL, your query would look something like this:

select campaign, clicks, spend, revenue from (
select campaign, sum(revenue) from 
    table2 inner join table3 on table2.group = table3.group group by campaign) as table23 
 inner join table1 on table1.campaign = table23.campaign

You can probably use the graphical MS Access editor, if you prefer that.

share|improve this answer
    
I had a feeling this was the case. Although I find it strange that Excel, a mostly brilliant program, would fall short in this regard. Access is not an option for me as I'm on a Mac. Don't much like the look of Filemaker either. Guess it's time to learn MySQL. –  jnthnclrk Feb 29 '12 at 13:32
    
Maybe SQLite is supported too. SQLite is a filebased SQL-database, doesn't need a server. Maybe this can help: 321492 I'd say it's just out of the scope of Excel. –  Markus Mar 1 '12 at 13:49
    
Thanks for the tip! SQLite looks much easier. –  jnthnclrk Mar 2 '12 at 13:05

Based on your example data, I'm assuming each group will only ever belong to one campaign. If that is the case, then this can be done quite easily in Excel with the VLOOKUP and SUMMIF formulas and some named ranges.

Picture of tables in Excel

  1. Select all of Table 3 and name it.
  2. Add a Campaign column to Table 2. and use the VLOOKUP formula to find the Campaign. If the tables are setup as in my picture, then the formula in cell H3 would be: =VLOOKUP(F3,Table3,2,FALSE).
  3. Select the Table 2 Revenue column and name it.
  4. Select the new Table 2 Campaign column and name it.
  5. Add a Revenue column to Table 1. Use the SUMIF formula to total the revenue in Table 2. In my example, the formula in cell D3 is: =SUMIF(Table2_Campaign,A3,Table2_Revenue).

Since the only difference between your Combined Table and Table 1 was the Revenue column, I just added it to Table 1.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.