1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel- Multiple Rows into a Single Row

Discussion in 'Business Applications' started by amiracali, Feb 24, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. amiracali

    amiracali Thread Starter

    Joined:
    Feb 23, 2013
    Messages:
    5
    I am working with a spreadsheet for invoicing.

    (I put 3 attachments of all the docs I am utilizing)

    { this is what I figured could work. If anyone has an alternate way of doing it, I welcome suggestions}

    I am trying to find a solution that takes every person's reservations and puts them across one row, I am doing this for an email merge to pull all of the data into an invoice to email each client.

    I have an extremely long column data sheet, so I am not sure what exactly you will need.

    I have two worksheets in 1 workbook

    The Spreadsheet One is Where I am Pulling Data From:
    My Columns Are:
    Full name Reservation Type Rooms Date Start time Length Finish time cost cum hrs Mo Pkg Cost IPC_Rooms Date Start time Length Finish time cost cum hrs Mo Pkg Cost
    This column has multiple rows of the same name showing every room reservation they have done for the month. There is up to 30 rows of reservations.

    All of the other data I am using is pulled from other resources.

    The Second Spreadsheet is what I have developed to use for the email-merge its columns are This:

    Name Email Outstanding Balance Private Locker Thrive Workshop TP Reward PI Comp Coupon Monthly Pkg Mo Pkg Rate IncChg_Type_1 IncChgDay_1 IncRm_1 IncLngth_1 IncRate_1 IncCost_1 IncChg_Type_2 IncChgDay_2 IncRm_2 IncLngth_2 IncRate_2 IncCost_2 Res Type_1 ResDate_1 ResRm_1 ResStart_1 ResLength_1 ResCumHrs_1 Res Type_2 ResDate_2 ResRm_2 ResStart_2 ResLength_2 ResCumHrs_2 Res Type_3 ResDate_3 ResRm_3 ResStart_3 ResLength_3 ResCumHrs_3 Res Type_4 ResDate_4 ResRm_4 ResStart_4 ResLength_4 ResCumHrs_4 Res Type_5 ResDate_5 ResRm_5 ResStart_5 ResLength_5 ResCumHrs_5 Res Type_6 ResDate_6 ResRm_6 ResStart_6 ResLength_6 ResCumHrs_6 Res Type_7 ResDate_7 ResRm_7 ResStart_7 ResLength_7 ResCumHrs_7 Res Type_8 ResDate_8 ResRm_8 ResStart_8 ResLength_8 ResCumHrs_8 Res Type_9 ResDate_9 ResRm_9 ResStart_9 ResLength_9 ResCumHrs_9 Res Type_10 ResDate_10 ResRm_10 ResStart_10 ResLength_10 ResCumHrs_10 Res Type_11 ResDate_11 ResRm_11 ResStart_11 ResLength_11 ResCumHrs_11 Res Type_12 ResDate_12 ResRm_12 ResStart_12 ResLength_12 ResCumHrs_12 Res Type_13 ResDate_13 ResRm_13 ResStart_13 ResLength_13 ResCumHrs_13 Res Type_14 ResDate_14 ResRm_14 ResStart_14 ResLength_14 ResCumHrs_14 Res Type_15 ResDate_15 ResRm_15 ResStart_15 ResLength_15 ResCumHrs_15 Res Type_16 ResDate_16 ResRm_16 ResStart_16 ResLength_16 ResCumHrs_16 Res Type_17 ResDate_17 ResRm_17 ResStart_17 ResLength_17 ResCumHrs_17 Res Type_18 ResDate_18 ResRm_18 ResStart_18 ResLength_18 ResCumHrs_18 Res Type_19 ResDate_19 ResRm_19 ResStart_19 ResLength_19 ResCumHrs_19 Res Type_20 ResDate_20 ResRm_20 ResStart_20 ResLength_20 ResCumHrs_20 Res Type_21 ResDate_21 ResRm_21 ResStart_21 ResLength_21 ResCumHrs_21 Res Type_22 ResDate_22 ResRm_22 ResStart_22 ResLength_22 ResCumHrs_22 Res Type_23 ResDate_23 ResRm_23 ResStart_23 ResLength_23 ResCumHrs_23 Res Type_24 ResDate_24 ResRm_24 ResStart_24 ResLength_24 ResCumHrs_24 Res Type_25 ResDate_25 ResRm_25 ResStart_25 ResLength_25 ResCumHrs_25 Res Type_26 ResDate_26 ResRm_26 ResStart_26 ResLength_26 ResCumHrs_26 Res Type_27 ResDate_27 ResRm_27 ResStart_27 ResLength_27 ResCumHrs_27 Res Type_28 ResDate_28 ResRm_28 ResStart_28 ResLength_28 ResCumHrs_28 Res Type_29 ResDate_29 ResRm_29 ResStart_29 ResLength_29 ResCumHrs_29 Res Type_30 ResDate_30 ResRm_30 ResStart_30 ResLength_30 ResCumHrs_30 Res Type_31 ResDate_31 ResRm_31 ResStart_31 ResLength_31 ResCumHrs_31
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,591
    First Name:
    Hans
    Hi, welcome to the forum.

    Any special reason for the large font size?
    I think most of us can read normal text :)
    I'm curious and will take a look at the attached files, I cannot understand why you want the all in one row to merge to Word, but like I said, I'll check the attachments.
    Maybe I can offer some help.
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,591
    First Name:
    Hans
    Hi,
    Well first of all I have no idea why you want all the dat in one row, this is unworkable
    Since the 'Sample All Chages.xlsx' has data linked to your local disk Heidi .... the links are invalid.
    Just for starters:
    I suggest you create an Excel sheet with all your client data in one row
    The Data Sheet Room Res looks okay,

    So when you want to run an invoice you have to select the client and then fill the word file with the data from the client tabel and all the reservations for that client for that peeriod only.
    This is not something to do 1-2-3, you'll have to think it over.

    Client data is always more or less static
    Reservations can be many.

    So maybe a temporary sheet where the data you need for the Invoice is temporarily cumulated, in my opinion this can be done with a macro, I'm sure there others here that are much better in formula handling to do this.

    You will have to give your structure a thorough thinkover.
    It is a 'relational database' even if you are not doing it in MS Access or any other databse
     
  4. amiracali

    amiracali Thread Starter

    Joined:
    Feb 23, 2013
    Messages:
    5
    I am not sure if I am following your line of thinking Hans,

    The only static data that I am using is the client name and email. The email is for the email-merge reference and the name is to give a data name.

    As far as having the data all on one row. I have tried using various different means to not do that, but with any formula, you need a data name. when I use a VLookup, it only pulls the first line of data.

    I have done a complex mail merge with this format before (all the data on one row) and it works well for that purpose. I am able to automate the majority of the invoicing process.

    The only thing I have been unable to automate is the pulling of each line of reservation to the one row. The company is growing, and we need to prepare for expansion. Automation seems to be the most efficient way to do that.

    Can you please expand and give examples on what you are referring to? I think maybe I just am not able to visualize it (I am a visual learner)

    Thanks for your help!!

    Heidi
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,591
    First Name:
    Hans
    I'll try and see if I can help in some way.
    One thing I need to see if I understand:
    One invoice for each client, is that correct?
    I'll have to try and fugure out where goes what and you do not need all data in one row, you can loop through a whole table and create the result in Excel before if is copied to the word file.
     
  6. amiracali

    amiracali Thread Starter

    Joined:
    Feb 23, 2013
    Messages:
    5
    Yes, One invoice for each customer.

    Thanks for any help you can give!!

    Heidi
     
  7. amiracali

    amiracali Thread Starter

    Joined:
    Feb 23, 2013
    Messages:
    5
    :) Yes, One invoice for each customer.

    Thanks for any help you can give!!

    Heidi
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,591
    First Name:
    Hans
    Hi Heidi,
    I've firs got to figure out the invoice so that I can use the same merge fields so you'll have to be patient.
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,591
    First Name:
    Hans
    It's taking a little longher, time is the issue, but I haven't forgotten :)
     
  10. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    The test macro will recompile into sheet2 one line per customer.
    If the business continues to grow, you should consider getting a proper software.
     

    Attached Files:

  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,591
    First Name:
    Hans
    Nice one! (y)
    Its a good thing that we now have 16384 columns in Excel 2007 (instead of the 256 in previous versions) and newer that means that you can store up to 1819 reservations per row :)
    In the previous versions you'd be stuck with 27 reservations max per row
     
  12. amiracali

    amiracali Thread Starter

    Joined:
    Feb 23, 2013
    Messages:
    5
    Good Day Hans,

    I am terribly sorry for taking this long to express my thanks and gratitude to you! :eek:

    I was able to utilize your macro, and it did help greatly.

    I wanted to say, truly, that I couldn't have done it without you, and that I truly appreciate that you are sharing your skills and expertise without expecting anything in return.

    It is a very rare quality indeed! I love the concept of this site and how everyone collaborates together to help those less knowledgeable.:D

    Thank you!!

    Heidi
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1090773

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice