Macros to move multiple rows of data for same unique identifier into one.

Hi -

Need assistance with an excel file. What I'm trying to do is help the acctg department simplify a manual process for invoicing some of our hotels that we work with. What I'm trying to do is have all of the rows for the same hotel name to be on one line so that we can do a mail merge by simplifying the process. I'm open to any suggestions. Here is a sample of the data - there are a lot more records but I will need for this to also calculate the final commission due to us per hotel.

Thanks in advance for the help!

Copy of Missing Data Report 6-5 thru 6-12_NEWSAMPLE.xlsx

I am looking at your spreadsheet and am wondering what exactly you are trying to do. I see that you want to line up similar entries horizontally which in my opinion is not a very good way to do it. What will you be doing after it is organized like this? Maybe I can help you cut out the middle man!

Originally Posted by GaidenFocus

I am looking at your spreadsheet and am wondering what exactly you are trying to do. I see that you want to line up similar entries horizontally which in my opinion is not a very good way to do it. What will you be doing after it is organized like this? Maybe I can help you cut out the middle man!

Thanks for the prompt response. What we are trying to do is basically send an email via mail merge with an invoice template attachment to each contact for those specific hotels. One invoice per hotel showing all of the reservation for that specific hotel with the total due. This unfortunately is due to some data that we lost during a migration to a new environment and the acctg software/server didn't receive during the nightly sql jobs. IT developers have done everything they can and I'm trying to simplify the one person's life in the acctg dept who has to send out these invoices. Of course the true data contains thousands of rows. Let me know if there is anything you recommend.

QB Dummy invoice.xls

Try a Pivot Table.

Ok so what you need is to extract all rows from the same hotel and place it elsewhere? I can make it go through the list and find everything from the same hotel, either save it on a new sheet, a new work book, or even put it into an email. I can also do the first idea you had if you think that is the best way to go!

Woops I did not see that you attached an invoice file, let me check that out.

Originally Posted by GaidenFocus

Ok so what you need is to extract all rows from the same hotel and place it elsewhere? I can make it go through the list and find everything from the same hotel, either save it on a new sheet, a new work book, or even put it into an email. I can also do the first idea you had if you think that is the best way to go!

Looking at the invoice template - do you see a way to automatically fill that info in there and send it in an email as an attachment? If not - then I think it might be easier for us if everything can be on one line.

Thanks!

And perhaps if you decide to go with my original idea - can the results be in a new sheet?

Yes we can auto fill the invoice, I've actually done something like that before (a guy on these forums helped me do it 3 years ago!). Let me modify it and get it back to you.

Originally Posted by GaidenFocus

Yes we can auto fill the invoice, I've actually done something like that before (a guy on these forums helped me do it 3 years ago!). Let me modify it and get it back to you.

Awesome, thanks!

Ok my friend this is all I have done for now. I will work on this later after i get some feed back from you. Please review the invoices to make sure they are procing correcting.

This is a brief explanation of what it does:

1. Makes a temp list of unique account numbers

2. filters the list for each account numbers

3. it gathers information from the filtered list, slaps it into the invoice and creates a new sheet, then clears the master invoice

this is not entirely what you wanted i know, but it will take some doing for the email part. it would be a good idea to make a database of account numbers and emails. this will make it easier to spit these off to specific email addresses. Once you have created this, make a dummy list (i just need the format of your database) and send it to me I will modify this workbook to parse your email list. Also are you using outlook?

Thanks this looks great. We need to show the occupant name, date of arrival and departure as well on the invoice. I can have more info on that tomorrow while I review this w/ the acctg rep. Also we do use outlook. This does look perfect. I also have the email addresses for each hotel so let me know where that should be added.

Thanks again

Dom

Ok make a completed sample invoice of where information goes and maybe a comment on the cell with the column it was from in the data sheet. also create a new sheet with account numbers and emails (account numbers in column A and emails in column B) when you have it like you want erase it then send it back to me and ill play around with it.

OK here is the file attached. I've added a new tab called "Email" and added the information you requested. Also, in the data tab I've added a column that says email sent. Is it possible to add a time stamp or something that shows that the email was sent to that hotel?

Also - how can we have the invoice sent as an attachment? Is PDF possible? If so we also would like to add a copy to the body of the email.

