Mail Merge By Grouping, Table Function to Insert Total of Column Value Multiple Records on Same Page
ฝัง
- เผยแพร่เมื่อ 29 ก.ย. 2024
- If you're looking to quickly and easily insert total of column value multiple records on the same page using Mail Merge, then this video is for you. In this tutorial, I'll be walking you through the steps to take in order to use the Table Function to quickly and easily insert a total of column value multiple records on the same page.
I'll cover the basics of Mail Merge and how to use the Table Function to make it so that multiple records can be inserted on the same page.
You'll be learning how to use the Table Function to complete the Mail Merge and make it easier to insert a total of column value multiple records on the same page. I'll be giving step-by-step instructions that you can follow along with and I'll be providing visual aids to help you understand the process. I'll also be discussing how the Table Function can be used in other applications.
This video will take you through the process of setting up the Mail Merge and how to use the Table Function to insert a total of column value multiple records on the same page. I'll be providing tips and tricks to make the process easier and more efficient. I'll also be discussing how to troubleshoot any issues that you might encounter.
By the end of this video, you'll be able to use the Table Function to insert a total of column value multiple records on the same page using Mail Merge. So, if you're interested in learning how to use the Table Function to quickly and easily insert a total of column value multiple records on the same page using Mail Merge, then this video is for you.
If you are new to my channel, please subscribe and press the bell icon too.
Thank you for watching!
🅻🅸🅺🅴----🆂🅷🅰🆁🅴-----🅲🅾🅼🅼🅴🅽🆃------🆂🆄🅱🆂🅲🆁🅸🅱🅴
Featured playlist.
• MailMerge
Visit my other Videos too:
Power BI Full Tutorial 2023:
• Power BI Full Tutorial...
Power Query:
• Power Query
3D formula:
• 3 D Formulas
Alt-Enter:
• Alt Enter
Aggregate and Subtotal:
• Aggregate and Subtotal
Logic Function:
• Logical Functions
Excel Worksheet links:
• Exce WorkSheets links
Mail Merge:
• MailMerge
Dilemma:
• Dilemma
Excel tips and tricks:
• Excel Short Tips and T...
Questions on Categorical Syllogism:
• Questions on Categoric...
Information Systems Basic:
• Information System Basics
This Video address one of the common task at workplace. It is using Mail Merge feature of MS Word with Excel file in such a way that Mail Merge generates pages by grouping, means multiple records on same page based on a particular field value and it also shows how column values be added at run time.
Change IF Function shown in the video with the the following, that will take care of single line record
=IF(B2=B3,1,IF(COUNTIF($B$2:$B$14,B2)=1,2,0))
#MailMerge
#MSWord
#MultipleRecordOnSamePage
Thank you so much, I saw so many convoluted and confusing videos before I found this, and now I understand what I need to do ... at last!!!!
Thank you very much for this video, I've been looking for this solution for months, and this helps me a lot.
This was a lifesaver! I was able to get it to work perfectly the first time. The second time, it was showing multiple documents for one any person with multiple lines of data when previewed, but when I finished the merge, it was exactly as it should be, with one document for each person, containing all of their data. Thank you!!!
Great to hear!
Great video, it is going to save me hours of work.
Thank you sir for helping us more power to you and do share more videos from basic level to advance bcz we want to learn it💕
Can you kindly explain the Bookmark, (you have used SET ID1) I am getting an Error! Bookmark not defined
I'm having an issue where when I run my merge the first one is correct but before it moves to next customer it displays the same customer but with one record missing and so on until there are none left then it moves onto next customer. Do you know what's wrong in this case?
I replicated the steps and found the same issue as other people commented :
a) 1 customer with multiple rows (2 lines) will produce 3 separate documents after "finish&merge" (edit individual documents).
Did you find a solution for this?
Can anyone help to explain bookmark " SET ID1"? How can i find name of this bookmark? I try to input this but it failed😢
And what to do If number of rows belonging to category that we want to display is unknown (vary from 1 to 100)
Hello, we followed your steps but our data has different amount of invoices, one customer have 4 while others can have up to 18, what should we do to leave the remaining rows blank
Did you solve your issue? I am having the same problem
AOA, How are you getting {MERGEFIELD}? Iam getting when i select Insert Merge Field and then select my field name.
Thanks
Press ctl f9
Sir , what if family records. father and his 8 or 10 members
THANK YOU SO MUCH! This solved my problem. Note to people having issues with Word creating multiple recipients and just removing a row each time... The error shows up in the preview but not in the final merge
Can someone assist me with this, please? I have tried it on the data that was exactly represented it he table but when I tried it in my data, it didn't work!! Does anyone have any idea what could be wrong?
Tell me where exactly it is not working for you..I will guide if possible
Hi, how to determine the number of rows as in my case it is varying for each and every recipient and also how to remove the blank rows ? let me know.please
Great video, there are alot of other videos making it complicated trying to use directories and what not. Question - would it be possible so the number of rows in the table itself dynamically updates with the number of invoices? So if Rick only has 2 invoices, the table he gets would only have two rows (and doesn't have the extra blank ones which you needed to include for the people that have 3).
Need answer for this please
search something that says dynamic table or resized table. I have seen it on TH-cam before, but it is a bit complicated. (involved VBA)
Thank you so much for the Video. I have added the { =Sum(Above)} .But it is not adding the numbers,it is displayed as it is in the mail merge document.What might be the issue
Great Sir, really a practical example reduces a lot of time and effort
Hell Sir,
I replicated the steps and found that it repeats the last line on the same page again before going on to the next customer. how to avoid this?
Thank you!!!
Why I filed code "SET" ID and insert filed on code the code is loss? It's not appear on table except condition "Next IF". I using MS office365.
Try Alt F9 it should show code. Press again to hide code
What if last row that VICK is deleted and so only one record is for VICK? In that case this formula will definitely fail though real world data might be so.
Great overview, thank you Khurram. Iteration in mail merge is very difficult. Can you clarify whether there is a way to remove unused rows for a particular ID? I have two source data tables that I am merging from Access, one Projects, the other Line Items. Each Project may have n Line Items. My question is how to handle the uneven nature of the rows - e.g., some Project records have only 1 line items while others may have more than 10. I'd like to show only the rows of data and not have empty rows. Appreciated!
Excellent vdo, every word is worthy and non useless
Hello Mr. Hkurram Khan,
I am not able to reproduce your tutorial. I am not able to correctly evaluate if the ID1 and ID2 are the same or not. The result of comparison is wrong (ID1 = ID2) even if it is not true. This way the word do not list next records (with test =1). -> I have changed the logic of if function -(> if ID1=ID2, insert merged fiield, "") and I am getting the next records (solved), but the next record if (test =0) does not work and is listing last record repeatadly. Please do you have any Idea, what am I doing wrong? thank you. Pavol
Sir, does it work for mail merge tool kit too?
I've been looking for this information for months. Thank you so much for making this easy!
Thanks for video, instruction is clear. I used it to merge over 1K docs. Some letters created had blank on some rows, others look good. What could be the problem?
Is there any way to avoid having to use a "helper"column? Surely there must be a way to check if the last record field and the field in the record after are different and proceed that way.
Finally!!!😁. A video that shows and clearly explains this type of process; and it actually works! Thank You!!!!
There is so much info on this topic online but I couldn't find a process that actually explains and shows it this clearly and works. I had actually given up on this process as an option. After all these years, you would think MS would have come up with this function as part of the standard Mail Merge. Thank you again.
I followed every step and when I Alt F9 to see the values of ID 1 and ID 2, it is blank. What am I doing wrong?
Only one issue, if one customer have 2 and another have 50 invoices then Excel table should be dynamic and not manual as you have done
Hello Sir -
Thank you for your help.
I merge does what I want, combining lines and a total on one page, however, it repeats the last line on the next page again before going on to the next customer.
I have the 1,0 text column set to =IF(K2=K3,1,0)
Here is the code in my table:
column 1 {IF ID1 = ID2 "" "{ MERGEFIELD Title }"}
column 2 {IF ID1 = ID2 "" "{ MERGEFIELD Fee }"}
column 3 { SET ID1 "{ MERGEFIELD Title }" }{ NEXTIF { MERGEFIELD Test } = 1 }{ SET ID2 "{MERGEFIELD Title}" }
The results show the first page with one customer and all the titles and fees listed. However, the second page is the same customer with the last title and fee repeated. Then the 3rd page goes on to the next customer. Single line customers work fine. Customers with multiple lines repeat only the last line on the next page.
I think this is similar to other questions here but I'm not sure a solution was posted yet.
Your help is greatly appreciated!
Hi Judy, I am facing the same issue, have you found a solution and would you share it with me? thanks
Hello,
I am also facing the same issue.Would like to know the solution . Could you please tell me
@@vaishnavisugumar9208 have you found solution?
Hey I did this and it works greak...However I'm having an issue in which the people with multiple items show up twice, one page with the correct information and another with only one record. Could you please help?
many thanks for this great video! instructions were super clear and easy to follow! my only issue is that for some reason the customer name gets reprinted on the following record.. for example, if a customer has 2 invoices number, the first page will be perfect - showing the 2 invoices and associated amount ; but the following page will be for the same customer and the second invoice.. any advise on how to fix it? i followed your video to the dot.. many thanks in advance
Thanks for watching,
You can see, we prepare data for Mail Merge.
What I understand, you refer the following scenario.
Two different customers with same names. In that case we assign some code to customers that uniquely identify the customer record and then group records based on this code and proceed as shown in video after grouping.
I hope that helps.
@@KhurramKhanExcelLogic I'm not sure that's what they mean. I have a similar problem.
My data set includes the person's name, their ID number, a course name, a course code, and a due date for each name. The first document for each name has all of the correct information. However, let's say John Doe has 4 courses to his name. After the 1st page, each subsequent page will included one less course name until it gets to only one course. Then a new page will start with the next name and so on and so forth.
If I group the records by name, what code would I use for that? Set ID1 and ID2 as the person's name? [Edit:] Tried that and it made a sheet for each course name.
@@dennisaikens1292 @khurramKhan
I am getting a similar issue. Has this been resolved? If I have 4 records for one person the first sheet works and shows 4 records. My second email (mail merge) will be for the same person and it will show 3 records and the following page will show 2 records for the same person. Is there a way to make it so you only see the first 4 records with duplicating the next sheets? Please help....
Hi, Have you got the solution for this.
@@bhishanwadhai6670 Same issue here
Sir, data in my table format needs to filled vertically (instead horizontally), can you pls help how can I do?
The rule is not working for me at the 13minute part of the video, it returns the same value for each ID. Any ideas?
Just came across this great tutorial and used it on my data table. However, I had a question. The Test column has a 1 or 0 based on the start and stop on the group. What happens if there is a SINGLE ENTRY? The TEST column basically inputs a 0.
It would be great if you could help me out on this.
did you find a solution for this? I have the same question
Thanks for your example. I almost got it to work. The only thing that doesnt work for me is that when i merge multiple rows in a table, the next "result" will be the same receipment with one less row in the table. Dont know what i'm doing wrong.
This is happening to me as well. What was the solution?
@@y12sr_dsm that happened to me when I preview the data, It worked as intended when I Finished the Merge.
Thank you thank you! This is exactly what I needed. Thank you for such a detailed explanation!
Keep creating new videos!! Thanks explained well without any distractions.
Hi sir... Can I share my file with you? And help me with the solution plsss 🙏🏻🙏🏻🙏🏻 the name repeats 😭
If you have followed same steps it should not be the case. Don't preview just finished Mail Merge, Names should not be repeated
Error bookmark not defined showing in last coulmn
Thank you so much!! It was really easy to understand!
Thanks for watching
Sir apki academy kahan hai.physical class attend krsakty hain kia
Sir file bh cloud me kahi upload krdya karen please
Can i have these file? Doc and xls thank you
I tried but this method don't work if single line data is present .. I mean this method will work for 2 or more group sets only.. 😭
Try following formula in Test Column, this should work for single line data
=IF(B2=B3,1,IF(COUNTIF($B$2:$B$13,B2)=1,1,0))
@@KhurramKhanExcelLogic Thanks for your reply sir, but i tried this method, marking single row data with Test as "1" but it did not work.., still waiting for resolution.
I will look into and respond
Check solution of the issue you mentioned in the video description, change IF Function accordingly and you are done.
Hi sir... I have the same prob... Will the if statement change in the table in the document?
It is keep printing the last record of entry is more than 3 our case, Sir
Please watch it again and follow steps, it won't repeat last record number of times
Outstanding Tutorial! Thank you.
Thank you so much sir..
Hi. I tried this. But the last record for each name keeps repeating
Do not Preview, It behave differently. Instead use Finish Mail Merge with Edit Individual Option. You will get the desired result
Excellent
thank you
Thank you !! very helpful!
Thank you kindly! What if I have more than 3 invoices for each customer? How to avoid repeating the code on each row?
Hi, say you have 10 invoices, and you set word document for 15 rows, you need to copy paste same code in word document in 15 rows. This will generate 10 invoices on same page and rest will be blank. This is how it works.
@@KhurramKhanExcelLogic Hi Sir, I am trying like if 2 records I'd is same with different values in other columns so for row1, its working fine however for 2nd row with 0, it's showing blank and thn again showing row 2 value in row3
@@KhurramKhanExcelLogic Also mail merge document is made for every row instead at Customer level document. Please help
Google sheet me kaise karte hai
merci le boss !
bookmark error
Thank you Khurram!
Sir, CAN I HAVE YOUR CONTACT DETAIL
Khurramit24@gmail.com
Great one. Really it would be helpful.
Glad it was helpful!
❤️
Thank you so much!
You're welcome!
@@KhurramKhanExcelLogic Have a prosperous, healthy and happy life, sir!
This is tedious for 50 records, very easy for 5 or 6. not a suitable solution. Thanks
Thank you sir, It worked with little issues.
Glad it helped