Closed Thread
Results 1 to 12 of 12

Extract Data from one sheet into another one based on criteria

  1. #1
    Registered User
    Join Date
    07-11-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    9

    Extract Data from one sheet into another one based on criteria

    Hello all,

    I am going to try and explain this as best as possible and hopefully someone can help me out. I am definitely not an excel wizard but have enough computer proficiency to follow any advice you guys could have... I think

    Ok so I have a Full Database of students enrolled in classes in my main sheet. I would like something to look through all the entries in the main sheet and extract students that are in a particular class and put their information in another sheet.

    So, in my full database I have a row for each student with various information: course enrolled in, student name, email address, phone number, etc and I would like to have an separate sheet for each class (Math, Science, etc) and have the individual databases take each student and all their information from the full database but only if the student is in that class.

    I have tried a couple different ways, VBA programming with wordy if and and and while loops, but nothing has quite done it I dont think. At the same time, I feel like there has to be some intrinsic and simple way to do this.

    Unfortunately, I would really need the individual database to automatically update if I enter a new student or change a student's information in the full database also.

    If this is too complicated or you do not want to totally help me cheat on this project, any tips about how I can grab information from different sheets based on a criteria and then neatly put it into a different sheet would be greatly appreciated.

    Thank you so much!

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

    Re: Extract Data from one sheet into another one based on criteria

    If it has to be VBA, I can't help you enough.

    You get better help if you post an Excel-example of your workbook, without confidential information.
    Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix.
    Use BEFORE/AFTER sheets if that helps make it clearer.
    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.

  3. #3
    Registered User
    Join Date
    07-11-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Extract Data from one sheet into another one based on criteria

    Thank you for the advice, it does not have to be in VBA, I will take any solution I can get. I attached a sample workbook of what I want. Keep in mind, I would like the individual class sheets to update automatically if they could, as you enter new entries into the full database.

    Thank you again!
    Attached Files Attached Files

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

    Re: Extract Data from one sheet into another one based on criteria

    With VBA I came to this solution (see the example)

    the code is from Ron de Bruin.

  5. #5
    Registered User
    Join Date
    07-11-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Extract Data from one sheet into another one based on criteria

    Quote Originally Posted by oeldere View Post
    With VBA I came to this solution (see the example)

    the code is from Ron de Bruin.
    Thank you very much. It is going to take me a while to sift through this because I am not too used to reading VBA code so I am not exactly sure what everything does. Thank you very much for the response though.

    A quick question: what exactly do I have to do to run the macro again, with new values (I am not too familiar with macros in excel)? Also, can I make it into a button for other people to use easily?

    Thank you!

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

    Re: Extract Data from one sheet into another one based on criteria

    I'm not very good at macro's either.

    But this one works (I tested it).

    It add an workbook of each unique value in collumn A and then add the value from collumn A to D.

    To run the macro you can use ALT + F8.

    To see the macro you can use ALT + F11 (there are 2 macro's available).

    Use the macro with the name (in module1) Copy_To_Worksheets()

    If the sheets already exist it makes an new workbook with the name err etc.

    You get an pop up of this to inform you.

    Then you have to rename those sheets.

    Always try code on a dummy workbook before trying it for real.
    Last edited by oeldere; 07-11-2012 at 03:37 PM. Reason: You get an pop up of this to inform you.

  7. #7
    Registered User
    Join Date
    07-11-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Extract Data from one sheet into another one based on criteria

    Quote Originally Posted by oeldere View Post
    I'm not very good at macro's either.

    But this one works (I tested it).

    ...
    Thank you, I see that now and it look appear to work pretty well. Thank you so much for the help. My only other question would be, is there a way, if you add additional entries to the full database, is there a way to update the individual ones instead of having to create new ones?

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

    Re: Extract Data from one sheet into another one based on criteria

    Yes, there is.

    But as i already told you, I can't help you enough with that.

    How do you get the value in sheet1 (manualy)?

    How often are changing the value and do you need to update the worksheets (how often do you want to run the macro?)
    Every day, every week, every month, every year?


    Maybe it's an option to delete all worksheet (with VBA) exept the frist and then run the macro.

  9. #9
    Registered User
    Join Date
    07-11-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Extract Data from one sheet into another one based on criteria

    Yeah, the information in sheet1 is inputted manually. I need to run the macro everyday

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

    Re: Extract Data from one sheet into another one based on criteria

    I think you get better help (and so I advice you), if you post a new question on the forum (Excel programming).

    Please refer (link) to this question, so other forummember can see (read) which solutions already been given.

  11. #11
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Extract Data from one sheet into another one based on criteria

    Hi,

    You should use Data Filter Advanced Filter, with the copy to another location option.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

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

    Re: Extract Data from one sheet into another one based on criteria

    I had though about that, but I think it's not easy to do (every day) with probaly a lot of unique names.

    The starter of the topic made an new question in Excel programming.

    You find the link of that question below.


    http://www.excelforum.com/excel-prog...ller-ones.html

Closed 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