Get value of category and set it in the next column

Question

I have a category variable and two other variables (services and price). I want to add the value of each category as an extra row and remove the category variable completely. I try to do this with a simple IF statement but obviously, I miss the first row of each category. How can I include the first row of each category in the desired output?

thanks

Answer 1

You can do it with Pivot Tables:

Category and Service Fields into Rows section and Price into Values section. Compact Design and deactivate subtotals.

Then you can copy/paste as values the Pivot Table and delete it, so you'll get your expected output.

With normal formulas in Excel it would be a really complex and heavy formula. With this steps you can do it literraly in seconds. Probably you could code something in VBA too.