Pivot tables: combining data from 2 columns into one field

**Dave32** · 04-15-2008, 10:30 AM

I am building a database with multiple records.

-Each record has a Unique ID.
-Each Record provides info about 3 marketing activities
-Each activity is funded by a the same of different fund pot of money.

So in one record I have several fields called fund pot1, fund pot2, fund pot3

Data Validation is A, B, C, D

I need to group the data into one field in a pivot table.

Dave32

**Portuga** · 04-15-2008, 01:23 PM

Not sure how exactly you want to group them, but you could create the grouped field in the raw data sheet and then add it to your pivot table as a field.

**Dave32** · 04-16-2008, 03:40 AM

Portuga,

Thanks for your reply. Please see attachment. I need to create a pivot table from the data highlighted in blue. The pivot table shown shows displays what I want to see (this is created from the data I manupulated in orange highlight). So both the cost and funding pot columns need to be considered as 2 fields for the pivot table.

Thanks,

Dave32

**Portuga** · 04-16-2008, 09:14 AM

You can use the database sheet to give you what you want.
I created an extra column callled "activity".

From this table (working as a mini database), I then created 2 pivot tables to display the data as you had it in the original sheet.

As you add more dfata to the database, once refreshed, the pivots will display the data automatically.
(use the refresh buton)

(See attached)

**Dave32** · 04-16-2008, 10:12 AM

Portuga,

Thanks for your help, but this does not solve the problem.

I cannot put the same campaign in different rows.

In my example there are four campaigns, Alpha Launch etc...

Each campaign has a Unique ID.

Each Campaign has two activities, Each activity may be funded from different fund pots.

I need to keep 1 row for each record (campaign).

Example Record

Unique ID = 123
Campaign Name= Alpha Launch
Product = XRAY
Country = ES
Activity1 = Posters
Cost of acivity 1 = £20
Fund Box = A
Activity 2 = Brochures
Cost of activity = £30
Fund Box = B

Unique ID = 124
Campaign Name= Beta Launch
Product = CELA
Country = FR
Activity1 = Event
Cost of acivity 1 = £2000
Fund Box = C
Activity 2 = Stickers
Cost of activity = £300
Fund Box = D

Etc....

The pivot table needs to look at how much is spend per fund box. (A, B, C, D)

Thanks,

Dave32

**royUK** · 04-16-2008, 10:58 AM

Portuga has demonstrated the best way to approach this which is also baiscally what I told you in a your earlier post.

If you don't do it this way then you will end up with your problem of running out of Columns.

**Dave32** · 04-16-2008, 11:21 AM

Roy,

So I am trying to do the imposible with one row of data.
Basically I need to store one record of data(campaign) over two rows.
Campaigns are unique, activities are not.

So:

Unique ID Campaign Name Activity Fund Box Cost Country

123 Beta Launch Posters A £20 FR
123 Beta Launch Stickers B £30 FR

I can see this will make a problem if I have to do a VLOOKUP as column A will not have unique data. I need to do a VLOOKUP as there is so much data for each record that I need to display it on one page in a readable format. Do think I would have success using MS Access? I there a limit on the amount of fields you have for each record in MS Access?

Dave32

**Portuga** · 04-16-2008, 11:25 AM

Thanks royUK for clearing up that issue.

Dave32:
You want to keep one row for each campaign, you dont need to.
For the pivot table solution this is the best approach.

If you want to get a formula solution to fill in that table, thats another story.
You can use sumproduct or sumif formulas to get the totals by pot.