VBA to map pivot data to another sheet

Wafee

Board Regular
Joined
May 27, 2020
Messages
104
Office Version
  1. 2013
Platform
  1. Windows
I have beow pivot table as my source. here i have selected repeat item labels option. Column A has senior Manager and Column "B" has managers under the senior manager.

Copy of Overall Shankar_Cox_UnapprovedTime Report.xlsm
ABAP
1Sum of DURATION
2Senior ManagerManagerGrand Total
3DINGARI, HRUSHIKESH K2103.72
4DINGARI, HRUSHIKESH KNAIR, GIRISH1143.72
5DINGARI, HRUSHIKESH KSARKAR, ASHIS720
6DINGARI, HRUSHIKESH K(blank)240
7FAHEEM, WAEL5675.5
8FAHEEM, WAELAGARWAL, DINESH1300.5
9FAHEEM, WAELCHANDRASEKHARAN, RAJEEV3686.5
10FAHEEM, WAELCHANG, SOK WU W90
11FAHEEM, WAELWHITE, CHRIS L500.5
12FAHEEM, WAEL(blank)98
13GERMANOTTA, DOMINIC3632.25
14GERMANOTTA, DOMINICBONOMO, MICHAEL1092.75
15GERMANOTTA, DOMINICHALL, TAMARA L160
16GERMANOTTA, DOMINICWafee40
17GERMANOTTA, DOMINICRANGARAJAN, JAGANNATH K1751
18GERMANOTTA, DOMINICSTRATFORD, CHRIS547.5
19GERMANOTTA, DOMINICSTRATTON, LAURA M41
20GURUMURTHY, VIVEK18041.8
21GURUMURTHY, VIVEKBASU, SANJAY458.83
22GURUMURTHY, VIVEKBELIHOMJI, ADIL9802.5
23GURUMURTHY, VIVEKJAIN, ARCHANA6365.97
24GURUMURTHY, VIVEKNIRANJANI, HIMANSHU1324
25GURUMURTHY, VIVEKSUDHARSAN, VELAMUR S90.5
26MADERA, ISAAC A4863.99
27MADERA, ISAAC ABAESSLER, CHRISTINA2719.49
28MADERA, ISAAC ACASO, ANDREW T76
29MADERA, ISAAC AGREGORY, LISA M709
30MADERA, ISAAC AJOBERT, SEBASTIEN635
31MADERA, ISAAC AKERCHER, TODD E40
32MADERA, ISAAC APAONE, KELLY M246
33MADERA, ISAAC AREPER, LAURA L358.5
34MADERA, ISAAC AVENKATRAMAN, SRINIVAS80
35RAMANATHAN, SANKARAN1249
36RAMANATHAN, SANKARANDAMIANO, NATALIE M291.5
37RAMANATHAN, SANKARANJOSHI, ANIRUDHA A339
38RAMANATHAN, SANKARANRAY, ANDREW L498.5
39RAMANATHAN, SANKARAN(blank)120
40SINGH, SUMIT2410.25
41SINGH, SUMITFUJINAMI, GARY G223
42SINGH, SUMITKRIER, RYAN210.5
43SINGH, SUMITPAFF, MARK D80
44SINGH, SUMITSCHANEL, BARBARA J524
45SINGH, SUMITSHARMA, RAJU70
46SINGH, SUMITTANUKU, NAGESWARA RAO900
47SINGH, SUMIT(blank)402.75
48SIVAGANESH, SIVAGNANALINGAM423
49SIVAGANESH, SIVAGNANALINGAMKAUL, ASHUMA S120
50SIVAGANESH, SIVAGNANALINGAMMYLAVARUPU, VENKAT S183
51SIVAGANESH, SIVAGNANALINGAMTHAKER, DHAVAL V80
52SIVAGANESH, SIVAGNANALINGAM(blank)40
53SLY, DAVID G1652.5
54SLY, DAVID GBARNAK, ROBERT W124
55SLY, DAVID GBILLINGER-JONES, ANTOINETTE M321
56SLY, DAVID GFONTANA, OTTAVIO S327
57SLY, DAVID GHAVEWALA, ASPI191.5
58SLY, DAVID GMATEO, ALFRED A523
59SLY, DAVID G(blank)166
60ZIAEE, KAMRAN5787.5
61ZIAEE, KAMRANCHAKRAVARTY, SHOMA743
62ZIAEE, KAMRANKUMAR, NANDA3307
63ZIAEE, KAMRANPASTOR, RICHARD51
64ZIAEE, KAMRANSEKAR, KALYANI392
65ZIAEE, KAMRAN(blank)1294.5
66Sundar40
67SundarMax40
68(blank)4866.88
69(blank)(blank)4866.88
70Grand Total50746.39
Pivot


Below is the output sheet where I need the update the grand total every week in to a new column.

