I would like to create a list of names for individuals that have certain needs from data in an excel spreadsheet. I have tried different variations of Next Record, Next Record If, Skip Record If, If...Then...Else, etc. and can't seem to figure it out. I have tried to search for suggestions but apparently I'm not familiar enough with the terminology to effectively search.



For Example:

Oxygen users
Smith, John
Jones, Mary

Assistance Devices
Jones, Mary: Wheelchair
Henderson, Harry: Walker, Cane

Diabetic
Henderson, Harry (Insulin)
Smith, John (Diet)

The spreadsheet lists the names of all persons regardless of whether or not they have an entry in each category.

• Some may be name only with no other data. These would be skipped completely.
• Some categories (Oxygen) have Yes or No with no blanks. These would only list the name if it were Yes, skipping any No answers.
• Some categories (Assistance devices) have only information if it exists, with blanks for no information entered. These would list the name with the items after the name, skipping any blanks.
• Some categories (Diabetic control) are free text. These would list the name with the type of control, skipping "None", "N/A", or blanks.

I appreciate any suggestions or pointers on where to learn more. I've done some cool stuff with mail merge the last few days and was excited about the possibilities. I just can't seem to get the syntax down to list names I want and exclude the names I don't.

You might need to post a sample Excel file that shows your data structure so we can suggest a way to produce a report like this.

I suspect that this is not something that is suited to a Mail Merge report in Word. It does however look like something you could do in Excel with a Pivot Table or possibly in Microsoft Access with a SQL query and report based on that query. It will be important to see how your data is tabulated to work out a method of producing this report.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

I have added an attachment of the excel export from our application software. I got the idea to do it via mail merge after creating a label mail merge and thought I might be able to do multiple records on a single page instead of records on multiple labels.

I thought the directory mail merge was the answer because I could put names under the headings like address lines, and just skip the names I didn't want. I just couldn't get them to skip correctly.

BTW, your profile picture is one of my favorite Gary Larson Far Side cartoons!

Attached Files

special_needs_export--not_a_real_mission--2023-06-22.xlsx (9.6 KB, 2 views)

Your data is structured quite nicely but I can't see how it could be used to build a mail merge report in a single Word document.

I think you can use a many-to-one type merge for one section of the report but needing to start multiple times with each a new filter/output fields is problematic.

A Word-centric method using a 'subreport' concept would be to create a shell document and produce separate mail merge output docs for each of the sections. You can then use IncludeText fields to link to those separate documents. This is probably too labour intensive to use as a realistic option.

I would say that this is a job that Microsoft Access is better suited for with a report comprised of sub-reports.

You could also create a macro to do it all in a single Word document. This macro could create a recordset from the input data and then loop through that to build the various sections.

Do any of those options appeal?

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

I somewhat suspected that this wasn't an easy straightforward project. Thank you so much for your fast response and providing the suggested options, but for the limited use I don't see that there would be a return on my time invested.

I can use the excel data to create the list manually. Which in itself is a step ahead of where I was. Prior to using this application software the data was gathered manually at personal interviews. I then went page by page of interview results and compiled the master list. Now I can compile the list manually from the data in the spreadsheet without having to conduct the 50 personal interviews.

I may revisit this when I am bored just because it is a bit of a challenge and I always like to win! :-)