+ Reply to Thread
Results 1 to 20 of 20

Need to pull all instances of a name from 1 sheet to a summary sheet in the same workbook

  1. #1
    Registered User
    Join Date
    05-12-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    25

    Need to pull all instances of a name from 1 sheet to a summary sheet in the same workbook

    I have a sheet that shows each instance of a student's demerit at school. I need to create a summary sheet that allows me to select a student by student ID # and it will populate with all of their demerits from the first sheet. The main sheet was not my creation so I am having trouble making things pair up.
    Attached Files Attached Files

  2. #2
    Forum Moderator 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
    27,960

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    Why not either
    a) Filter the data, either with an Autofilter, or an Advanced filter which extracts the records to an output area or
    b) Use a Pivot Table with Slicers which allow you to filter the data. See attached
    Attached Files Attached Files
    Richard Buttrey

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

  3. #3
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    Why not just use a pivot table? Use the Student ID field as a Filter, and put the fields you want to see in Rows. Format to suit.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Registered User
    Join Date
    05-12-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    25

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    I want people to just use the filter but I have been asked to come up with a summary page.

    I am not sure how pivot tables work. I know enough about excel to be the person people ask to do things but not enough to be able to figure this out.

  5. #5
    Forum Moderator 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
    27,960

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    Quote Originally Posted by rr23724 View Post
    I want people to just use the filter but I have been asked to come up with a summary page.

    I am not sure how pivot tables work. I know enough about excel to be the person people ask to do things but not enough to be able to figure this out.
    A Pivot Table IS as summary page.
    I'm struggling to understand why you would want to complicate this.
    A Pivot table requires no formulae and can be set up in less than a minute. It requres little knowledge of Excel functions or anything else so given your comment then it would seem the most sensible approach.

  6. #6
    Registered User
    Join Date
    05-12-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    25

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    To clarify...I am not making this difficult. People above me are the ones asking for this. They want a nice printable summary page that looks pretty. I understand that you think a pivot table is the answer or that filtering is the way to go. I would just use the filter option personally. But I am not the decision maker here. If you can't or don't want to help me with a summary page, then I appreciate your input but please move on to the next person who needs help.

    Thank you
    Last edited by rr23724; 09-19-2019 at 10:02 AM.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    Wow !!

    Richard has been very helpful and has given you a solution in Post #2 (see Sheet3 in his file).

    Does this not do what you want? If not, please explain what you want the output to look like (your Sheet2 is just blank, with no indication of how you would like it to appear).

    Would you be more comfortable with a formula-based solution, rather than a pivot table?

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    05-12-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    25

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    Yes...a formula based solution. The pivot table is not the answer for what I have been asked to provide even though it does pull the information. And I thought I clearly stated that I don't know how pivot tables work. If not, I apologize.


    I didn't think what the page looks like mattered since as long as I have the formula I can put it in the cells I want them in and pretty the page up for the admins how they want.

    See new attachment, I tried to show a general view of what they want. Yes, I understand it looks a lot like the pivot table but I don't know how to manipulate the pivot table to make it look cleaner and print how they want. I do know how to manipulate it if it has formulas in the cells.
    Attached Files Attached Files

  9. #9
    Forum Moderator 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
    27,960

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    Quote Originally Posted by rr23724 View Post
    To clarify...I am not making this difficult. People above me are the ones asking for this. They want a nice printable summary page that looks pretty.

    Thank you
    'Pretty' is an extremely subjective word in this context and largely in the eye of the beholder. I will have one view, you will perhaps have another and the 'people above you' maybe a third.

    Therefore don't expect us to guess what your pretty document should look like. You need to show us. So knock up an example based on the information you've given us and reupload the workbook

    One way or another if you're not going to use a Pivot Table you will almost certainly need to use a filter of some sort. In truth it's not a summary you are looking for but a subset of your existing data. That means the output will no doubt vary in length from run time to run time. Now you can automate that with a macro or you can do it manually.

    Be aware though that any 'pretty' report that strays too far away from a simple table of information, with perhaps blank rows / columns, or other formatting like bold text or colours will need a lot more familiarity with Excel - particulaly if a macro to do the job is not an option.
    I sense from your previous comment that you may be at the beginning of your Excel journey. If the people above you are expecting stuff that you're uncomfortable about producing then unless they're prepared to help you increase your knowledge then you'll need to set their expectations at a somewhat lower level.

    All sorts of stuff is capable of being done with Excel but as in all aspects of life the more detailed something is the more knowledge that is required. That's simply a fact of life and not limited to Excel alone.

  10. #10
    Registered User
    Join Date
    05-12-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    25

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    Quote Originally Posted by Richard Buttrey View Post
    'Pretty' is an extremely subjective word in this context and largely in the eye of the beholder. I will have one view, you will perhaps have another and the 'people above you' maybe a third.

    Therefore don't expect us to guess what your pretty document should look like. You need to show us. So knock up an example based on the information you've given us and reupload the workbook

    One way or another if you're not going to use a Pivot Table you will almost certainly need to use a filter of some sort. In truth it's not a summary you are looking for but a subset of your existing data. That means the output will no doubt vary in length from run time to run time. Now you can automate that with a macro or you can do it manually.
    I gave a basic idea one post before yours. I have asked for help with this same thing years ago but can not find that thread or even remember if I even had the same login back then. The solution someone gave me had something to do with INDEX and MATCH but I can't remember for the life of me how they did it. It did not require pivot tables, filters, or macros and it worked perfectly. That is the type of solution I am looking for.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    rr23724. You should wind your neck in a bit and stop being so free and easy with your complaints. We are here to help, but neither want - nor need - your disrespect.

    You can use this formula to return the pupil data in C4, copied down:

    =VLOOKUP($C$2,Sheet1!B:E,1+ROWS(C$4:C4),FALSE)

    and variants of this in the other cells (different shades = different formula):

    =IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!$B$2:$B$100)/(Sheet1!$B$2:$B$100=$C$2),ROWS(B$9:B9))),"")
    Attached Files Attached Files
    Glenn



  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    Here's a formula-based solution for you (see attached).

    I put this formula in cell L2 of Sheet1:

    =IF(B2=Sheet2!$C$2,MAX(L$1:L1)+1,"-")

    and copied it down to row 15, though you could copy it further if you have more data - the hyphens help to show where the formula is active, and the formula just sets up a simple sequence where the record is for the student you have chosen in cell C2 of Sheet2.

    In Sheet2 I have used this formula in cell C4:

    =IF($C$2="","",IFERROR(VLOOKUP($C$2,Sheet1!$B:$E,ROWS($1:2),0),""))

    and this can be copied down into C5 and C6 to bring the name(s) and grade for the student chosen in C2. Incidentally, you could have a data validation drop-down in cell C2, whereby you could select the student's ID from a pre-defined list, but I've not set this up for you. As it stands, you need to type the student ID directly into cell C2, so it is more prone to mistakes.

    Then I used this formula in A9:

    =IF(ROWS($1:1)>MAX(Sheet1!$L:$L),"",MATCH(ROWS($1:1),Sheet1!$L:$L,0))

    and copied this down to A15 (although again, this can be copied further if you feel it is required (i.e. for very naughty students) ). This formula generates the row(s) on Sheet1 where the appropriate records for the selected student can be found, and it helps to shorten the remaining formulae.

    I put this formula in cell B9:

    =IF($A9="","",INDEX(Sheet1!H:H,$A9))

    If the fields that you wanted to display were in sequence, then you could just copy this across, but as it is you need to change the index range as follows:

    C9: =IF($A9="","",INDEX(Sheet1!F:F,$A9))
    D9: =IF($A9="","",INDEX(Sheet1!G:G,$A9))
    E9: =IF($A9="","",INDEX(Sheet1!K:K,$A9))

    in order to bring the data from the appropriate column. Finally, these formulae can be copied down as far as you need them.

    Now all you need to do is to enter a new student ID into cell C2 and the display will automatically change.

    Hope this helps.

    Pete
    Attached Files Attached Files

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    Incidentally, if you want to see your previous posts, click on User CP near the top of the screen, and then on View all subscribed posts. You can click on any of the listed titles to see your thread - your earliest thread was in May 2015.

    Hope this helps.

    Pete

  14. #14
    Registered User
    Join Date
    05-12-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    25

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    Quote Originally Posted by Glenn Kennedy View Post
    rr23724. You should wind your neck in a bit and stop being so free and easy with your complaints. We are here to help, but neither want - nor need - your disrespect.
    Im sorry you feel like I am the one being disrespectful. Go back up and read post #5 and tell me if you don't think that would be read as insulting to someone who is not an expert. I felt as if I was being called an idiot and responded based on that. I was then lectured in post #9 about life, which I do not need.


    Also...Pete_UK - I did that with the posts and did not see the thread I was talking about. I may have used a different user name before and forgot the login. Thank you though!

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    Well, both Pete and I thought you were a bit over the top in your reaction...

    Anyhow. Put that aside. How do the offered solutions look?

  16. #16
    Registered User
    Join Date
    05-12-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    25

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    Both work just fine...Im curious on Pete's solution why the sequence formula? What advantage does it give? I don't understand that one.

    On yours...I can reason that one out for the most part based on formula I have used in the past. AGGREGATE is a new one for me as well as the "1+ROWS" part of the LOOKUP formula, I'm going to have to look those up and mess with them to try to understand them better.

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    I was a "late converter" to Excel 2010, when AGGREGATE was introduced. It's really powerful.... Others here showed me how useful it could be.

    The 1+ROWS(C$4:C4) is simply a counter. It resolves to 2, 3 and 4 as you drag it down. It tells Excel to return the value from the 2nd, 3rd and 4th column.

    Any further Qs, just shout. I'll let Pete answer for himself. Otherwise,

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  18. #18
    Registered User
    Join Date
    05-12-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    25

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    Quote Originally Posted by Glenn Kennedy View Post
    I was a "late converter" to Excel 2010, when AGGREGATE was introduced. It's really powerful.... Others here showed me how useful it could be.

    The 1+ROWS(C$4:C4) is simply a counter. It resolves to 2, 3 and 4 as you drag it down. It tells Excel to return the value from the 2nd, 3rd and 4th column.

    Any further Qs, just shout. I'll let Pete answer for himself. Otherwise,

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Thank you!

  19. #19
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    I don't have the AGGREGATE function in my version of XL, as it was only introduced in XL2010, so I have to use other ways around it. The purpose of the helper column in Sheet1 is to uniquely identify each record which belongs to the selected student. The simplest way to do this is to allocate a sequential number to each record, which acts as a unique ID and can easily be detected on the other sheet by means of the ROWS($1:1) term - when this is copied down it becomes ROWS($1:2), then ROWS($1:3) and so on in successive rows, which evaluates to 1, 2, 3 etc., i.e. the same sequence that occurs on the other sheet.

    Notice that in my VLOOKUP formula I started with the term ROWS($1:2) (which returns 2), and so this will return 2, 3, 4 on successive rows (i.e. get the data from the 2nd, then the 3rd, then the 4th column of the table), whereas Glenn used effectively 1+ROWS($4:4), which generates the same sequence when copied down as ROWS($4:4) returns 1 (there is one row between row $4 and row 4), but the second row number increments when it is copied down.

    In using helper columns in my solution (i.e. column L of Sheet1 and column A of Sheet2), the individual formulae used are much simpler, and so the solution should be easier to maintain and modify if required in the future. For example, suppose a future requirement would be to be able to select a student (as you have now) but only within a range of dates that you specify - with my solution you would only need to change the formula in L2 of Sheet1 and copy it down, and the rest will still be valid.

    Hope this helps.

    Pete

  20. #20
    Registered User
    Join Date
    05-12-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    25

    Re: Need to pull all instances of a name from 1 sheet to a summary sheet in the same workb

    That makes a little bit of sense, I will have to keep messing with it just to figure it out...thank you!

+ 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. Pull in names to a summary sheet
    By scruz9 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-30-2018, 11:21 AM
  2. Replies: 6
    Last Post: 05-25-2018, 03:31 PM
  3. Replies: 0
    Last Post: 10-21-2015, 03:03 PM
  4. Replies: 0
    Last Post: 02-05-2014, 05:18 PM
  5. [SOLVED] Pull out information from main sheet to summary sheet
    By abhineet.sabharwal in forum Excel General
    Replies: 3
    Last Post: 03-02-2013, 01:46 PM
  6. Pull Data from Various Worksheets into a Summary Sheet
    By shanipk82 in forum Excel General
    Replies: 1
    Last Post: 04-14-2011, 12:36 PM
  7. Pull Date from Log to Summary Sheet
    By Jogier505 in forum Excel General
    Replies: 2
    Last Post: 05-12-2010, 01:52 PM

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