Multi Row Formula

[ShantanuDagar]

Hello,

I had a data set for which I needed to create a 2 stage pivot table. The following is at where I am stuck right now:

I have a dummy data as follows:

Name                      Area                  Sum

A                             ABCD                  10

A                             AHBS                   20
A                             ASDS                   30

B                             ASKO                  40

C                             NJKS                  50

C                            ASPD                   23

Now, I want to remodel it like a multi level pivot table is in excel.

In the data above there are 3As, so 1st A should be visible, rest 2 cells must become empty. B will remain as it is. C also only the first one must remain, other one must become empty. (Could use multi row formula tool ig, but not able to do it correctly)

Also, I need 1 sum totals for group wise names. Like:

Other data remains as it is.

1 row additional for each name type. Sum will have total of all As. Area would remain empty.

[AndrewDMerrill · Accepted solution]

Here is a simple sample workflow that either does what you need or should be a good starting point for you:

[rzdodson]

@ShantanuDagar one approach using an iterative macro.

[ShantanuDagar]

This is good. Thx.