+ Reply to Thread
Results 1 to 10 of 10

Create separate tables for each category from a master table

  1. #1
    Registered User
    Join Date
    06-07-2013
    Location
    South Australia
    MS-Off Ver
    Excel 2016
    Posts
    21

    Create separate tables for each category from a master table

    I have a "master table" (worksheet called "MASTER TABLE") that records purchases made by three people (column B): P-A, P-B and P-C (selected from a drop-down list)
    Each purchase is placed within one of 8 categories (column C), selected from a drop-down list

    I want to have a separate worksheet for each category.
    When an entry is made on the "MASTER TABLE" worksheet the information for THAT purchase is automatically added to the next row in the corresponding category worksheet.

    I can do this manually by filtering the MASTER TABLE for each category, and then copy-paste the filter results into the appropriate category worksheet - this is what I have done in the attached sample file.

    Is it possible to get this result automatically as a new purchase is entered.
    Thank you very much for any help.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Create separate tables for each category from a master table

    Hi

    VBA Books Sheet
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Do next one till you done Other sheet!

    See the file
    Attached Files Attached Files

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,117

    Re: Create separate tables for each category from a master table

    Try this ARRAY formula A2, copied down and across....
    =IFERROR(INDEX('MASTER TABLE'!A$2:A$1000,SMALL(IF('MASTER TABLE'!$D$2:$D$1000="Books",ROW('MASTER TABLE'!$A$2:$A$1000)-1),ROWS($A$1:A1))),"")

    You would need to change "Books" for each sheet, or, if you want to automate that too, put this in a cell somewhere (say, H1), and then reference it...
    =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

    The 1st formula then becomes...
    =IFERROR(INDEX('MASTER TABLE'!A$2:A$1000,SMALL(IF('MASTER TABLE'!$D$2:$D$1000=$H$1,ROW('MASTER TABLE'!$A$2:$A$1000)-1),ROWS($A$1:A1))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    06-07-2013
    Location
    South Australia
    MS-Off Ver
    Excel 2016
    Posts
    21

    Re: Create separate tables for each category from a master table

    Thank you!
    Works like a charm! (as you would know)

    Very much appreciate your time and expertise to help me!

    How do I mark this post as SOLVED?

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,117

    Re: Create separate tables for each category from a master table

    which 1 did you use?

    To mark your thread as SOLVED, use Thread Tools (located above your first post) and choose "Mark this thread as "solved".

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,117

    Re: Create separate tables for each category from a master table

    deleted my duplicate post

  7. #7
    Registered User
    Join Date
    06-07-2013
    Location
    South Australia
    MS-Off Ver
    Excel 2016
    Posts
    21

    Re: Create separate tables for each category from a master table

    Quote Originally Posted by FDibbins View Post
    which 1 did you use?

    To mark your thread as SOLVED, use Thread Tools (located above your first post) and choose "Mark this thread as "solved".
    Awesome! - thanks for that. Done.

  8. #8
    Registered User
    Join Date
    06-07-2013
    Location
    South Australia
    MS-Off Ver
    Excel 2016
    Posts
    21

    Re: Create separate tables for each category from a master table

    Quote Originally Posted by FDibbins View Post
    Try this ARRAY formula A2, copied down and across....
    =IFERROR(INDEX('MASTER TABLE'!A$2:A$1000,SMALL(IF('MASTER TABLE'!$D$2:$D$1000="Books",ROW('MASTER TABLE'!$A$2:$A$1000)-1),ROWS($A$1:A1))),"")

    You would need to change "Books" for each sheet, or, if you want to automate that too, put this in a cell somewhere (say, H1), and then reference it...
    =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

    The 1st formula then becomes...
    =IFERROR(INDEX('MASTER TABLE'!A$2:A$1000,SMALL(IF('MASTER TABLE'!$D$2:$D$1000=$H$1,ROW('MASTER TABLE'!$A$2:$A$1000)-1),ROWS($A$1:A1))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Wow! Thank you Ford. This also achieves exactly what I wanted!
    Two brilliant solutions to my problem and there would be no way I could have done this without your willingness to share you expertise.
    MANY, many thanks

  9. #9
    Registered User
    Join Date
    06-07-2013
    Location
    South Australia
    MS-Off Ver
    Excel 2016
    Posts
    21

    Re: Create separate tables for each category from a master table

    Quote Originally Posted by FDibbins View Post
    which 1 did you use?

    To mark your thread as SOLVED, use Thread Tools (located above your first post) and choose "Mark this thread as "solved".
    Sorry - there was a time difference in you posting and me trying to implement them.

    I think I will be using your solution Ford as it doesn't involve using macros/VBA code (which I am not familiar with)
    However I was more than happy to use Michael's code as it completely solved my problem as well.

    Both were exactly what I wanted to happen. Thank you Michael.
    And big THANKYOU to you Ford for your brilliant solution - one that I will implement and get to learn from.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,117

    Re: Create separate tables for each category from a master table

    thanks for the kind words and rep, I am happy to help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA: table into several tables based on a category
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2014, 10:11 PM
  2. [SOLVED] Create continuous non blank table based on merge of separate dynamically filled tables
    By escobf in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-15-2013, 04:15 PM
  3. Automatically create subsheets based on master sheet category type
    By upsidedownjim in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-10-2013, 02:21 PM
  4. Macro - Create smaller word tables from master excel table
    By VBA_n00b in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2011, 10:12 PM
  5. Replies: 4
    Last Post: 06-19-2009, 09:02 AM
  6. create separate files from master
    By izza56 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-15-2007, 05:33 AM

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