Ungrouping just part of a group in a pivot table

[benjtp]

Hi,

I have grouped hundreds of items into different groups based on different parameters. For example all products that have to do with paper, all ones that have to do with pens etc.
I therefore have product group and product group 2.
However, I need to remove one of them to essentially make it a separate group.
The only way I have managed to do so is to start over by ungrouping everything. Redoing it will take me hours. There must be a better solution than that.
Thanks in advance.

 

[RoryA]

I think you'd be better off with a lookup table and an additional column in the source data. I pretty much never manually group items in pivot tables because it's a pain to maintain.

 

[benjtp]

Thanks,
Do you have any instructions on how to do that. I have more than 1000 items and writing a long vlookup feels like I'm going to make a big file too big to load.

 

[RoryA]

You just need a two column table somewhere with the product group in the first column and the parent group in the second column. Then it's a simple =vlookup(product group, product group table range, 2, false) or, if you can sort the lookup table and be sure that all the groups are in there, you can use the faster form =vlookup(product group, product group table range, 2)

 

[RoryA]

If you're working in 365 rather than 2003, you can also use Power Query.

 

[benjtp]

maybe, but don't i still have to make an enormous vlookup covering each and every product?

 

[RoryA]

I don't know what you mean by "an enormous vlookup". You need every item to appear in your lookup table, yes.

 

[benjtp]

I don't know what you mean by "an enormous vlookup". You need every item to appear in your lookup table, yes.

What I mean is that the vlookup with have to contain multiple lines ie. if this then this 1000 times. Does that sound correct to you?
Thanks

 

[RoryA]

The VLOOKUP formula will be basically what I posted earlier. Not long or complicated.

 

[benjtp]

OK, thanks
But to my original question, is it possible without a vlookup?