Pivot Table, Max

[poikl]

Hi, I'd like help in adding a column to a Pivot Table where in ColA it lists 3000 Products and then in ColB thru ColBT it lists Truck Id# (example Truck#1, Truck #2 etc.) so in the added ColBU i'd like it to show the Truck# which was used most to carry that particular product.
So for example if Product Id# 123 was carried once in Truck #1 (ColB) and 3 times in Truck#3 (ColD) then in ColBU it would show ColD.
Can you please help me with this?

 

[gelu]

Hi P,

Here is a solution

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Product	Attribute	Value
3	LOW000003	truck1	45		E	F	G	H	I	J	K	L
4	LOW000003	t2	16
5	LOW000003	t3	71		Sum of Value	Column Labels
6	LOW000003	t4	50		Row Labels	t2	t3	t4	t5	t6	t7	truck1
7	LOW000003	t5	87		LOW000001	26.2	92.4	4.8	4.5	40.7	92.0	82.7	G
8	LOW000003	t6	52		LOW000003	97.9	64.2	56.0	32.0	57.3	73.5	72.0	F
9	LOW000003	t7	23		POF100529	54.6	67.2	80.3	79.8	1.8	96.3	62.4	K
10	POF100549	truck1	74		POF100549	38.7	3.9	19.6	88.8	77.9	83.6	85.0	I
11	POF100549	t2	95		RSW102021	68.1	32.5	12.4	94.9	33.5	44.4	27.9	I
12	POF100549	t3	43		RSW104021	93.0	52.8	2.6	45.2	41.9	57.5	29.1	F
13	POF100549	t4	60		RUG121211	78.1	84.3	64.1	36.3	99.6	24.9	66.7	J
14	POF100549	t5	85		RUG126151	89.3	64.6	78.8	55.2	55.4	74.6	39.9	F
15	POF100549	t6	33		RUG126575	80.0	3.6	25.8	58.7	43.2	52.1	70.9	F
16	POF100549	t7	53		RUG128117	47.5	19.6	42.3	64.9	95.0	79.3	46.9	J
17	RSW104021	truck1	44		RUG128188	0.2	33.2	87.1	35.5	98.1	40.9	46.3	J
18	RSW104021	t2	78		RUG128337	81.0	63.9	11.3	10.0	19.3	97.6	43.7	K
19	RSW104021	t3	58		RUG128722	67.2	55.0	48.1	11.0	96.1	11.7	9.6	J
20	RSW104021	t4	61		RUG128815	78.8	16.0	37.3	33.2	0.1	83.9	53.5	K
21	RSW104021	t5	97		RUG129088	92.5	19.6	31.3	89.0	21.2	10.8	53.7	F
22	RSW104021	t6	35		RUG143211	83.3	56.6	28.1	17.2	10.0	64.8	6.6	F
23	RSW104021	t7	33		RUG146153	79.8	69.1	7.8	37.3	63.3	15.1	71.1	F
24	RUG128337	truck1	52		RUG146575	35.8	28.0	17.5	65.3	35.6	79.1	53.3	K
25	RUG128337	t2	92		RUG148388	50.8	38.5	13.0	61.8	22.2	57.0	36.4	I
26	RUG128337	t3	37		RUG148724	37.4	27.2	94.5	21.7	3.7	35.1	73.4	H
27	RUG128337	t4	33		RUG148835	28.2	62.3	37.1	28.2	96.2	5.0	22.0	J
28	RUG128337	t5	2		RUG149088	15.2	98.5	3.4	22.4	0.2	4.3	78.7	G
29	RUG128337	t6	73		RUG150257	63.6	74.4	62.7	171.8	121.4	81.2	136.2	I
30	RUG128337	t7	69		RUG151221	41.5	0.6	73.0	31.5	44.8	41.0	70.8	H
31	RUG143211	truck1	60		RUG152706	87.9	32.4	68.0	32.4	9.0	88.2	22.9	K
32	RUG143211	t2	15		RUG152710	71.4	67.8	60.7	79.3	66.1	49.9	36.0	I
33	RUG143211	t3	20		RUG152711	73.1	3.4	20.8	77.1	45.5	67.0	98.1	L
34	RUG143211	t4	76		RUG152717	19.6	3.0	67.9	39.6	29.2	53.3	31.5	H
35	RUG143211	t5	84		RUG152892	82.9	36.7	72.5	66.7	38.0	28.4	62.4	F
36	RUG143211	t6	61		RUG152894	80.2	38.7	39.8	68.5	9.9	60.6	0.4	F
37	RUG143211	t7	32		RUG153423	26.2	76.5	14.5	51.2	28.6	52.3	26.2	G
38	RUG146153	truck1	66		RUG154706	52.2	21.6	71.8	84.8	82.3	95.0	95.0	L
39	RUG146153	t2	12		RUG154730	59.5	26.7	79.7	85.2	5.8	87.4	78.7	K
40	RUG146153	t3	65		RUG154731	88.2	18.1	20.7	62.3	48.1	78.4	70.0	F
41	RUG146153	t4	34		Grand Total	2,070	1,453	1,455	1,843	1,541	1,966	1,860
42	RUG146153	t5	20
43	RUG146153	t6	54
44	RUG146153	t7	61
45	RUG146575	truck1	31
46	RUG146575	t2	4
Sheet2

Cell Formulas
Range	Formula
E3:L3	E3	=CHAR(COLUMN()+64)
M7:M40	M7	=INDEX($E$3:$L$3,MATCH(MAX(E7:L7),E7:L7,0))

Regards,

G

 

[poikl]

G, I really appreciate your help.