Transpose Many Dynamic Ranges (vertical) to Horizontal Arrays for Mail Merge

Context: I want to change how data is imported into my Word doc (through mail merge) from my excel document.* The process described below happens constantly and each time the number of users ranges from 20 to 1,000.Sheet1: (user application lists)Dynamic ranges (1 range per column; each column with one user's unique list of appllications) as lists are updated over/over.* User apps listed vertically is req'd.Sheet 2: (mail merge source table)1 user / row - COL A-C = user-specific fields.* COL D' formula pulls the "app list" (from a dynamic range) in a single cell (forcing a return after each app).* Sheet 3: (software list)Software vendor, app names, etc. are assigned to column headers; e.g. COL C denotes if the application requires a customized installation (cell has a "Yes" value with a nested hyperlink to the documentation).Goals:

A) For each Word document (assigned to a single user) I want to bring in their list of applications (from sheet 1) into a table [one app per row; such that each app is in its own row].

* B) I want to have a table column (1/2")to the left of the application with nothing in it (for someone to initial install completion) and

* C) I want to have a table column (1/2") to the right of the application that places a "Yes" in the cell if the application requires customization - with the hyperlink.

I am able to merge the single cell ("app list") into a single table cell my Word file but... I have no idea how to accomplish A, B, and C - or for that matter... if it's possible.* I've done a ton of searching and it would appear to be possible... but way over my head.Any help appreciated. (couldn't post sample file - will try again)

Thanks. I didn't drag the file into the 2nd window.

You have over 998 split for each njones and sadams in column 4 if use vbLf as a delimiter in sheet 2 to get to sheet 3.
Only jsmith has 4 split result

Thanks for replying AB33. I'm a bit of a noobie but, if I understand your reply, I have different ranges set for each user. You're correct - my mistake - my sample file should specify a dynamic range for each. To be honest I found a variety of approaches online and I'm not sure which is best for my situation. I can safely say each user will have less than 50 applications. I've uploaded a corrected sample file.

Regarding vbLf... I'm afraid I don't understand.

What I meant despite there are only5-6 words in each cell, there are over 2000 empty lines, so when you extract these 5-6 words, you will end up with over 1000 empty lines.

Is your desired result is sheet 2, i.e. you wanted to transfer the data in sheet 3 in to sheet 2?

My goal is to use a "dynamic range" to capture the applists (in sheet3) so that I don't have any empty lines of data. I actually set the ranges manually for my first merge last week. With so many more of these to come I'll convert these to dynamic ranges using OFFSET. I think I can figure that part out.

My real challenge is best described in the attached diagram. I'm afraid this challenge lies with both Excel and Word. I hope I've given you adequate information.

Thanks for your attention to this.