Mail Merge Multiple Rows into one based on multiple columns

[cochranb13]

I am trying to create a mail merge that I can send to staff weekly that gives them individual data along with ONLY names of students that are failing. I have searched and found how to combine rows using one condition and a compare column, but I do not understand the syntax well enough to know how to include a second condition.



I have included screenshots below of data sheet, what I am getting when I run merge, what I want when I run merge, and what I have entered in merge.

 

[amiroo]

Hi,

I have a suggestion for you

create a column in your DB and use this formula in it:

=TEXTJOIN(CHAR(10),TRUE,IF(LEFT([Score])="f",[student full],""))

use this formula as an array formula

 

[Macropod]

It's not apparent to me where you're getting the data for «A», «B», «C», etc. from as nothing in your Excel screenshot is capable of generating that for a mailmerge.

As for the grouping, you can use Word's Catalogue/Directory Mailmerge facility (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:

Microsoft Word Catalogue/Directory Mailmerge Tutorial

[Solved] Microsoft Word Catalogue/Directory Mailmerge Tutorial Mail Merge

www.msofficeforums.com

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:

mail merge with duplicate names but different dollar amounts

mail merge with duplicate names but different dollar amounts Mail Merge

www.msofficeforums.com

Access to Word, Creating a list from multiple records

[Solved] Access to Word, Creating a list from multiple records Mail Merge

www.msofficeforums.com

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: Many to one email merge using tables
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:

Mail Merge - To a Word Table on a Single Page

I am trying to Mail Merge multiple records using data extracted from an Excel 2010 Spreadsheet into the body of a table in Word 2010. I want the records to "list" in the Word table on a single page,

answers.microsoft.com

For some working examples, see:

Mail Merge: Using One Excel File with Multiple Sheets

[Solved] Mail Merge: Using One Excel File with Multiple Sheets Mail Merge

www.msofficeforums.com

Merge excel list into Word Receipt

I have an excel spreadsheet that I use for our annual charity auction. The excel spreadsheet has two sheets. One sheet for the guests with the their corresponding paddle numbers. The second sheet is the list of items for sale. I manually enter the paddle number, guest name, and final price next...

www.excelforum.com

The DATABASE field can even be used without recourse to a mailmerge. An example of such usage can be found at: Mail merge into different coloumns

Alternatively, you may want to try one of the Many-to-One Mail Merge add-ins, from:
Graham Mayor at Merge Many To One ; or
Doug Robbins at OneDrive

 

[cochranb13]

It's not apparent to me where you're getting the data for «A», «B», «C», etc. from as nothing in your Excel screenshot is capable of generating that for a mailmerge.

As for the grouping, you can use Word's Catalogue/Directory Mailmerge facility (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:

Microsoft Word Catalogue/Directory Mailmerge Tutorial

[Solved] Microsoft Word Catalogue/Directory Mailmerge Tutorial Mail Merge

www.msofficeforums.com

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:

mail merge with duplicate names but different dollar amounts

mail merge with duplicate names but different dollar amounts Mail Merge

www.msofficeforums.com

Access to Word, Creating a list from multiple records

[Solved] Access to Word, Creating a list from multiple records Mail Merge

www.msofficeforums.com

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: Many to one email merge using tables
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:

Mail Merge - To a Word Table on a Single Page

I am trying to Mail Merge multiple records using data extracted from an Excel 2010 Spreadsheet into the body of a table in Word 2010. I want the records to "list" in the Word table on a single page,

answers.microsoft.com

For some working examples, see:

Mail Merge: Using One Excel File with Multiple Sheets

[Solved] Mail Merge: Using One Excel File with Multiple Sheets Mail Merge

www.msofficeforums.com

Merge excel list into Word Receipt

I have an excel spreadsheet that I use for our annual charity auction. The excel spreadsheet has two sheets. One sheet for the guests with the their corresponding paddle numbers. The second sheet is the list of items for sale. I manually enter the paddle number, guest name, and final price next...

www.excelforum.com

The DATABASE field can even be used without recourse to a mailmerge. An example of such usage can be found at: Mail merge into different coloumns

Alternatively, you may want to try one of the Many-to-One Mail Merge add-ins, from:
Graham Mayor at Merge Many To One ; or
Doug Robbins at OneDrive

