Grouping large amounts of data

**LLForm** · 10-28-2014, 10:48 AM

Hello all! I'm not sure if what I'm wanting is even possible to do, but I figured it didn't hurt to ask!

I have an excel spreadsheet with a ton of different sales data. It displays all orders that had a sale price of over $1000. However, it is currently a bit of a mess so I'm trying to clean it up and have it look as user friendly as possible. I've attached a sample, imagine that but much, much larger. What I'd like to do is collapse lines that show multiple products that were ordered in a single order to show an overview of the entire order, but be expandable to be able to show the details as well. I'm not sure how well that makes sense, so I'll explain a bit more.

On the sample, Rows 5 and 6 are the same order. So are Rows 7-10. Instead of displaying each of those rows, I'd like to instead show a summary row that has info about the order (Order number, Sales Amount, Bill Date, Bill Doc, Customer, Territory, Account, Unit, Zip, Period) but can be expanded to show all data.

Is that even remotely possible? With a pivot table, maybe? I just hate pivot tables.

Thanks!!

Sample Book.xlsx

**newdoverman** · 10-28-2014, 11:19 AM

I think that there is too much data being included in one table. There is personal data for the client as well as the account data. This makes for a lot of duplication of data. It would be better, I think to have two tables, one for account action and the other for personal data for the clients. This was the client data doesn't have to be repeated for each transaction on the account. The account # would be the only common field for the two tables.

With the data the way that it currently is, perhaps your best solution would be to apply the Filter found on the Data Tab and filter on what you want to see out of the total mass of data.

**hoyasaxa215** · 10-28-2014, 11:25 AM

You can achieve this using the subtotal function based on Sales Amount. Then you can click on the number 2 right underneath the formula bar to reduce it to the basic order info, as I've done in the attached. Clicking on 3 will expand all the orders.

Once you add subtotals to your whole file, you'll see that the summary row has lots blanks in it. This can be easily fixed, as I've done on the attached sample. Click on 3 so all the orders are fully expanded. Select all the cells between the first blank cell that you want to fill in and the last one. In your sample file, that would be C3:Q12. Click ctrl+G and select 'Special'. Click the circle that says 'Blanks' and click OK, which should highlight all the blank cells. Click on the formula bar and reference the cell below the top left selected cell. In your sample, the top left blank cell is C3, so you type in =C4. Important. Do not simply hit enter. You MUST hit ctrl+enter. This will fill in all the blank cells with the data immediately below. You can copy/paste values if you'd like to remove the formulas.

Let me know if that's what you had in mind.