Hi all,
A tricky one that I can't work out that I would love some help with. In essence it involves transposing a set of data into a form that can be used for a mail merge.
I have attached a file to work with and try and explain the need.
The "Data" tab contains the original data. It is not in the most helpful form, but it is what I have to work with. NOTE - all details have been changed for annomity (and formulas replaced with values, etc).
The file lists the customer name, contact, email, product, location and then a price for each day. The data is arranged with headings on the left and the information going across the page in columns (not ideal!). There is a separate column for each combination of product and location. So if a company gets more than 1 product, or gets deliveries to multiple locations, that company will have a number of columns, as every different combination is a different price.
The objective is to generate a list that can be used in a mail merge situation to email the price out to each customer. Mail merge requires headings at the top and data in rows. While in essence this should be a simple transpose task (as I have done in the tab "mailmerge1"), I was given some additional criteria and ran into some limitations imposed by the way MS does its mail merge:
1. Mail merge can only have one (1) email address in the Email field, so for companies with multiple emails, there needs to be a separate entry in the mail merge list for each email address and then that row has a duplicate of the information for that company.
2. There is a STRONG preference to only send one (1) email to each email address. The way I did it in "mailmerge1" only has 1 product/location combination per line, so each person would recieve multiple emails for each combination they have. I thought about concatenating some information to achieve this, however the next point makes this hard...
3. The original list of data is very dynamic! It gets updated with new customers, existing customers who want a different product or location and customers who get taken off the list. This happens at least once/twice a week.
Now ideally, the best solution would probably be in a database, but for the moment I only have excel to work with, and apparently excel can do anything!! So hopefully we can work something out.
Given that the list is so dynamic, I think trying to do this with formulas is difficult (at least for me!), so I was thinking a macro solution may be appropriate? That way the macro could just be run each day and it would update the mail merge list based on the data tab at the time. Problem is, I'm not very good with macros either, hence asking the Chandoo community.
The macro may work something like:
- clear all data on "mailmerge2" tab
- For i = 1 to number of companies
- For n = number of product/location combinations (i.e. number of columns for that company)
- Go grab the customer name, contact, email, date, terms, and all the product/location combinations (n)
- IF there are multiple emails, duplicate the above row with new email address
- repeat for all i
- sort list on company name when finished
- end
On tab "mailmerge2" I have given an example of how I would like the output to look (highlighted in yellow) just by using a direct = statement. If we can manipulate the data to look like this, we will all be awesome!
Thanks in advance for the help, if there are any more questions, please ask. I have tried to explain what I need but realise it might not all make sense to others.
Cheers,
Oxi
A tricky one that I can't work out that I would love some help with. In essence it involves transposing a set of data into a form that can be used for a mail merge.
I have attached a file to work with and try and explain the need.
The "Data" tab contains the original data. It is not in the most helpful form, but it is what I have to work with. NOTE - all details have been changed for annomity (and formulas replaced with values, etc).
The file lists the customer name, contact, email, product, location and then a price for each day. The data is arranged with headings on the left and the information going across the page in columns (not ideal!). There is a separate column for each combination of product and location. So if a company gets more than 1 product, or gets deliveries to multiple locations, that company will have a number of columns, as every different combination is a different price.
The objective is to generate a list that can be used in a mail merge situation to email the price out to each customer. Mail merge requires headings at the top and data in rows. While in essence this should be a simple transpose task (as I have done in the tab "mailmerge1"), I was given some additional criteria and ran into some limitations imposed by the way MS does its mail merge:
1. Mail merge can only have one (1) email address in the Email field, so for companies with multiple emails, there needs to be a separate entry in the mail merge list for each email address and then that row has a duplicate of the information for that company.
2. There is a STRONG preference to only send one (1) email to each email address. The way I did it in "mailmerge1" only has 1 product/location combination per line, so each person would recieve multiple emails for each combination they have. I thought about concatenating some information to achieve this, however the next point makes this hard...
3. The original list of data is very dynamic! It gets updated with new customers, existing customers who want a different product or location and customers who get taken off the list. This happens at least once/twice a week.
Now ideally, the best solution would probably be in a database, but for the moment I only have excel to work with, and apparently excel can do anything!! So hopefully we can work something out.
Given that the list is so dynamic, I think trying to do this with formulas is difficult (at least for me!), so I was thinking a macro solution may be appropriate? That way the macro could just be run each day and it would update the mail merge list based on the data tab at the time. Problem is, I'm not very good with macros either, hence asking the Chandoo community.
The macro may work something like:
- clear all data on "mailmerge2" tab
- For i = 1 to number of companies
- For n = number of product/location combinations (i.e. number of columns for that company)
- Go grab the customer name, contact, email, date, terms, and all the product/location combinations (n)
- IF there are multiple emails, duplicate the above row with new email address
- repeat for all i
- sort list on company name when finished
- end
On tab "mailmerge2" I have given an example of how I would like the output to look (highlighted in yellow) just by using a direct = statement. If we can manipulate the data to look like this, we will all be awesome!
Thanks in advance for the help, if there are any more questions, please ask. I have tried to explain what I need but realise it might not all make sense to others.
Cheers,
Oxi
