Advanced data sorting in Excel 2007?

Question

I want to know if its possible to sort data in Excel 2007 as follows. If it is possible can someone tell me how to do it please?

DATA BEFORE SORT:
Red 10
Blue 30
Red 10
Yellow 20
Blue 20
Red 20
Yellow 10
Blue 10

DATA AFTER SORTING:
Blue 60
Red 40
Yellow 30

The data might be a lot more extensive than the example (in that the list may be longer but no more info than Name and Numeric Value), but im sure you know what im getting at, basically I need to group same named items together in one group with the sum total of all the values at the side, from one long list of data. The names and numbers would be in separate cells, of course.

Please help me if you can! Your help would be much appreciated!

Regards,

Ross

Answer 1

Glad your data is in separate cells as thought first of all it was in the one. To start with highlight all data but NOT the column headings then, from the Home menu click on the Sort icon which is to the right of the ribbon. (Or from Data menu, the sorting is in the middle of the ribbon. If your data werejust in the one columnyou could just click on the A-Z and it will sort everything.



However, as you have rightly got it in different columns, , click on custom sort, then sort firstly by colour and then by figures so that if you were to have, for instance, blue 60, blue 45, the blue 45 would appear before the blue 60. If you then want totals of each colour, as it is sorted, you could then do a subtotal of each category which would be easier than doing a sumif function. If you use the Data menu for sorting, then the subtotalling is in the same menu, to the right side but in this case you need to highlight the column headings as well.

Answer 2

Yes, but you need to do another table for that



- Create a list of unique items in another range of cells (Or sheet)

In your example:

Red

Yellow

Blue



- Paste SUMIF function in the next cell like this

=SUMIF( A:A, G1, B:B)

Assuming your long big list is in column A, the values you want to sum in column B and the unique list is in column G



- Then Fill that cell donw to cover all your values



Then do the sort for this new table G and H



Good luck



VBAXLMan

Answer 3

That is not sorting, but summing. You can use the sumif function to sum all numbers where the color is blue.

Answer 4

I agree