Editing A Manually Created Group In Pivot Table

For an insurance audit, we have to submit a list of payees' names and payments (basically the cash disbursements), and identify which of those payees is a subcontractor, because subcontractors have a different classification for insurance purposes whereby they have to have their own insurance or the policyholder gets charged for a premium for that subcontractor.

There is no field design in the spreadsheet to indicate who is a subcontractor, and we simply submit this list of names to the insured, asking them who is a subcontractor.

They will then edit the list, sending it back to us.

Herein lies the problem: for we will have to edit the group, adding and deleting as needed to perfect subcontractor group for the insurance audit.

I'm searching over the Internet, and not seeing any advice on how to edit a group once it's created, how to add or remove the names to/from the group.

In the attached spreadsheet is the group called Subcontractors, and I'd like to add names to it or maybe even remove a name or two based upon the reply that I get back from the insured.

Any advice on how to go about doing this?

Thanks in advance

Attachment 868744

This is what the group looks like after attempting to add additional items to the group.

This task of separating the subcontractors into a separate group has proven to be most challenging.

Maybe because it’s a manually created Group, the fact that the record count is 324.

All of the YouTube videos and Goggle searching have showed examples of small pivot tables, and how to create a group in that pivot table.

None illustrate doing a group with such a large number of records, nor how to edit that group once it is created.

As I saw no answers coming in t, I just thought is easier to add a field called “sub”, put a Y in there for those that are deemed subcontractors.

After that, it was just a matter of filtering.

The cons are that now I have to generate two separate reports, as opposed to before when I could’ve just labeled that group “Subcontractors”. Now the auditor is gonna cry “oh no, now I’ve got to go through two separate reports to get the total cash disbursements.”

The pros are that it was relatively simple to create to subcontractor group and make edits as needed.

It appears that the ability to edit a manually created group [that is not predicated on an existing field in the data set] in a pivot table, just by clicking and dragging (i.e. Grouping) just is not there yet, or is just not easily done.Attachment 868760

I think that's what I did by adding the "Sub" column to the data set, but all the videos and instructions that I've seen on the Internet show that all you have to do is hold the control key down and right-click on the record and then click group.

The problem is that you cannot edit or change that group.

Plus, I think that the manually clicking and grouping works well for a small pivot table, but this pivot table ran 324 rows (which is preferable, because the data set was more than 3900 records).

YouTube video that i found on grouping in pivot tables:

https://www.youtube.com/watch?v=KGqdPGAztkY

But...it doesn't show anything about "editing" (adding/removing) the items of the group.

Yes, SubContractor and PAYEE are the same row items, for they are all vendors.

We then have to identify the "subcontractors" from the list of vendors to the auditor, saving the trouble of the auditor going through the list on their own in more than likely picking out the incorrect and misclassifying a vendor as a subcontractor.

The original pivot table does not have a field to indicate who is a subcontractor, for out of the 324 vendors, only about 15 or so are subcontractors.

So, it was initially thought to just manually select the subcontractors as shown in the YouTube video, but when edits were needed, it was discovered that editing the group in the pivot table is difficult, if not impossible.

Even the procedure demonstrated in the YouTube video is for a small pivot table, will all be rose can be seen no one screen. Dealing with 324 rows is not exactly fun when you have to page up and page down multiple times to select the vendor to include in the subcontractor group that your manually creating. I found it even harder to delete a vendor from that subcontractor group that was erroneously included.

And, it seems that with the suggestion of having to add the subcontractor column that editing the manually created subgroup is something that's not easily done.

But, alas it seems as though that was the only viable solution, again as I was looking for a solution and Excel that just is not there at this present time

I appreciate the feedback!

" only used if you have small data sets"

I think you hit the nail on the head!

From all the YouTube videos that I've seen, none mention that manually creating groups should not be used with large pivot tables.

What this also highlights is the fact that editing the group once you've created is a no no.