I have one table filled with customer data. Some examples:

  • date of last contact
  • gender
  • ...

Now I want to create a new table where only male customers are shown, ordered by the date of the last contact.

Is there a build in function to achieve data aggregation in this way or do I have to write a script? It occurs to me that there has to be some built-in way because I think it's a common task to evaluate data in this way.

  • So far I discovered that i can convert my range into a Table and filter/sort. But that only allows me to create one Table and not multiple (maybe a dashboard) with different kind of sortings/filters – rootman Feb 3 '12 at 15:51
  • Have you tried using a pivot table? Could you be more clear in what you're trying to do? I understand you want to use data you have and examine it based on multiple attributes. A pivot table will do that. – Raystafarian Feb 3 '12 at 16:12
  • No I havn't used pivot tables so far. I am quite new to Excel, I've used it occasionally for easy tasks but never took a dive into all the possibilities. I will give pivot tables a try! – rootman Feb 3 '12 at 17:55
  • If pivot tables work you can copy -> paste special -> values to new sheets or you can use the camera tool to take screen shots of the tables. Also, if they work for you come back and let me know so I can "answer" the question properly – Raystafarian Feb 3 '12 at 17:57
  • Look at advanced filters as well. You can create macros to pull what you need into different sections based on criteria and uniqueness etc – datatoo Feb 3 '12 at 22:20

Look at using Advanced Filter. You can either filter in place, or set rather elaborate critera and Filter to a new location.

It is really quick and you can setup your data range so that if it is refreshed from an external source you can easily pull a new table to view.

You can also set your criteria fields with your most common choices using data validation lists or dropdowns and have a fairly extensive report generator.

  • advanced filters look interesting, but i don't know if they do the trick. this is the excact thing i want to do: i have a table with costumer data: name | date | ... it is setup as a table and new records will be added. now i want another table just showing the name and date. filtered by dates (should be in the future). i could filter my first table, but thats not what i want to do. so how do i link the two tables, thats all i need basically – rootman Feb 9 '12 at 11:20
  • That is exactly what advanced filter is good for. Your original table is treated like a datasource, you setup criteria, and you can have the results write to a new table location filtered as you wish. – datatoo Feb 9 '12 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.