How to keep certain fields the same

zaahideen · #1 09-03-2023, 08:45 AM

I have a mailmerge document where i am taking data from an excel sheet.
the data is exam results for students from different classes for different subjects.

i have attached an image which shows sample data

so the in mailmerge file, the subjects and class fileds should correspond and remain the same, however the student name and score should change accordingly.

then the subjects for the next class should correspond etc.

is this possible?

Guessed · #2 09-03-2023, 04:21 PM

There may be some new analysis features in Excel but I would have thought you need to flatten the data into very tall table to be able to do all the clever Pivot Tables and Mail Merge functionality.
The structure I would be aiming for is:

Code:

Name     | Class   | Subject     | Mark
Joe      | Class 1 | Physics     | 45
Peter    | Class 1 | Physics     | 42
Joe      | Class 1 | Maths       | 48
Peter    | Class 1 | Maths       | 41
Jane     | Class 2 | Physics     | 45
Sally    | Class 3 | Chemistry   | 42
Jane     | Class 2 | RE          | 48
Sally    | Class 3 | Maths       | 41

Once you have restructured along these lines, the sorting, collating and mail merges become simple.

zaahideen · #3 09-04-2023, 12:18 AM

Thanks
So then, if using the method above that you've explained, how would I then have all of Sally's results, subjects etc on one mail merge result?

Guessed · #4 09-04-2023, 03:03 PM

In Excel you would add filter buttons at the top by making it a table (aka ListObject) and filter by name.

To set up a mail merge where each person gets a list of their results using this source info, you would need to set up a more complicated merge which is known as a 'Many to One' merge. This tool by Graham Mayor should provide assistance for doing that - Merge Many To One

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Convert an excel file with many fields into one with a few specific fields	Sportsmen	Excel	12	11-19-2022 02:34 PM
How can I set up tab to go back and forth between legacy fields and content control fields in a Word	Ikajed	Word	1	10-13-2017 06:06 PM
Displaying FORMTEXT fields when merge fields are empty or null	sfkHooper	Mail Merge	6	01-19-2016 04:24 PM
Macro to keep formatted form fields after mail merge or replace text with formatted form fields	jer85	Word VBA	2	04-05-2015 10:00 PM
Word 2010 - DOCX File with fields -> PDF with fields	senglory	Word	2	03-27-2012 10:10 AM