Pivot Database with 3 Dimensions

brodskive

New Member
Joined
Jul 1, 2010
Messages
2
I have been trying to setup a database for activity at a workplace. The database is suppose to have columns for activity (ID-number), people involved in it and equipment used. The problem is that the number of people involved in an activity can vary along with the equipment used. Basically this means a third dimension (a dynamic one) to the excel-table, or a list in a cell if you like..
E.g.:
|Activity|____|______User________|Equipment|
|_#0001_|___|______user1________|_a, b, c, d_|
|_#0002_|___|_user1, user2, user3_|____b, f___|

I want to be able to track statistics for each individual user and each type of equipment independently of groups using pivot tables. Is it possible to make a database like this in excel without too much hassle?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

sr12345

Active Member
Joined
Feb 20, 2009
Messages
317
By placing the user(s) before the equipment, you are implying a hierarchical relationship between them that does not actually exist.

Instead, in your data table, can you add a column and use a CONCATENATE function to combine your user entry and equipment entry into one cell, and then use that concatenated column as the second value in your pivot table?
 

brodskive

New Member
Joined
Jul 1, 2010
Messages
2
Thanks for your reply! I'm not sure if I follow what you say about hierarchy.. equipment is independent of the user(s), it is only connected to the certain activity. In other words equipment and users are independent variables that are connected to one activity. Can cells created by the CONCATENATE function be recognized as multiple items in one cell by pivot tables?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,979
Messages
5,767,432
Members
425,412
Latest member
andrealp4444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top