none
Need to create small macro in excel RRS feed

  • Question

  • Hi,

    I have two tab in a excel sheet

    Sheet one:

    A                      B

    WBSLevel2 LFINCT0116951
    Task Effort to Analyze
    Task Effort to Fix
    Task Monitoring Task
    WBSLevel2 LFINCT0118382
    Task Effort to Analyze
    Task Effort to Fix
    Task Monitoring Task
    WBSLevel2 LFREQ0150864
    Task Effort to Analyze
    Task Effort to Fix
    Task Monitoring Task
    WBSLevel2 LFREQ0151759
    Task Effort to Analyze
    Task Effort to Fix
    Task Monitoring Task
    WBSLevel2 LFINCT0118553

    Sheet two:

    A

    LFINCT0116951
    LFINCT0118382
    LFREQ0150864
    LFREQ0151759
    LFINCT0118553
    LFREQ0150823
    LFINCT0117950
    LFINCT0115636
    LFINCT0118043
    LFINCT0117840
    LFINCT0114946
    LFINCT0121200

    Now my requirement is if I past list of data in sheet two then in sheet one should be look like as i shown above.

    I want to make it dynamic  using macro,for example if i past list of data with 5 records then in sheet two then in sheet one i should get only five set of data .

    Please help me to write a macro  that will automatically create data format  as shown in sheet one.


    Friday, February 27, 2015 12:03 PM

Answers

  • That is no obstacle, we can create also the intermediate items for "Task" in column A from a second list in one step, paste this items in column B in Sheet2:

    Effort to Analyze
    Effort to Fix
    Monitoring Task

    In Sheet1 use this formulas, resp. values in the cells:

    B1:  =Sheet2!A1
    C1:  1
    B2:  =IF(A2="WBSLevel2",INDEX(Sheet2!A:A,C2),INDEX(Sheet2!B:B,(C2-INT(C2))*100))
    C2:  =IF(A2="WBSLevel2",ROUNDDOWN(C1,0)+1,C1+0.01)


    Fill the formulas in B2:C2 down to the end of your data, hide column C, done.

    Andreas.

    Friday, February 27, 2015 2:32 PM

All replies

  • Hi,

    I have two tab in a excel sheet

    Sheet one:

    A                      B

    WBSLevel2 LFINCT0116951
    Task Effort to Analyze
    Task Effort to Fix
    Task Monitoring Task
    WBSLevel2 LFINCT0118382
    Task Effort to Analyze
    Task Effort to Fix
    Task Monitoring Task
    WBSLevel2 LFREQ0150864
    Task Effort to Analyze
    Task Effort to Fix
    Task Monitoring Task
    WBSLevel2 LFREQ0151759
    Task Effort to Analyze
    Task Effort to Fix
    Task Monitoring Task
    WBSLevel2 LFINCT0118553

    Sheet two:

    A

    LFINCT0116951
    LFINCT0118382
    LFREQ0150864
    LFREQ0151759
    LFINCT0118553
    LFREQ0150823
    LFINCT0117950
    LFINCT0115636
    LFINCT0118043
    LFINCT0117840
    LFINCT0114946
    LFINCT0121200

    Now my requirement is if I past list of data in sheet two then in sheet one should be look like as i shown above.

    I want to make it dynamic  using macro,for example if i past list of data with 5 records then in sheet two then in sheet one i should get only five set of data .

    Please help me to write a macro  that will automatically create data format  as shown in sheet one.


    You dont need a macro to do that. You can do this using formulas. So for example: on Sheet1, B1 cell is =Sheet2!A1, B2 cell is =Sheet2!A5, B3 cell is =Sheet2!A9 ...

    Friday, February 27, 2015 12:37 PM
  • To John,

    This is the way but what if no of data is not always comes in fix size .some time list may come with 7 records and some time it come with 2.

    Friday, February 27, 2015 1:33 PM
  • That is no obstacle, we can create also the intermediate items for "Task" in column A from a second list in one step, paste this items in column B in Sheet2:

    Effort to Analyze
    Effort to Fix
    Monitoring Task

    In Sheet1 use this formulas, resp. values in the cells:

    B1:  =Sheet2!A1
    C1:  1
    B2:  =IF(A2="WBSLevel2",INDEX(Sheet2!A:A,C2),INDEX(Sheet2!B:B,(C2-INT(C2))*100))
    C2:  =IF(A2="WBSLevel2",ROUNDDOWN(C1,0)+1,C1+0.01)


    Fill the formulas in B2:C2 down to the end of your data, hide column C, done.

    Andreas.

    Friday, February 27, 2015 2:32 PM