• Dave Foord
  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,632 other followers

  • Dave Foords Twitter

    • Headline in news that having 1 alcoholic drink a day could shorten life. To reduce the risk I think I’ll increase to 2 drinks per day! 1 week ago
    • A lot of my work at the moment, and I expect for the next few weeks, is helping clients get their #moodle sites ready for #GDPR 1 week ago
    • RT @alistairm: Many students experience needless barriers. #Accessibility & #inclusion are often left to specialists but standard tech in H… 1 week ago
    • RT @EmathsUK: It is bonkers that this petition is not getting more traction. A centralised, free to use service for advertising all teachi… 2 weeks ago
    • After 2 weeks off, back into all things #Moodle for different clients. Lots of upgrading getting ready for GDPR 2 weeks ago
  • Advertisements

Spreadsheets: How to Sort Data Onto Sub Sheets based on values in a given column

One of the most popular posts on this blog, is one I published back in 2011, titled How to automatically pull data between different google spreadsheets  I am often asked by people, is it possible to filter the data based on the value in a column, before pulling the data across?

I have created a video which shows a technique whereby data is filtered internally within a workbook, so data is pulled onto subsequent sheets, based on values in a certain column. In this example I am using a class of students, and all the grade A students are copied onto a sheet called “A”, all the grade B students are copied onto a sheet called “B” etc. This principle could easily be used to organise a list of sales by sales rep, or by region.

The video is about 13 minutes long, but well worth watching, if you are interested in this technique.

For this example I have used Excel, but this would also work with other spreadsheet systems such as OpenOffice or Google Sheets.

The file used in the video example can be downloaded here, if you want to see or copy the formulas used.

Spreadsheet – sort data onto new sheets Shared

The mechanism also uses a technique to display the sheet name in a cell in the spreadsheet. More details on this can be found at https://davefoord.wordpress.com/2015/04/16/how-to-display-the-sheet-name-in-a-cell-in-an-excel-spreadsheet/

I hope that this helps people to make better use of Spreadsheets, whether it is in education, work, or for personal use.

Advertisements

7 Responses

  1. […] Edit 29/03/2017 – Please also look at another related post – https://davefoord.wordpress.com/2017/03/29/spreadsheets-how-to-sort-data-onto-sub-sheets-based-on-va… […]

  2. Thank you for your very easy to follow video. I’ve implemented the steps in a workbook and love the results. However, I’ve run into a bit of a problem. From my master sheet I’m sorting names and info into 2 different sub-sheets. On those 2 sub-sheets, I added a column to then sort the names and info into 7 sub-sub sheets using the same procedures outlined in the video. All works well until I add new names to the master sheet and then sort those names into alphabetical order. Names look great on the 2 sub-sheets and 7 sub-sub-sheets however, the column I added to the 2 sub-sheets to facilitate the sorting into the 7 sub-sub-sheets isn’t locked to the data in the row and therefore doesn’t shift with the info when the new names are filtered in. Any suggestions on how to keep the new data on the 2 sub-sheets locked together with the data coming in from the master sheet? If you need a sample of what I’m describing email me and I’ll send you a copy. Thank you for your help.

    • Hi Sheila – glad you liked the video. The crux of the whole issue is that the sorting mechanism only works if the destination sheets are only ever going to be viewed only – as soon as you try and edit the destination sheets, you are going to have problems, and I cannot suggest a simple solution. It would be possible to write macros to do this, but I am not going to try and explain how to do that here.

      So sorry if I have raised your hopes then dashed them.

      • Thank you for your quick reply. Could you send me in the direction of tutorial or webpage that has the info on the macros I would need?

      • Learning to write macros (Visual Basic) or VBA for Excel as often known, is not a quick something to pick up. I personally learnt from a book, but nowadays there are various online tutorials – e.g. http://www.homeandlearn.org/ and http://www.excel-easy.com/vba.html and many others. You would need to learn quite a bit to create what you want though, so this isn’t a quick fix I am afraid.

  3. Hi Dave, love this video very helpful, however I’m trying to do this with my master sheet being a separate spreadsheet.. is there a way of using importrange together with the vlookup?!

    • Sorry for not replying sooner, but was away over Easter. Yes you can use the importrange in tandem with the vlookup. The easiest approach is to use the importrange to pull the contents of the desired sheet from the master to a separate sheet in the other document. You then use the vlookup as normal. Once set up you can always hide this sheet, so you won’t realise that it is there.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: