Need help grouping by sales order # and sorting high to low

[bhoth]

Hi All,

I am trying to figure out how to Group by sales order number (column F) then sort by high to low dollar amount per sales order(column I). Right now it's sorted by high to low dollar amount by line item.

I have played around with a Pivot Table but the only problem is I need to be able to add comments after sorting /manipulating the data. Pivot tables don't allow editing of any field. I also played around with Data-Subtotal but it didn't want to group like I need it to.

Thank you so much for any help!

See photo below:
2018-01-13 00_05_48-Excel test grouping.xlsx - Excel.jpg

[BadlySpelledBuoy]

Select cell I2 and sort high to low (Z-A) then click in cell F2 and sort whichever way you want.
It will group the data by sales order number but leave the dollar amounts in descending order for each.

Or have I misunderstood what you're trying to achieve?

BSB

[bhoth]

I wish it would work like that but it does not. As soon as you sort the column F2, it breaks the I2 column sort. Sales order 235679 is the biggest by dollar amount ($749.90) so I want it listed first with all it's line items, then 129369 ($711.61) then lastly sales order 123456 ($227.30). It would be nice to have a subtotal (could be a new column with subtotal).
2018-01-13 08_45_22-Excel test grouping.xlsx - Excel.jpg

[BadlySpelledBuoy]

Looks like it's sorted just fine to me. Sales order ID's are grouped together and amounts are in descending order.

Can you manually create what your desired outcome is and show is that?

BSB

[bhoth]

Sure see below, I want the sales orders sorted high to low so the sales order with the largest value is first, sales order with the 2nd highest value sorted second and so on.
2018-01-13 09_16_55-Excel test grouping.xlsx [Repaired] - Excel.png

[AliGW]

Welcome to the forum!

Firstly, please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Secondly,will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[bhoth]

Hi See attached the Excel file with the data setup manually.

[Glenn Kennedy]

Is this what you had meant??

Untitled.png

[bhoth]

Sorry no that does not work either. I really could care less how the sales order numbers are sorted. I want sales order line items grouped together with the sales order AMOUNT sorted high to low.

Maybe my example does not work because there are not enough. Picture 1000 sales orders, all of them vary by the # of line items and by dollar amount. I want Excel to look through all the sales orders, figure out which sales order has the highest dollar amount, and group all the line items in the sales order first.

Then look for the second highest sales order by dollar amount, group all the line items together and display right underneath the first sales order and so on.

[Glenn Kennedy]

Like this??

Sort by helper and then amount??
If not, manually sort a sample and show what you want.

[sandy666]

maybe another point of view:

[bhoth]

See attached manually set up exactly how I want it. Note that the sales order numbers are all out of order. The sales order with the highest dollar amount is first (with the line items sorted high to low). Then the sales order with the second highest dollar amount is listed next (with the line items sorted high to low) and so on.

[Glenn Kennedy]

How does that differ from the attachment at Post 10???

[bhoth]

Hi Glen, sorry I missed that before but yes that is correct. So how did you do that?

[Glenn Kennedy]

Well... as described. Here's the sort box

[bhoth]

I'll take a closer look at this thank you!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.