0

This is an example of the data I have: 

Country     Score   University  
a           5       
                    1   
                    2   
                    3   
b           7       
                    1   
                    2

I want to be able to hide/unhide the university rows and sort by the score column while keeping the universities under their countries.

Is there any way to do this? I tried grouping but it didn't do what I want.

romanbird
  • 103
  • 1
    No. This can't be done without changing the way you have your data organized. A better way, would be to make a Table in Excel with the columns Country, Score and University. Make sure every record (row) every column filled in. The order of the rows doesn't matter anymore then. After that, create a pivot table based on that table. That gives you everything that you asked for (and more! ;-) – agtoever Aug 12 '14 at 15:05
  • As pointed out by @agtoever, you have way little information in your sheet to achieve what you want. Way to go, is to populate country and score details for university rows as well. This way you can use the filter or sort based on country/score (and also retain relevant University details). If sorting and filtering is all you require, that should be good. Else, you can go for pivot tables to play around a lot more with the data. – jjk_charles Aug 12 '14 at 15:13
  • Thanks for the comments. I don't want to populate the cells that are empty. I was thinking of using a database, since the information I'm tracking is relational. I need to keep track of several things for each university, and didn't show that above for simplicity. I haven't worked with databases before but I have a few hours to learn a new thing. I will make a database in Access. Good idea? – romanbird Aug 12 '14 at 16:16
  • No; I'd stay away from Access. Stick to Excel. That'll do the job just fine. I'll summarize my response in the answer so you can accept it if that's ok by you. – agtoever Aug 12 '14 at 19:42

1 Answers1

0

There is now way to do this with the data in the structure that you have.

It can be done with a minor adjustment:

  • Make a "Table" in Excel with the following columns: Country, Score, University, like this:

    +-------------+-------------+-------------+
| Country     | Score       | University  |
+-------------+-------------+-------------+ 
| a           | 5           | 1           |
| a           | 5           | 2           |
| a           | 5           | 3           |
| b           | 7           | 1           |
| b           | 7           | 2           |
| ...         | ...         | ...         |
+-------------+-------------+-------------+

  • You can use the table to sort and filter.

  • Create a Pivot table based on this table (Insert, Pivot table, select the table above) and you can do any analysis by drag-and-dropping fields in de row/column headers or in de value field.

Good luck!

agtoever
  • 6,272
  • Pivot table can do the job in Excel and this answers my question. I have already started a database in Access and want to keep using it. – romanbird Aug 13 '14 at 03:23