+ Reply to Thread
Results 1 to 7 of 7

Macro To Create rows to capture data in Columns

  1. #1
    Registered User
    Join Date
    11-28-2012
    Location
    Daresalam,Tanzania
    MS-Off Ver
    Excel 2007/10
    Posts
    3

    Macro To Create rows to capture data in Columns

    I am working on a personnel planning as part of a budget process. I have the staff data by name and other relevant static data then the data has multiple columns by type of benefit (Salary, Bonus, Medical, GPA etc). The data is also monthly so as to account for when new staff joins the organisation by month. I am attaching file with two tabs one in green where the plan is set and the other in blue trying to show how the output would look. I will appreciate any help offered. Thanks a lot. I believe VBA can solve my issue and is not complicated. I am only very weak in VBA but I love how they save time and all. Please find time to look into my thing and am happy to respond to any clarifications.
    Attached Files Attached Files
    Last edited by sirm; 09-12-2018 at 12:18 AM. Reason: Add more emphasis

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro To Create rows to capture data in Columns

    I need a more specific result sheet: I'm unable to reconcile the account with its data
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    11-28-2012
    Location
    Daresalam,Tanzania
    MS-Off Ver
    Excel 2007/10
    Posts
    3

    Re: Macro To Create rows to capture data in Columns

    Thanks for trying to help me. I wish I can explain it better in words.

    1) the input data is showing the plan for staff showing when they start and the estimated monthly salary (the monthly cells with either 1 or 0 are to show if the position will be in staff or not in staff) Columns A to U
    2) Columns V to Z have the percentages for other staff benefits that are incurred over and above the basic salary on column I
    3) Cells I1, V1, X1, W1, Y1 and Z1 contain the Ledger code for the specific staff cost e.g. salary ledger is 7250, bonus 7500 etc
    4) The columns AB to CZ show the calculation of the different staff costs per month based on the initial entries in Columns I to Z

    The results or what is expected is:
    1. The data on the columns A to H to remain on the rows
    2. The Staff costs columns are then transposed to the rows meaning the for each benefit the initial rows A to H will appear twelve times so as to accommodate 12 months
    3. The Month and ledger code would appear after the amount as additional column for each row e.g. salary for Jan would appear on column I, column J would show the ledger code 7250
    4. The entire table is arranged as per 1 to 3 above so that the number of rows will be equal to staff members in the plan multiplied by number of benefits, multiplied by 12 (months) e.g. for the attached table with 5 employees and s6x staff costs categories =(5x6x12= 360 Rows)
    5. The table will have headers as from A to H in the entry table with the rest columns being I to K being "monthly cost" , "Month" and "Ledger code" respectively.

    I would wish for the macro to continue down to all rows with data so that if the plan has 500 employees it will have (500x6x12)= 36,000 rows in the result sheet.

    I hope this helps

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro To Create rows to capture data in Columns

    But the sheet provided is inconsistent - I still need a better constructed result sheet

  5. #5
    Registered User
    Join Date
    11-28-2012
    Location
    Daresalam,Tanzania
    MS-Off Ver
    Excel 2007/10
    Posts
    3

    Re: Macro To Create rows to capture data in Columns

    I have reattached the file with two staff costs matched for first row I hope that is better
    Attached Files Attached Files

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro To Create rows to capture data in Columns

    Still irreconcilable differences

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,339

    Re: Macro To Create rows to capture data in Columns

    If it helps, I didn't understand the details either. The basic operation looks like a somewhat routine "unpivot" operation. Assuming you can still get Power Pivot for your version(s) of Excel (or if you have upgraded to 2016 without updating your profile here), and you are not required to use a VBA macro, the easiest solution may be to use an unpivot type command in Power Pivot (Get and Transform in 2016). https://excelchamps.com/blog/unpivot-data/
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to create multiple rows with similar columns
    By bkelly0721 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-24-2015, 02:55 PM
  2. Create VBA Macro to create new rows according to data between Sheet1 and Sheet2
    By kcarth in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-29-2015, 02:06 PM
  3. Macro to create zip files from form to capture data.
    By dtechpcs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-02-2015, 11:33 AM
  4. Replies: 7
    Last Post: 08-05-2014, 05:34 AM
  5. Create a Macro to capture date from other excels - Help Please!
    By Inez15 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2013, 09:56 AM
  6. Need help to create macro's to capture screen shots while doing manual testing
    By Nidhya J in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-25-2012, 10:52 AM
  7. Macro to delete certain rows and create columns
    By excel77 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-14-2010, 10:06 AM

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