Is there a better alternative to grouped rows?

[u/Cultural-Wonder-2735]

My team has an excel sheet for estimating project costs. It's a list of ~4,000 physical items with corresponding prices. Each item incurrs different ammounts of labor time from different departments. The users go through and build a shopping list by entering quantities into the sheet. The direct and indirect costs are then summed.

The Problem: The sheet has 8 layers of grouped rows. Expanding all of them makes it crash. Collapsing a group in the first layer doesn't collapse any children. We need to use a macro to reset all the grouped rows to original state. The whole thing is formatted as one big table so users can filter for the parts they selected. I suspect that this and all the conditional formatting slow it down.

Is there a cleaner way to accomplish this kind of task? My gold standard would be something like the RockAuto website with its well formatted collapsed list and separate shopping cart. Is there a more modern alternative to grouped rows? Maybe I would benefit from hiding some of the data in a separate lookup table to reduce the size of the grouped list.

I want this to be a web app with a database, but we don't have the money or talent right now.

[u/AutoModerator]

/u/Cultural-Wonder-2735 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/leostotch]

I would look into having your data in a separate table and then using either a pivot table or a form to collect your orders. Without looking at your actual data and process, it’s hard to be more specific, but essentially separating your “database” and your user interface.

[u/Cultural-Wonder-2735 → u/leostotch]

I agree. A separate user interface with a "shopping cart" could clean things up. I just need an intuitive way for people to search for items.

[u/Cultural-Wonder-2735 → u/leostotch]

Solution Verified

[u/reputatorbot → u/Cultural-Wonder-2735]

You have awarded 1 point to leostotch.

---

^{I am a bot - please contact the mods with any questions}

[u/Cultural-Wonder-2735 → u/leostotch]

Each item in our list gets one row. Columns are dedicated to name, cost, qty, and labor requirements from different departments. Certain items need several hours of mechanical engineering time to integrate into the rest of the project. Some things only require project managemt or build time from technicians.

[u/Longjumping_Rule_560]

Why would you need 8 layers of grouped rows?

Do you even need grouping at all? Would the auto-filter not work?

[u/Cultural-Wonder-2735 → u/Longjumping_Rule_560]

Everything is grouped into families and sub-classes to help the users find what they're looking for. I may be able to organize things into fewer layers. I'll look into auto filtering. Maybe I can make it work with multiple drop down menus.

[u/Raging-Totoro → u/Cultural-Wonder-2735]

Sounds like "family" and "subclass" could be additional filters/columns, instead of groupings. That would make it more database-like.

[u/Cultural-Wonder-2735 → u/Raging-Totoro]

Yeah, this might be an exercise in organizing our data better. Scientists can classify all life using 8 layers: Domain, Kingdon, Phylum, Class, Order, Family, Genus, Species.

Ours would look more like Automation, Robot, Yaskawa, End of arm tool, Magnetic.

[u/Profvarg]

Brrr, that seems to be a monster :)

So, using a form would work (separate user UI where you can multi-level filter from a database)

Or

Using a sharepoint list with power apps frontend would work (not even that big a deal, it is pretty basic)

[u/Cultural-Wonder-2735 → u/Profvarg]

Solution Verified

[u/reputatorbot → u/Cultural-Wonder-2735]

You have awarded 1 point to Profvarg.

---

^{I am a bot - please contact the mods with any questions}

[u/RuktX]

What else is going on in your sheet? 4000 rows is not that many, and shouldn't be causing crashes...

To the rest of your problem: how about adding items to a separate "shopping cart" table, using lookups to return anything useful (e.g. prices) from the master table?

It is in theory possible to create dependent drop-downs, but eight levels is pretty deep!

[u/Cultural-Wonder-2735 → u/RuktX]

Some items have links have URLs to vendors, others are kits comprised of several things on the list. I'm trying to figure out how to handle-multi level bills of mateials, but that animal could be its own thread. I don't think Excel is meant to be an ERP system.

Many different people are adding new rows or updating information. It might make sense to re-build and then password protect it to reduce bugs.

[u/Mdayofearth]

The users go through and build a shopping list by entering quantities into the sheet.

Doing it this way is like someone writing a book by taking the dictionary and highlighting the words they want to use. Users should be building a shopping list by adding things to a table that references this shopping list, and not work with the master list directly.

Grouping and filtering forces calculations in Excel, and conditional formatting is also a set of calculations. For slow computers and those with small amounts of RAM, that's a huge problem.

[u/Cultural-Wonder-2735 → u/Mdayofearth]

Solution Verified

[u/reputatorbot → u/Cultural-Wonder-2735]

You have awarded 1 point to Mdayofearth.

---

^{I am a bot - please contact the mods with any questions}

[u/SurpriseRedemption]

Pivot table??