The data for the As, Bs, Cs, etc is coming from columns not included in screenshot.

 

[cochranb13]

Hi,

I have a suggestion for you

create a column in your DB and use this formula in it:

=TEXTJOIN(CHAR(10),TRUE,IF(LEFT([Score])="f",[student full],""))

use this formula as an array formula

Ok. Being unfamiliar with the advanced Excel and Mail Merge things, what would I do with this new column?

 

[amiroo]

Ok. Being unfamiliar with the advanced Excel and Mail Merge things, what would I do with this new column?

You just need to create a new column and copy this formula in it. but after copy it, instead of press Enter key you should press ctrl+shift+enter keys.
and in Word just add this column in your paper.

 

[Macropod]

Cross-posted at: Mail Merge Combining Rows with multiple conditions
Please read Mr Excel's policy on Cross-Posting in rule 13: Message Board Rules

 

[cochranb13]

You just need to create a new column and copy this formula in it. but after copy it, instead of press Enter key you should press ctrl+shift+enter keys.
and in Word just add this column in your paper.

I added Column and made Array Formula and it copied only Failing Student Names over; however, when I run mail merge I still either get all students for a teacher or no students. I cannot get the merge to pull just the failing student names.

 

[amiroo]

Would you please share a sample of your files?

 

[cochranb13]

Here is Excel data