Final question (and thanks for all of your help w this!) does the macro look at the entire data tab or is it confide to a range of cells? I'm asking this in case we do have data that goes beyond the 343rd row. Same would be for the Email tab. Also can the master tab for the invoice auto adjust the rows of data that gets added if more than whats specified now? If not - no biggie - I don't think we will run into that issue (but I believe the range needs to be adjust as we added a new column.

Thanks again for all your help!

working.xlsm

Ok to answer your questions:

Yes we can add a time stamp to the lines. We could even have it bypass those lines to only proc entries it has not done before. (im starting to think that new entries are added to the end of this list?)

I am sure converting it to PDF is possible but not on my end as I do not have the ability to create PDFs (nor the slightest idea on how to do it!)

The auto filter function will encompass the entire sheet not a specific range of cells.

What I was planning on doing is instead of creating new sheets (which was just for your visualization anyways) we copy the master sheet to a new workbook and put that into an attachment. Also i could copy a range of cells into the body of the email.

It is possible to at the end of the list or start fresh. Could we implement both options?

Don't worry about converting to PDF as long as the attachment can be restricted so that the hotel cant change the data.

I can provide the copy for the body of the email shortly. I'll add it on here.

Yes both options would work fine, as long as you have the "PAID" column which i'm assuming you want to use to indicate it has been invoiced?

No lets use the email sent column (last one)

DOH, haha I opened the wrong workbook and didn't see the changes you made. No worries. I will work on this some more today and let you know what i get done.

Also the Email database only needs one occurance of an account number, and im going to make it ask for an email if it can't find one. that way it will update itself with email addresses.


I like the new invoice btw it was a little messy before

Disregard double post

Originally Posted by GaidenFocus

DOH, haha I opened the wrong workbook and didn't see the changes you made. No worries. I will work on this some more today and let you know what i get done.

Also the Email database only needs one occurance of an account number, and im going to make it ask for an email if it can't find one. that way it will update itself with email addresses.


I like the new invoice btw it was a little messy before

excellent idea about the updating/asking for an email addrerss!!!

haha I know it was messy - acctg just doesn't have a visual for things! LOL!

Here is the copy for the body of the email:

Hello,


Please note, due to a systems error some reservations from June were not billed properly. Please see attached for missed June reservations for your property. I appreciate your help, and am sorry for the inconvenience. If you have any questions feel free to contact me directly.

OK so I have completely rewritten this thing and it is running great. I will have to work on the email portion tomorrow as I do not use outlook at my house. (I had the 4th off, hope you did too!) I need to know what date is supposed to be under invoice date so I can make that auto populate as well. Also the Customer Info page holds the data for the emails and asks for emails when it finds a new account number.

Oh and the way I'm doing this creates a folder called "Completed" then moves the invoice in there as a separate workbook. What naming convention would you want to use? right now I'm using {ACC_NUM} - {DATE}.xlsx

Originally Posted by GaidenFocus

OK so I have completely rewritten this thing and it is running great. I will have to work on the email portion tomorrow as I do not use outlook at my house. (I had the 4th off, hope you did too!) I need to know what date is supposed to be under invoice date so I can make that auto populate as well. Also the Customer Info page holds the data for the emails and asks for emails when it finds a new account number.

Thanks! I did have the 4th off - hope u had a good one!

Date should be the day that the invoices get sent. So =Today() ?

Originally Posted by GaidenFocus

Oh and the way I'm doing this creates a folder called "Completed" then moves the invoice in there as a separate workbook. What naming convention would you want to use? right now I'm using {ACC_NUM} - {DATE}.xlsx

As far as the folder that's folder that's perfect!

Ok I got it working but am running into an issue with Outlook. When excel trys to send an email it pops up an alert saying "A program is trying to send an email on your behalf" and you have to wait 5 seconds to hit yes. Trying to find a way to disable this. Also what should the subject be?

OK I got it to work! Let me know if you find any issues with it or have any questions.

Originally Posted by GaidenFocus

OK I got it to work! Let me know if you find any issues with it or have any questions.

This looks great. Subject should be as follows:

Hotel_Name [Hotel_acctg_code] - Quikbook.com Invoice, Payment Due Upon Receipt


Thanks!

Here you go!

this is great! thank you so much. I will review this with the team on Monday and let you know if I run into any issues!

thanks again!

No problem, glad I could help.