+ Reply to Thread
Results 1 to 9 of 9

Collate a list of unique items only from multiple sheets into specific categories

  1. #1
    Registered User
    Join Date
    08-26-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Collate a list of unique items only from multiple sheets into specific categories

    Hello,

    I am trying to create a master sheet that shows unique items from cells C7:C16 across 40 worksheets, and list them in each category they are attached to according to cells G7:I16 (they can be attached to up to 3 different categories) on the master sheet.

    The idea is to be able to enter all the criteria for each unit separately into the appropriate worksheet (column c), apply it to an outcome/outcomes (columns G:I), and then have the macro (or formula, if that is easier?) compile the data into the outcome categories on the master showing each unique criterion in each category (which means a criterion can be found in more than one category, but only once in each). This way we can see at a glance if the required outcomes are being met by the criteria or not. At the moment, with 9 outcome categories and 9 criteria per category across 40 units, it is a big task to see if there is any deficiency. Being able to compile the list, we can both quantify and qualify the criteria to ensure it meets the required outcomes.

    I have attached a zipped version of a cut down example showing 8 sample worksheets, the master worksheet (called 'Learning outcomes'), an attempt at writing a macro to compile the data into a single list (worksheet entitled 'ULO list') and another worksheet. The full workbook has 10 worksheets that compile data (including 'Learning outcomes') and 40 individual worksheets with the actual data for each component which is manually entered.

    CurrMapSample1.xlsm

    Thank you for your help.
    BR
    Last edited by Boyler_Room; 01-14-2013 at 09:35 PM. Reason: Updated attached file

  2. #2
    Registered User
    Join Date
    08-26-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Collate a list of unique items only from multiple sheets into specific categories

    Does what I am asking make sense? Is it possible?

    Any help you could provide would be much appreciated. I would just plug away at it myself but we have to provide data to a regulator in the next couple of weeks and I am busy trying to prepare everything else to accompany it.

    Thanks
    BR

  3. #3
    Registered User
    Join Date
    08-26-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Collate a list of unique items only from multiple sheets into specific categories

    I am sorry to bother you again, but should I presume from the silence that this is not possible? Or does my request simply not make sense?

    Could anyone help, please?

    Thanks
    BR

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Collate a list of unique items only from multiple sheets into specific categories

    I can't make that for you, but maybe you have more change, if you post an small example of your data instead of a zip file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Registered User
    Join Date
    08-26-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Collate a list of unique items only from multiple sheets into specific categories

    Thanks oeldere. I have cut four tabs out of the file to reduce the file size enough to upload the sample rather than needing to zip it up.

    I hope this helps.

    The idea is to list the categories under the main table in the Learning Outcomes tab. Whether vertical or horizontal, it doesn't matter. The key is to be able to see the list, and for the list to update if any further tabs are added in future, or any further learning outcomes added to existing tabs.

    Thanks
    BR

  6. #6
    Registered User
    Join Date
    08-26-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Collate a list of unique items only from multiple sheets into specific categories

    Perhaps I am asking the wrong question.

    Is it possible to create a list of all the data from multiple worksheets with tables containing different data sets in the same format, and sort that data into specified categories (which are identified in the table as GLOs). For example a table within a worksheet might look like this:

    ULO Primary GLO Seconday GLO Other GLO
    Developing and understanding ABC 1 - -
    Reflecting on XYZ 6 2 -
    Demonstrate effective application of ABC 5 1 3
    Identify QRS 4 - -

    The idea is to be able to sort on a master sheet all the ULOs into the GLO categories. If a ULO is attached to more than one GLO then it should appear in both category lists. I only want each ULO to appear once in each category list though. I have 40 worksheets to extract data from. There can be up to 9 GLOs (categories) and up to 9 ULOs in each GLO.

    Can you help?

    Thanks
    BR

  7. #7
    Registered User
    Join Date
    08-26-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Collate a list of unique items only from multiple sheets into specific categories

    Sorry to keep asking, but can this be done at all?

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Collate a list of unique items only from multiple sheets into specific categories

    I took a look at your file, but:

    1) I don't understand which data need to be on the master sheet.

    2) you get merged cells in your data, which also makes it more difficult.

    I'm no VBA expert, so I can't help you enough with this one.

    But seen your data, I think it's a hell of a job (making an VBA solution for that).

  9. #9
    Registered User
    Join Date
    08-26-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Collate a list of unique items only from multiple sheets into specific categories

    Thanks oeldere. Do you have any suggestions for how I could format it to make it a little more straight forward?

    Or can it be done with two macros? Perhaps one that collects all the data from all sheets, and another to sort and remove duplicates?

    Regarding the data I am trying to capture on the master sheet, in the tabs labelled "Unit #" I am trying to capture all the data in cells C7:C16, sort it according to the categories attached in E7:G16 (i.e. C7 would appear under the categories identified in E7, F7, and G7), and remove any duplicate entries in each category.

    I've just looked at my original post, and noticed that because I made some changes to the worksheets to get it to fit into the size limits, my cell references are wrong. I cannot edit my post, unfortunately. The correct cell references are in this post.
    Last edited by Boyler_Room; 02-28-2013 at 08:07 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1