1

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|
5
  • 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? Feb 28, 2012 at 11:08
  • 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, 2012 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, 2012 at 15:19
  • I'm afraid so. This is the structure of the data I'm working with. :(
    – jnthnclrk
    Feb 28, 2012 at 15:22
  • I don't think you can handle this without a UDF or some VBA (I've tried some formulas but with no luck)
    – JMax
    Feb 28, 2012 at 16:17

2 Answers 2

3

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.

2
  • 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, 2012 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, 2012 at 13:49
1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .