Excel 2013: Automated Way to Create Data Table for Pivot?

[beadedbytes]

I'm wondering if there's a somewhat automated way to generate a code listing that's based on common elements.

I'm working with a data tool that allows one to pull customer data by selecting desired metrics. In addition to the CODE, a sampling of the metrics are listed below -- STATE, VERSION, TYPE, SRC1, SRC2 and SRC3. Here's are the issues though.

ISSUE #1
The original CODE denotes the different combinations of the STATE, VERSION and TYPE metrics. A CODE is assigned to each unique combination of these 3 metrics. I also add the SRC1, SRC2, SRC3 metrics to each record before downloading the data for my Excel pivot table. By doing this, the original CODE is no longer unique and the number of rows in the Data Set 'increases'. After downloading, I create a revised 'unique' code for each record in the Data Set by concatenating the STATE, VERSION, TYPE, SRC1, SRC2, SRC3 metrics.

ISSUE #2
The Data Set only includes records wherein the customer has responded. Since this info can vary as responses are received, I want to build - in advance - a table for my pivot that includes all possible combinations of the metrics in the Data Set below. I would then use this all-inclusive table for my Excel Pivot updates instead of creating and attaching revised coding to the actual Data Set. Is there a way to do this inside Excel? I hope so. Please let me know. Thank you in advance.

[u]# of Variables Per Field (Multi = infinite possibilities)[/u]
Multi   	50	Multi	Multi	10	2	3
						
[u]DATA SET[/u]						
CODE    	STATE	VERSION	TYPE	SRC1	SRC2	SRC3
0315-001	AZ	ABCD01	XYZ	0	Y	-
0315-001	AZ	ABCD01	XYZ	0	Y	A
0315-001	AZ	ABCD01	XYZ	0	Y	B
0315-001	AZ	ABCD01	XYZ	0	Z	-
0315-001	AZ	ABCD01	XYZ	0	Z	A
0315-001	AZ	ABCD01	XYZ	0	Z	B
						
0315-002	AZ	ABCD02	XYZ	1	Y	-
0315-002	AZ	ABCD02	XYZ	1	Y	A
0315-002	AZ	ABCD02	XYZ	1	Y	B
0315-002	AZ	ABCD02	XYZ	1	Z	-
0315-002	AZ	ABCD02	XYZ	1	Z	A
0315-002	AZ	ABCD02	XYZ	1	Z	B

 

[SkipVought]

HI,

I'm having difficulty understanding a question in Issue 1.

If I'm understanding the question in Issue 2 correctly, it seems that each time you get a data set, you make a new Pivot Table and somehow you think that getting all the base row data elements, including those not in any particular data set for which there are no SRC aggregations to report, will help in some way?

Help me out, please!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[beadedbytes]

Thanks, Skip. I can always count on you for feedback.

I don't re-create the Pivot table. I download Data Set each week (cumulative responses, including any new info), assign revised coding to this Data Set and use as the updated table that feeds into the Pivot.

I will create a step-by-step visual that will hopefully make it clearer. Will send later today or tomorrow. Sorry for any confusion.

 

[beadedbytes]

Okay. Let's try this again.

Before the company sends data to their vendors who interact with the customers, they assign each record a code. See 1st column below. At this stage, the code is comprised of three metrics -- Customer's State, Product and corresponding Product#.

[u]CODE ORIG	ST	VERSION	SRC	SRC1	SRC2	SRC3[/u]
0315-001	AZ	ABCD01	XYZ	n/a	n/a	n/a
0315-002	AZ	ABCD02	XYZ	n/a	n/a	n/a

When the customer responds, the vendor sends data back to the company with response details. Each customer record contains the above unique code. When the data is loaded back to company database, the system appends additional data metrics, specifically SRC1, SRC2 and SRC3. By adding these metrics, the number of combinations from now six metrics has increased and the original code is no longer unique. This is the data view that would be available to me for download.

CODE ORIG	ST	VERSION	SRC	SRC1	SRC2	SRC3
0315-001	AZ	ABCD01	XYZ	0	Y	-
0315-001	AZ	ABCD01	XYZ	0	Y	A
0315-001	AZ	ABCD01	XYZ	0	Y	B
0315-001	AZ	ABCD01	XYZ	0	Z	-
0315-001	AZ	ABCD01	XYZ	0	Z	A
0315-001	AZ	ABCD01	XYZ	0	Z	B
0315-002	AZ	ABCD02	XYZ	1	Y	-
0315-002	AZ	ABCD02	XYZ	1	Y	A
0315-002	AZ	ABCD02	XYZ	1	Y	B
0315-002	AZ	ABCD02	XYZ	1	Z	-
0315-002	AZ	ABCD02	XYZ	1	Z	A
0315-002	AZ	ABCD02	XYZ	1	Z	B

Since I do analysis based on the six, not three, metrics, I re-assign a 'unique' code by concatenating all six metrics.

CODE ORIG	ST	VERSION	SRC	SRC1	SRC2	SRC3	CODE-4-PIVOT
							        [Concatenated 6 Metrics]
0315-001	AZ	ABCD01	XYZ	0	Y	-	0315-001_AZ_ABCD01_XYZ_0_Y_-
0315-001	AZ	ABCD01	XYZ	0	Y	A	0315-001_AZ_ABCD01_XYZ_0_Y_A
0315-001	AZ	ABCD01	XYZ	0	Y	B	0315-001_AZ_ABCD01_XYZ_0_Y_B
0315-001	AZ	ABCD01	XYZ	0	Z	-	0315-001_AZ_ABCD01_XYZ_0_Z_-
0315-001	AZ	ABCD01	XYZ	0	Z	A	0315-001_AZ_ABCD01_XYZ_0_Z_A
0315-001	AZ	ABCD01	XYZ	0	Z	B	0315-001_AZ_ABCD01_XYZ_0_Z_B
0315-002	AZ	ABCD02	XYZ	1	Y	-	0315-002_AZ_ABCD02_XYZ_1_Y_-
0315-002	AZ	ABCD02	XYZ	1	Y	A	0315-002_AZ_ABCD02_XYZ_1_Y_A
0315-002	AZ	ABCD02	XYZ	1	Y	B	0315-002_AZ_ABCD02_XYZ_1_Y_B
0315-002	AZ	ABCD02	XYZ	1	Z	-	0315-002_AZ_ABCD02_XYZ_1_Z_-
0315-002	AZ	ABCD02	XYZ	1	Z	A	0315-002_AZ_ABCD02_XYZ_1_Z_A
0315-002	AZ	ABCD02	XYZ	1	Z	B	0315-002_AZ_ABCD02_XYZ_1_Z_B

 

[beadedbytes]

I inadvertently hit the post button.

Once I've created the concatenated code, I update my Pivot with this updated, cumulative table. The Pivot table is driven off of the concatenated code.

Let me know if this makes sense and if you have additional questions.

 

[beadedbytes]

Additional info ...

The download data is customer response data. I pull down data weekly. The export is cumulative and contains new info each week. Instead of creating the revised unique coding each week, I prefer to create a master code table in the beginning of the project that contains all possible coding combinations based on the six metrics and update the table for pivot with response data only.

I don't want to be creating new table with updated coding each week since their is other info attached to the table that I would also need to update each week. Make sense?

 

[SkipVought]

I guess I don't understand what you need to pivot. What are you aggregating?

This is weekly data, yet I see no dates to differentiate one week's set vs another.

And I still don't get what you really need to do???

So this is the only data that you will use in your Pivot, whatever that means to you...

CODE-4-PIVOT
[Concatenated 6 Metrics]
0315-001_AZ_ABCD01_XYZ_0_Y_-
0315-001_AZ_ABCD01_XYZ_0_Y_A
0315-001_AZ_ABCD01_XYZ_0_Y_B
0315-001_AZ_ABCD01_XYZ_0_Z_-
0315-001_AZ_ABCD01_XYZ_0_Z_A
0315-001_AZ_ABCD01_XYZ_0_Z_B
0315-002_AZ_ABCD02_XYZ_1_Y_-
0315-002_AZ_ABCD02_XYZ_1_Y_A
0315-002_AZ_ABCD02_XYZ_1_Y_B
0315-002_AZ_ABCD02_XYZ_1_Z_-
0315-002_AZ_ABCD02_XYZ_1_Z_A
0315-002_AZ_ABCD02_XYZ_1_Z_B

This just does not make sense to me???

Are you really using the PivotTable Wizard and if so on what source data table? It must not be anything that you have displayed so far. I just don't get what you're doing???

At this point I really need to see your workbook. Can you upload?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]