Merge Help.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	State ID	Student Number	Last Name	First Name	Student Full	Birthdate	Cohort Year NGA	Grade	Course Number	Section Number	Period Start	Course Name	Score	Percent	Task	Term Start	Teacher Full Name	Compare	Merge Help	Teacherr individual	Blank Grades	Posted	Posting %	Email	A	B	C	D	F	Pass Rate
2	123456789	123456	Last1	First1	First1 Last1	1/1/02	2020	12	1234	312	3	class 1	A-	92.12	Progress/Eligibility	S2	Teacher 1	Teacher 1		Teacher 1	0	18	100%	teacher1@email.com	3	5	5	2	3	83.3%
3	123456790	123457	Last2	First2	First2 Last2	1/1/02	2020	13	1234	312	3	class 1	C+	79.42	Progress/Eligibility	S2	Teacher 1	Teacher 1		Teacher 2	0	18	100%	teacher2@email.com	4	5	3	2	4	77.8%
4	123456791	123458	Last3	First3	First3 Last3	1/1/02	2020	14	1234	312	3	class 1	B+	86.5	Progress/Eligibility	S2	Teacher 1	Teacher 1		Teacher 3	0	18	100%	teacher3@email.com	7	7	1	2	1	94.4%
5	123456792	123459	Last4	First4	First4 Last4	1/1/02	2020	15	1234	312	3	class 1	C	75.97	Progress/Eligibility	S2	Teacher 1	Teacher 1		Teacher 4	0	16	100%	teacher4@email.com	5	3	3	1	4	75.0%
6	123456793	123460	Last5	First5	First5 Last5	1/1/02	2020	16	1234	312	3	class 1	B	85.39	Progress/Eligibility	S2	Teacher 1	Teacher 1
7	123456794	123461	Last6	First6	First6 Last6	1/1/02	2020	17	1234	312	3	class 1	F	65.66	Progress/Eligibility	S2	Teacher 1	Teacher 1	First6 Last6
8	123456795	123462	Last7	First7	First7 Last7	1/1/02	2020	18	1234	312	3	class 1	C-	71.38	Progress/Eligibility	S2	Teacher 1	Teacher 1
9	123456796	123463	Last8	First8	First8 Last8	1/1/02	2020	19	1234	312	3	class 1	A	95.09	Progress/Eligibility	S2	Teacher 1	Teacher 1
10	123456797	123464	Last9	First9	First9 Last9	1/1/02	2020	20	1234	312	3	class 1	C-	69.6	Progress/Eligibility	S2	Teacher 1	Teacher 1
11	123456798	123465	Last10	First10	First10 Last10	1/1/02	2020	21	1234	312	3	class 1	F	83.95	Progress/Eligibility	S2	Teacher 1	Teacher 1	First10 Last10
12	123456799	123466	Last11	First11	First11 Last11	1/1/02	2020	22	1234	312	3	class 1	D	66.54	Progress/Eligibility	S2	Teacher 1	Teacher 1
13	123456800	123467	Last12	First12	First12 Last12	1/1/02	2020	23	1234	312	3	class 1	B-	81.32	Progress/Eligibility	S2	Teacher 1	Teacher 1
14	123456801	123468	Last13	First13	First13 Last13	1/1/02	2020	24	1234	312	3	class 1	C-	72.23	Progress/Eligibility	S2	Teacher 1	Teacher 1
15	123456802	123469	Last14	First14	First14 Last14	1/1/02	2020	25	1234	312	3	class 1	B	85.15	Progress/Eligibility	S2	Teacher 1	Teacher 1
16	123456803	123470	Last15	First15	First15 Last15	1/1/02	2020	26	1234	312	3	class 1	F	89.47	Progress/Eligibility	S2	Teacher 1	Teacher 1	First15 Last15
17	123456804	123471	Last16	First16	First16 Last16	1/1/02	2020	27	1234	312	3	class 1	D	68.83	Progress/Eligibility	S2	Teacher 1	Teacher 1
18	123456805	123472	Last17	First17	First17 Last17	1/1/02	2020	28	1234	312	3	class 1	A	92.96	Progress/Eligibility	S2	Teacher 1	Teacher 1
19	123456806	123473	Last18	First18	First18 Last18	1/1/02	2020	29	1234	312	3	class 1	B-	81.9	Progress/Eligibility	S2	Teacher 1	Teacher 1
20	123456789	123456	Last1	First1	First1 Last1	1/1/02	2020	12	1234	712	7	class 2	D	101	Progress/Eligibility	S3	Teacher 2	Teacher 2
21	123456790	123457	Last2	First2	First2 Last2	1/1/02	2020	13	1234	712	7	class 3	F	102	Progress/Eligibility	S4	Teacher 2	Teacher 2	First2 Last2
22	123456791	123458	Last3	First3	First3 Last3	1/1/02	2020	14	1234	712	7	class 4	B+	103	Progress/Eligibility	S5	Teacher 2	Teacher 2
23	123456792	123459	Last4	First4	First4 Last4	1/1/02	2020	15	1234	712	7	class 5	C-	104	Progress/Eligibility	S6	Teacher 2	Teacher 2
24	123456793	123460	Last5	First5	First5 Last5	1/1/02	2020	16	1234	712	7	class 6	A	105	Progress/Eligibility	S7	Teacher 2	Teacher 2
25	123456794	123461	Last6	First6	First6 Last6	1/1/02	2020	17	1234	712	7	class 7	B+	106	Progress/Eligibility	S8	Teacher 2	Teacher 2
26	123456795	123462	Last7	First7	First7 Last7	1/1/02	2020	18	1234	712	7	class 8	A	107	Progress/Eligibility	S9	Teacher 2	Teacher 2
27	123456796	123463	Last8	First8	First8 Last8	1/1/02	2020	19	1234	712	7	class 9	F	108	Progress/Eligibility	S10	Teacher 2	Teacher 2	First8 Last8
28	123456797	123464	Last9	First9	First9 Last9	1/1/02	2020	20	1234	712	7	class 10	F	109	Progress/Eligibility	S11	Teacher 2	Teacher 2	First9 Last9
29	123456798	123465	Last10	First10	First10 Last10	1/1/02	2020	21	1234	712	7	class 11	A-	110	Progress/Eligibility	S12	Teacher 2	Teacher 2
30	123456799	123466	Last11	First11	First11 Last11	1/1/02	2020	22	1234	712	7	class 12	A-	111	Progress/Eligibility	S13	Teacher 2	Teacher 2
31	123456800	123467	Last12	First12	First12 Last12	1/1/02	2020	23	1234	712	7	class 13	F	112	Progress/Eligibility	S14	Teacher 2	Teacher 2	First12 Last12
32	123456801	123468	Last13	First13	First13 Last13	1/1/02	2020	24	1234	712	7	class 14	B	113	Progress/Eligibility	S15	Teacher 2	Teacher 2
33	123456802	123469	Last14	First14	First14 Last14	1/1/02	2020	25	1234	712	7	class 15	C	114	Progress/Eligibility	S16	Teacher 2	Teacher 2
34	123456803	123470	Last15	First15	First15 Last15	1/1/02	2020	26	1234	712	7	class 16	B+	115	Progress/Eligibility	S17	Teacher 2	Teacher 2
35	123456804	123471	Last16	First16	First16 Last16	1/1/02	2020	27	1234	712	7	class 17	C+	116	Progress/Eligibility	S18	Teacher 2	Teacher 2
36	123456805	123472	Last17	First17	First17 Last17	1/1/02	2020	28	1234	712	7	class 18	B+	117	Progress/Eligibility	S19	Teacher 2	Teacher 2
37	123456806	123473	Last18	First18	First18 Last18	1/1/02	2020	29	1234	712	7	class 19	D	118	Progress/Eligibility	S20	Teacher 2	Teacher 2
38	123456789	123456	Last1	First1	First1 Last1	1/1/02	2020	12	1234	712	7	class 27	A	126	Progress/Eligibility	S28	Teacher 3	Teacher 3
39	123456790	123457	Last2	First2	First2 Last2	1/1/02	2020	13	1234	712	7	class 28	A	127	Progress/Eligibility	S29	Teacher 3	Teacher 3
40	123456791	123458	Last3	First3	First3 Last3	1/1/02	2020	14	1234	712	7	class 29	A	128	Progress/Eligibility	S30	Teacher 3	Teacher 3
41	123456792	123459	Last4	First4	First4 Last4	1/1/02	2020	15	1234	712	7	class 30	B+	129	Progress/Eligibility	S31	Teacher 3	Teacher 3
42	123456793	123460	Last5	First5	First5 Last5	1/1/02	2020	16	1234	712	7	class 31	B	130	Progress/Eligibility	S32	Teacher 3	Teacher 3
43	123456794	123461	Last6	First6	First6 Last6	1/1/02	2020	17	1234	712	7	class 32	B+	131	Progress/Eligibility	S33	Teacher 3	Teacher 3
44	123456795	123462	Last7	First7	First7 Last7	1/1/02	2020	18	1234	712	7	class 33	B-	132	Progress/Eligibility	S34	Teacher 3	Teacher 3
45	123456796	123463	Last8	First8	First8 Last8	1/1/02	2020	19	1234	712	7	class 34	A	133	Progress/Eligibility	S35	Teacher 3	Teacher 3
46	123456797	123464	Last9	First9	First9 Last9	1/1/02	2020	20	1234	712	7	class 35	B+	134	Progress/Eligibility	S36	Teacher 3	Teacher 3
47	123456798	123465	Last10	First10	First10 Last10	1/1/02	2020	21	1234	712	7	class 36	D	135	Progress/Eligibility	S37	Teacher 3	Teacher 3
48	123456799	123466	Last11	First11	First11 Last11	1/1/02	2020	22	1234	712	7	class 37	F	136	Progress/Eligibility	S38	Teacher 3	Teacher 3	First11 Last11
49	123456800	123467	Last12	First12	First12 Last12	1/1/02	2020	23	1234	712	7	class 38	D	137	Progress/Eligibility	S39	Teacher 3	Teacher 3
50	123456801	123468	Last13	First13	First13 Last13	1/1/02	2020	24	1234	712	7	class 39	A	138	Progress/Eligibility	S40	Teacher 3	Teacher 3
51	123456802	123469	Last14	First14	First14 Last14	1/1/02	2020	25	1234	712	7	class 40	B+	139	Progress/Eligibility	S41	Teacher 3	Teacher 3
52	123456803	123470	Last15	First15	First15 Last15	1/1/02	2020	26	1234	712	7	class 41	C-	140	Progress/Eligibility	S42	Teacher 3	Teacher 3
53	123456804	123471	Last16	First16	First16 Last16	1/1/02	2020	27	1234	712	7	class 42	A	141	Progress/Eligibility	S43	Teacher 3	Teacher 3
54	123456805	123472	Last17	First17	First17 Last17	1/1/02	2020	28	1234	712	7	class 43	B+	142	Progress/Eligibility	S44	Teacher 3	Teacher 3
55	123456789	123456	Last1	First1	First1 Last1	1/1/02	2020	12	1234	712	7	class 44	A	143	Progress/Eligibility	S45	Teacher 3	Teacher 3
56	123456790	123457	Last2	First2	First2 Last2	1/1/02	2020	13	1234	712	7	class 80	A	179	Progress/Eligibility	S81	Teacher 4	Teacher 4
57	123456791	123458	Last3	First3	First3 Last3	1/1/02	2020	14	1234	712	7	class 81	F	180	Progress/Eligibility	S82	Teacher 4	Teacher 4	First3 Last3
58	123456792	123459	Last4	First4	First4 Last4	1/1/02	2020	15	1234	712	7	class 82	F	181	Progress/Eligibility	S83	Teacher 4	Teacher 4	First4 Last4
59	123456793	123460	Last5	First5	First5 Last5	1/1/02	2020	16	1234	712	7	class 83	A-	182	Progress/Eligibility	S84	Teacher 4	Teacher 4
60	123456794	123461	Last6	First6	First6 Last6	1/1/02	2020	17	1234	712	7	class 84	A-	183	Progress/Eligibility	S85	Teacher 4	Teacher 4
61	123456795	123462	Last7	First7	First7 Last7	1/1/02	2020	18	1234	712	7	class 85	F	184	Progress/Eligibility	S86	Teacher 4	Teacher 4	First7 Last7
62	123456796	123463	Last8	First8	First8 Last8	1/1/02	2020	19	1234	712	7	class 86	B	185	Progress/Eligibility	S87	Teacher 4	Teacher 4
63	123456797	123464	Last9	First9	First9 Last9	1/1/02	2020	20	1234	712	7	class 87	C	186	Progress/Eligibility	S88	Teacher 4	Teacher 4
64	123456798	123465	Last10	First10	First10 Last10	1/1/02	2020	21	1234	712	7	class 88	B+	187	Progress/Eligibility	S89	Teacher 4	Teacher 4
65	123456799	123466	Last11	First11	First11 Last11	1/1/02	2020	22	1234	712	7	class 89	C+	188	Progress/Eligibility	S90	Teacher 4	Teacher 4
66	123456800	123467	Last12	First12	First12 Last12	1/1/02	2020	23	1234	712	7	class 90	B+	189	Progress/Eligibility	S91	Teacher 4	Teacher 4
67	123456801	123468	Last13	First13	First13 Last13	1/1/02	2020	24	1234	712	7	class 91	D	190	Progress/Eligibility	S92	Teacher 4	Teacher 4
68	123456802	123469	Last14	First14	First14 Last14	1/1/02	2020	25	1234	712	7	class 92	A-	191	Progress/Eligibility	S93	Teacher 4	Teacher 4
69	123456803	123470	Last15	First15	First15 Last15	1/1/02	2020	26	1234	712	7	class 93	F	192	Progress/Eligibility	S94	Teacher 4	Teacher 4	First15 Last15
70	123456804	123471	Last16	First16	First16 Last16	1/1/02	2020	27	1234	712	7	class 94	A-	193	Progress/Eligibility	S95	Teacher 4	Teacher 4
71	123456805	123472	Last17	First17	First17 Last17	1/1/02	2020	28	1234	712	7	class 95	C	194	Progress/Eligibility	S96	Teacher 4	Teacher 4
Data

