Add Dynamic row number to Excel pivot table

[gadesans]

Hello experts,
Is it possible to add a Dynamic row label to Excel pivot table?


This is the original data[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]REP[/TD]
[TD]PROD[/TD]
[TD]QTY[/TD]
[TD]PRC/UNIT[/TD]
[TD]REV[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]NOTEBOOK[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]CALCULATOR[/TD]
[TD]4[/TD]
[TD]50[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]PEN[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]NOTEBOOK[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]PENCIL[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]NOTEBOOK[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]PENCIL[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]CALCULATOR[/TD]
[TD]5[/TD]
[TD]50[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]PEN[/TD]
[TD]13[/TD]
[TD]5[/TD]
[TD]65[/TD]
[/TR]
</tbody>[/TABLE]

Standard Pivot table will give formatting like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sum of Total Rev[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rep[/TD]
[TD]Product[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Calculator[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Notebook[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pen[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Notebook[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pencil[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Calculator[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Notebook[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pen[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pencil[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD]707[/TD]
[/TR]
</tbody>[/TABLE]

I would like to get a column inserted for Row Number
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Rep[/TD]
[TD]Row Num[/TD]
[TD]Product[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]Calculator[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]Notebook[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]Pen[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[TD]Notebook[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD]Pencil[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]6[/TD]
[TD]Calculator[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7[/TD]
[TD]Notebook[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8[/TD]
[TD]Pen[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9[/TD]
[TD]Pencil[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

And, I would like the Row number to be dynamic i.e. if data for A is not displayed, the numbers should increasing serially from 1, 2, 3

 

[jbiehl]

Did you find a solution to this?

 

[sandy666]

imho with standard pivot table it is not possible but you can add column (here: Index) to the source table

Book1
	B	C	D	E	F	G
3	Index	REP	PROD	QTY	PRC/UNIT	REV
4	1	A	NOTEBOOK	5	10	50
5	2	A	CALCULATOR	4	50	200
6	3	A	PEN	6	5	30
7	4	B	NOTEBOOK	7	10	70
8	5	B	PENCIL	8	2	16
9	6	C	NOTEBOOK	2	10	20
10	7	C	PENCIL	3	2	6
11	8	C	CALCULATOR	5	50	250
12	9	C	PEN	13	5	65
Sheet1

Cell Formulas
Range	Formula
B4:B12	B4	=ROW(A1)

and use this column in your pivot table

but it will not be updated automatically if you change/filter your pivot table