Is there a way to hide data and and shift data around it without hiding entire row/column?

[max3732]

As you can see from the basic sample attached I have a table showing different suites, tenants in the suite and other data with the tenant like sqft.

The issue is I have one tenant taking multiple suites, like all of suite B or suite C and it still works, but is kind of busy showing it multiple times. What I'd rather do is have it like the after where it consolidates them all together.

I can manually type in the suite name and square footage so don't need a formula to do that.

What I'd like would be a way to hide just the data in the table without also affecting the rest of the data in the columns/row. I know I could simply delete them and move the data up, but I have a number of different formulas that go off of the way I have it setup now that would stop working. (the data you see in the sample is aggregated from another tab in my actual sheet). Also if the tenant leaves or takes less square footage later I'd like to be able to subdivide it again without having to relink all the formulas.

Is there a simple way to do this?

[ORoos]

Hi there,

I think a simple pivot table would work in this scenario.

Please see the attached file.

You can expand/collapse the data for each tenant, so you have the total, or just click on the small '+' to see the details.

After you made changes in the data, just refresh the pivot table. The table can also be on a separate sheet if preferred.

Hope this helps.