+ Reply to Thread
Results 1 to 9 of 9

Reorganize and add columns using a macro

  1. #1
    Registered User
    Join Date
    01-03-2018
    Location
    Seattle, WA
    MS-Off Ver
    MS Office 365
    Posts
    2

    Reorganize and add columns using a macro

    I have the following spreadsheet that I need to have reorganized be placed in the the format on Final Report tab within the spreadsheet. It takes too much time to do this process manually every week and I'm hoping that someone can assist with creating a Macro for that can help me with my new layout.

    Main Spreadsheet layout:
    Employer# Employer Name Employer Address Emp. Contact Person Employer Phone Employer Fax Employer Email Report Total Local Agreement Contract ID Work Month SSN Last Name First Name Total Hrs Premium Hrs NIA Amount Rate Posted Date Receipt No. Comments

    Final Report - the first column should be blank
    Receipt No. Employer Name Employer# Local PaymentType WorkMonth EndoftheMonth Local Contract ID Group ID App's_Codes SSN Last Name First Name MI Total Hrs Premium Hrs NIA Amount TotalHoursWorked Agreement DataEntryDate Rate

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling
    MS-Off Ver
    Microsoft Office 365
    Posts
    17,844

    Re: Reorganize and add columns using a macro

    Are these column names you've posted?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    08-27-2017
    Location
    Modesto, CA
    MS-Off Ver
    Microsoft Office Excel 2003
    Posts
    55

    Re: Reorganize and add columns using a macro

    To get a prompt answer, You might limit your columns of data to, say, four, limit the rows to, say six, and provide an example of the four columns of data in the Main spreadsheet, and the order in which you want the data in the Final Report. Otherwise, your data is just too much to get a handle on. A solution could then be readily provided with fairly explicit and obvious steps to expand it to fit your data. Just sayin'

  4. #4
    Registered User
    Join Date
    08-27-2017
    Location
    Modesto, CA
    MS-Off Ver
    Microsoft Office Excel 2003
    Posts
    55

    Re: Reorganize and add columns using a macro

    dogs cats pigs cows
    a f k p
    b g l q
    c h m r
    d i n s
    e j o t

    For example, with this data in A1:D6 of Worksheet(3), the following code, which assumes you want cats, cows, dogs, pigs in the final report, will produce that in Worksheet(4)

    Please Login or Register  to view this content.
    Here's the result from worksheets(4), Range("b1:e6")

    cats cows dogs pigs
    f p a k
    g q b l
    h r c m
    i s d n
    j t e o

    Maybe that,s one simpleminded approach you could consider. It might also provide a model from which others can make other suggestions.

  5. #5
    Registered User
    Join Date
    08-27-2017
    Location
    Modesto, CA
    MS-Off Ver
    Microsoft Office Excel 2003
    Posts
    55

    Re: Reorganize and add columns using a macro

    Whoops! One line of the code should have been

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-31-2012
    Location
    Germany
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Reorganize and add columns using a macro

    The easiest way would be to use Power Query for this. In Power Query you can "append" tables to each other and Power Query automatically matches the columns based on their headers. In the past I developed VBA code to do things like this, but nowadays I create a little Power Query script for this type of work (and many other purposes). It goes faster and is much more versatile. At the beginning it requires some learning and playing around but its worth it.

  7. #7
    Registered User
    Join Date
    08-27-2017
    Location
    Modesto, CA
    MS-Off Ver
    Microsoft Office Excel 2003
    Posts
    55

    Re: Reorganize and add columns using a macro

    I know nothing about Power Query. Is that something the original poster might likely be able to mount? Could you give him some clues to get started? (I wouldn't mind seeing them myself

  8. #8
    Registered User
    Join Date
    01-31-2012
    Location
    Germany
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Reorganize and add columns using a macro


  9. #9
    Registered User
    Join Date
    01-03-2018
    Location
    Seattle, WA
    MS-Off Ver
    MS Office 365
    Posts
    2

    Re: Reorganize and add columns using a macro

    Thank you for the additional information about Power BI, I will take a look at the software to see if it meets my needs.

+ 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