Copy of Overall Shankar_Cox_UnapprovedTime Report.xlsm
ABJKLM
1
2YTD to OCT 10YTD to OCT 17YTD to OCT 24YTD to OCT 31
3Senior Manager/Manager
4GERMANOTTA, DOMINIC7747.333204.53592.25
5BONOMO, MICHAEL1697829.51092.75
6HALL, TAMARA L1306.33295.5160
7PRADHAN, JEETENDRA 1243.5279NA
8RANGARAJAN, JAGANNATH K2760.51675.51751
9STRATFORD, CHRIS74085547.5
10STRATTON, LAURA MNA4041
11((blank)NANANA
12DINGARI, HRUSHIKESH K695534942103.72
13NAIR, GIRISH2141.52829.51143.72
14SARKAR, ASHIS1842584.5720
15VENKATRAMAN, SRINIVAS 1654NA
16HUGHES, GEORGE L 246NANA
17THAKER, DHAVAL V942.5
1812980240
19FAHEEM, WAEL6901.435039.55675.5
20AGARWAL, DINESH28031670.51300.5
21CHANDRASEKHARAN, RAJEEV2876.3427403686.5
22CHANG, SOK WU W404090
23ZAVAR, MASTANEH 228NANA
24WHITE, CHRIS L902.09589500.5
25(blank)52NA98
26GURUMURTHY, VIVEK24709.612029518041.8
27BASU, SANJAY838.25780.58458.83
28BELIHOMJI, ADIL12993.4412266.59802.5
29JAIN, ARCHANA8242.425193.176365.97
30SUDHARSAN, VELAMUR S428.5450.590.5
31NIRANJANI, HIMANSHU22071604.251324
32(blank)NANANA
33MADERA, ISAAC A5674.683484.944863.99
34BAESSLER, CHRISTINA2199.681883.942719.49
35CASO, ANDREW T3404076
36GREGORY, LISA M290734709
37JOBERT, SEBASTIEN1363170635
38REPER, LAURA L71664358.5
39PAONE, KELLY M216421246
40KERCHER, TODD E2944040
41VENKATRAMAN, SRINIVASNANA80
42(blank)256132NA
43MUSE, ERIC V12000
44BOWEN, TROY L 120NANA
45PETTIT, STEPHENNANANA
46SANKARANARAYANAN, RAMESHNANANA
47TOTH, RICHARD A NANANA
48(blank)NANANA
49RAMANATHAN, SANKARAN3078.671902.161249
50DAMIANO, NATALIE M583.17NA291.5
51RAY, ANDREW L
52MYLAVARUPU, VENKAT S1562
53RAY, ANDREW L813.5602.66498.5
54KUSHWAHA, ABHITABHNANANA
55JOSHI, ANIRUDHA A1139.5339
56(blank)120160120
57SINGH, SUMIT5155.41762.332410.25
58FUJINAMI, GARY G552174223
59KRIER, RYAN394.4120210.5
60PAFF, MARK D966.512080
61SCHANEL, BARBARA J56132524
62TANUKU, NAGESWARA RAO1266.5821900
63KIRKWOOD, SARA M 397NANA
64SHARMA, RAJUNANA70
65(blank)1018495.33402.75
66SLY, DAVID G5230.751414.731652.5
67BARNAK, ROBERT W83644124
68BILLINGER-JONES, ANTOINETTE M1054.540321
69FONTANA, OTTAVIO S431787327
70HAVEWALA, ASPI1001.5120191.5
71MATEO, ALFRED A1711.25383.73523
72(blank)196.540166
73ZIAEE, KAMRAN6,160.425,639.835,787.50
74CHAKRAVARTY, SHOMA422340743
75KUMAR, NANDA29053259.53307
76MONDRY, MARC WNANANA
77PASTOR, RICHARD1258.42252.8351
78SEKAR, KALYANI618.5672.5392
79(blank)956.511151294.5
80SIVAGANESH, SIVAGNANALINGAM610365423
81JOSHI, ANIRUDHA A445
82KAUL, ASHUMA S125205120
83THAKER, DHAVAL V
84MYLAVARUPU, VENKAT S
85SHARMA, RAJU40NA
86THAKER, DHAVAL V12080
87MYLAVARUPU, VENKAT S40183
88(blank)NANA40
89Grand Total72343.2946601.9945799.51
View 1 Week on Week Trend - Un


steps.

1.need to compare output(Sheet2) sheet and pivot sheet(sheet1) and check whether any new senior manager is added to the list and if yes then that needs to be added in to the column "A" of sheet to above Grand total. if any senior manager is removed nothing to be done. it should also ignore (blanks) in senior manager column.

2. Need to copare managers under senior managers and if there is an addition or movement (Managers might move from one senior managers to other) then that manager name to be added under respective senior manager. In case of movement we alos need to retain existing line as it might contain data for past week. it should not ignore (blanks) under manager.

3. once this is sorted grandtotal from pivot needs to be pulled and published in to the column immediately after the last column in row 4 that contains data. in this case it is column "M". I have repeated item labels in pivot so that it might be easy for me to pull the hours using combination of senior managers and managers and also combination of senior managers and (blanks).

I also think repeating senior Managers in Column A of sheet2 as in pivot might help me to figure additions or movements in terms of SM and managers by comparing combination of column A and Column B of both sheets.


In this scenario manager "Wafee" needs to be added under "GERMANOTTA, DOMINIC". new line for senior manager "Sundar" needs to be added and under him "Max" needs to added as his manager.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,620
Latest member
dsubash

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
Back
Top