Sorting grouped rows

melpotter22

New Member
Joined
Nov 29, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
I created a workbook where I took orders and separately entered items all under the same persons name. I figured out how to group all of the information, but I am now trying to sort the data and I cannot figure it out (I am not an excel expert...I am simply a volunteer for a PTO trying to do a fundraiser, so speak simply if you respond please! lol!) I want to be able to organize it alphabetically, and then be able to sort by payment method.
Screenshot 2021-11-29 220023.jpg
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Welcome to the board!

Sorting data in that format is a nightmare. I don't think I could do it. But I'll give you an easy alternative way: Create a pivot table from your data.

First of all you're going to have to manipulate your data to get all the names & payment methods ( basically all the info you might want to sort / slice your data by ) on each and every row. I'd use Power Query to do this but you should be able to do it manually as well:
First select the name columns and make sure the cell format is General (basically anything other than text should do). And even before that you might want to save your file with another filename just to make sure your data is safe even if you happened to mess up.
Then you should select Blanks from the Go To Special dialog found under the Find & Select -menu on your Home tab of the ribbon (or shortcut Ctrl + G followed by Alt + S and Alt + K ). If your empty cells are actually empty they all should be selected by now. To fill them with the value from the cell above type "=" and click the cell above your active cell. Press Ctrl + Enter to fill your selection with the formula.
You can do several colunmns at a time if you click Ctrl when you select them.
To fill the cells with the data from the cell below (as in the Method of Payment) do the same but click the cell below your active cell when you write the formula and click Ctrl + Enter to fill the cells with that formula as well. You might want to clean up your Method of payment column data little bit to make it easier to add up: Instead of all the check numbers and venmo details you might want to replace them with a simple Check / Venmo instead. Doing this in a helper column might not be a bad idea. Also, get rid of all the total rows you might have in your data. You're not going to need them with pivot tables.
When you have all the data you want in every row you're almost done. All that is left to do is the pivot table:
Select all your data (including the headers) or - if there's no empty rows / columns - just a single cell. Then go to Insert tab and select Pivot Table. You should see the Pivot Table Fields -dialog on the right side of your Excel window.
If you want to see the sum of Price by Customer Name, for example, click (or drag) the Customer Name from the bigger field on top of the Pivot Table Fields dialog to the Rows field on the bottomleft of the dialog. Then click (or drag) the Price field to the Values field. Your pivot table should now show the totals by Customer Names.
To see the sums by Method of payment either replace the the Customer Name on your pivot table with the Method of payment and you're done.
To see the method of payment as well as the Customer Name you can either drag the Method of payment next to the Customer Name in the rows field or drag it to the Columns field.
Basically, that's all there is to it: The hardest part is getting your data to a usable form and once you have created the pivot table you can slice and dice the data any possible way you want to.
 
Upvote 0
Thank you so much! I was afraid I was going to have to fill all the cells. I’ll format it that way in the future for easier sorting! Thanks so much for replying!
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top