Cell Formulas
Range	Formula
R2:R71	R2	=Q2
S2:S71	S2	=TEXTJOIN(CHAR(10),TRUE,IF(LEFT(M2)="f",E2,""))
U2:U5	U2	=COUNTIFS($Q:$Q,$T2,$M:$M,"")
V2:V5	V2	=COUNTIFS($Q:$Q,$T2,$M:$M,"*")
W2:W5	W2	=V2/(U2+V2)
Y2:Y5	Y2	=COUNTIFS($Q:$Q,$T2,$M:$M,"A*")
Z2:Z5	Z2	=COUNTIFS($Q:$Q,$T2,$M:$M,"B*")
AA2:AA5	AA2	=COUNTIFS($Q:$Q,$T2,$M:$M,"C*")
AB2:AB5	AB2	=COUNTIFS($Q:$Q,$T2,$M:$M,"D*")
AC2:AC5	AC2	=COUNTIFS($Q:$Q,$T2,$M:$M,"F")
AD2:AD5	AD2	=IF(W2=0,"No Grades Posted",(1-(AC2/((SUM(U2:V2))))))
E2:E71	E2	=CONCATENATE(D2," ",C2)

I need merge to Generate separate emails to Teacher 1, 2, 3, and 4 that tells them their individual grade data and lists only student failing their course. Students with A, B, C, or D should not be listed in email.