+ Reply to Thread
Results 1 to 32 of 32

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

  1. #1
    Registered User
    Join Date
    07-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    14

    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

  2. #2
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

    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!

  3. #3
    Registered User
    Join Date
    07-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    14

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

    Quote Originally Posted by GaidenFocus View Post
    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

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

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

    Try a Pivot Table.

  5. #5
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

    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!

  6. #6
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

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

  7. #7
    Registered User
    Join Date
    07-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    14

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

    Quote Originally Posted by GaidenFocus View Post
    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!

  8. #8
    Registered User
    Join Date
    07-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    14

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

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

  9. #9
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

    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.

  10. #10
    Registered User
    Join Date
    07-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    14

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

    Quote Originally Posted by GaidenFocus View Post
    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!

  11. #11
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

    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?
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    07-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    14

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

    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

  13. #13
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

    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.

  14. #14
    Registered User
    Join Date
    07-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    14

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

    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

  15. #15
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

    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.

  16. #16
    Registered User
    Join Date
    07-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    14

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

    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.

  17. #17
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

    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?

  18. #18
    Registered User
    Join Date
    07-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    14

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

    No lets use the email sent column (last one)

  19. #19
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

    Re: Macros to move multiple rows of data for same unique identifier into 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

  20. #20
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

    Disregard double post

  21. #21
    Registered User
    Join Date
    07-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    14

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

    Quote Originally Posted by GaidenFocus View Post
    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.

  22. #22
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

    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.

  23. #23
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

    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

  24. #24
    Registered User
    Join Date
    07-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    14

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

    Quote Originally Posted by GaidenFocus View Post
    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() ?

  25. #25
    Registered User
    Join Date
    07-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    14

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

    Quote Originally Posted by GaidenFocus View Post
    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!

  26. #26
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

    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?

  27. #27
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

    -deleted still working on it
    Last edited by GaidenFocus; 07-05-2013 at 11:57 AM.

  28. #28
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

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

  29. #29
    Registered User
    Join Date
    07-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    14

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

    Quote Originally Posted by GaidenFocus View Post
    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!

  30. #30
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

    Here you go!
    Attached Files Attached Files

  31. #31
    Registered User
    Join Date
    07-02-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    14

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

    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!

  32. #32
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

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

    No problem, glad I could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1