+ Reply to Thread
Results 1 to 12 of 12

Help pivot table? Merge? Mail Merge?

  1. #1
    Registered User
    Join Date
    05-23-2019
    Location
    Denver
    MS-Off Ver
    Excel 2016
    Posts
    5

    Help pivot table? Merge? Mail Merge?

    Hello Everyone,

    I'm new and I need your help! I have an excel file with a list of students and classes they are taking; however, each class lists the student up to however many number they are taking. There are hundreds of students so I don't want to do it manually.

    Example of the problem

    Name ID CLASS
    John Smith 1234567889 College Algebra
    John Smith 1234567889 College English
    John Smith 1234567889 College Business

    what I need

    Name ID CLASS 1 CLASS 2 CLASS 3
    John Smith 1234567889 College Algebra College English College Business


    In order for me to create a mail merge for word document to create a summary of what a student has taken, I will need all the classes in the same row and create new columns. I assume that it can be done, but i've wasted my time on youtube trying to figure it out and it has not been fruitful.

    If you can help, i would be eternally grateful.
    Attached Files Attached Files
    Last edited by egarza123; 05-24-2019 at 04:54 PM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Help pivot table? Merge? Mail Merge?

    The data appears to be perfectly suited for what you need to do.

    However, since students can take a variable number of classes, you probably want data of the sort:
    Name | Id | Course 1, Course 2, Course 3.

    This can be accomplished with a pivot table and some VB code,

    However, it would go better with a sample workbook with some sample data. Dummy up the names and/or the student IDs. A couple dozen records should be enough to prove the concept.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    05-23-2019
    Location
    Denver
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Help pivot table? Merge? Mail Merge?

    Thanks Dflak.

    i'm actually a novice/noob at excel and spreadsheets for that matter. Is there a youtube link that you know that could walk me through the process? I feel I am way over my head on this.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Help pivot table? Merge? Mail Merge?

    Give me a sample file, and I'll explain every step of the process. With something concrete to work with, it will be easier to explain. Also, I'll design it to be "plug and play:" you copy and paste the data in and click on a button.

    The part that may be over your head will be the VB coding. If you have no experience at all with it, it will be like trying to translate from a foreign language. However I will do my best to leave more than copious comments in the code as well as in the posted description.

  5. #5
    Registered User
    Join Date
    05-23-2019
    Location
    Denver
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Help pivot table? Merge? Mail Merge?

    I attached a sample to the original message. I could tell when i started youtubing BV code that it was not going to be for beginners. Thanks!

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Help pivot table? Merge? Mail Merge?

    First off, you'll need to fix your birthdays. The same person should have the same birthday.
    Once you do this, here's one way to get your result by using Power Query:

    1) Highlight your data > Data > From Table
    2) Group By > Advanced > Group by Name, Student ID, and Birthday > New column name: Temp > Operation: All Rows > OK
    3) Add Column > Custom Column > formula =Table.Column([Temp],"Class") > OK
    4) Click on the box to the right of the Custom header then select Extract Values > Comma > OK
    5) Select the Temp column then Home > Remove Columns > Close & Load

    See attached.
    Attached Files Attached Files

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Help pivot table? Merge? Mail Merge?

    Also, you can change the name of the custom column if you wanted to while in step #3 above.

    The cool thing about Power Query is that once you have everything set up, all you have to do is add new data at the bottom of your blue table and then refresh the green table.

  8. #8
    Registered User
    Join Date
    05-23-2019
    Location
    Denver
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Help pivot table? Merge? Mail Merge?

    Thank you for catching the birth dates! I will try the power query and follow the instructions you have given.

  9. #9
    Registered User
    Join Date
    05-23-2019
    Location
    Denver
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Help pivot table? Merge? Mail Merge?

    On step 4, the column shows error. Is is supposed to be like that because i cant find the extract values on the box anywhere. [i had the wrong name on the column] it worked! Thanks! ; however, with power query can it add the classes in a different column? Because i'm doing a mail merge in word, I will need to list the classes on different rows.
    Last edited by egarza123; 05-24-2019 at 07:52 PM.

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Help pivot table? Merge? Mail Merge?

    Absolutely. You can do this step at the end of the steps from post #6 but since we already have the power query created, I'll show you how to edit it to add more steps.

    Click anywhere on the green result table on Sheet2 > Query > Edit > Select the last column with the classes in it > Split Column > By Delimiter > Comma > Each occurrence > OK > Close & Load

    This will put each class in it's own column instead of having them all in one cell separated by commas.

  11. #11
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Help pivot table? Merge? Mail Merge?

    You can use Word's Catalogue/Directory Mailmerge facility for this (the terminology depends on the Word version). To see how to do so with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
    http://www.msofficeforums.com/mail-m...-tutorial.html
    or:
    http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
    The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

    Depending on what you're trying to achieve, the field coding for this can be complex. However, since the tutorial document includes working field codes for all of its examples, most of the hard work has already been done for you - you should be able to do little more than copy/paste the relevant field codes into your own mailmerge main document, substitute/insert your own field names and adjust the formatting to get the results you desire. For some worked examples, see the attachments to the posts at:
    http://www.msofficeforums.com/mail-m...html#post23345
    http://www.msofficeforums.com/mail-m...html#post30327

    Another option would be to use a DATABASE field in a normal ‘letter’ mailmerge main document and a macro to drive the process. An outline of this approach can be found at: http://answers.microsoft.com/en-us/o...1-1996c14dca5d
    Conversely, if you're using a relational database or, Excel workbook with a separate table with just a single instance of each of the grouping criteria, a DATABASE field in a normal ‘letter’ mailmerge main document could be used without the need for a macro. An outline of this approach can be found at:
    https://answers.microsoft.com/en-us/...f-8642e46fa103
    For some working examples, see:
    http://www.msofficeforums.com/mail-m...-multiple.html
    https://www.excelforum.com/excel-gen...ml#post5110813

    Alternatively, you may want to try one of the Many-to-One Mail Merge add-ins, from:
    Graham Mayor at http://www.gmayor.com/ManyToOne.htm; or
    Doug Robbins at https://onedrive.live.com/?cid=5AEDC...43615E886B!566
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  12. #12
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Help pivot table? Merge? Mail Merge?

    I thought I had posted this, but here is a "push button" solution using VBA.
    Attached Files Attached Files

+ 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. Replies: 5
    Last Post: 11-15-2018, 10:54 PM
  2. Replies: 0
    Last Post: 06-24-2015, 02:34 AM
  3. Replies: 0
    Last Post: 01-05-2015, 07:35 AM
  4. Replies: 2
    Last Post: 07-12-2012, 08:11 PM
  5. Mail Merge from Pivot Table
    By scrubdubbins in forum Excel General
    Replies: 0
    Last Post: 03-31-2011, 10:06 AM
  6. Pivot Table / Mail Merge
    By nmicon in forum Access Tables & Databases
    Replies: 1
    Last Post: 03-25-2009, 12:19 AM
  7. Replies: 0
    Last Post: 04-21-2006, 03:40 PM

Tags for this Thread

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