Posts by Xani

    Hello,
    I would like to combine Project Managers Names after splitting as you will see in sheet column Current Project Manager Name into group sheet and named sheet same as in column Owners Name after Combine


    Example:
    Combine Engineering Department – User + Mr Ahmed Mokhtar Ahmed Soliman =ENG Sheet

    Files

    Hello,



    I would like to combine owners as you will see in the sheet Name Change, there is a list of Current Project Manager Names, and then Change To “Which after Splitting it will change the name of Project Manager”, and what I want to do instead is to combine for example:


    Project Manager Engineering Department – User + Mr Ahmed Mokhtar Ahmed Soliman and re-name sheet after combine & splitting to ENG which combine both.


    Project Manager Marlon Casil + Mr Jasim Hasan Alseba + OGA Shift - Department User and re-name sheet after combine & splitting to IAB - Shift (Marlon Casil)



    For the Project Managers that are single they will have their own name for example:



    Mr Ahmad Rafik Bin Mohd Tahir re-name sheet after splitting to IAB - Ahmad Rafik.



    Currently the Workbook work as following:


    Splitting each Project Manager Name, and re-name Project Manager Name as the one in Name Change SheetColumn of Change To.



    Is that possible to do ? If not, Am currently doing it manually for example I created Total Summary sheet which will calculate Completed , Open of each Project Manager, then I combine sum of each owner and return that value to Yearly Summary Page.



    I don’t know if my explanation is clear or not but I hope someone will understand what I mean.

    Files

    Re: Return Total value from one sheet to another sheet depend on criteria ?


    KjBox,


    This formula gave me COMP output
    =SUMPRODUCT((Sheet1!$H$2:$H$500= 'Total Summary'!F5)*(Sheet1!$E$2:$E$500<>{"COMP","CAN"}))

    NBVC,


    =COUNTIFS(Sheet1!$H$2:$H$500,'Total Summary'!F5,Sheet1!$E$2:$E$500,"<>COMP",Sheet1!$E$2:$E$500,"<>CAN")


    above formula is working, Shall i use same for COMP formula below?

    =COUNTIFS(Sheet1!$H$2:$H$500,'Total Summary'!F5,Sheet1!$E$2:$E$500,"COMP")

    Dear Sir, Madam


    If I have several sheets in the Workbook, each one has a specific name but I don't Like and I want to Change. I want to search by name for each and re-name the sheet using MACRO to re-name all to a specific names for each sheet.


    How Can I do that?


    For Example ...


    [TABLE="width: 274"]

    [tr]


    [td]

    [TABLE="class: grid, width: 500, align: center"]

    [tr]


    [td]

    Current Project Manager Name (H)

    [/td]


    [td]

    Change To

    [/td]


    [/tr]


    [tr]


    [td]

    Mr Anoop Krishna Ravella

    [/td]


    [td]

    Anoop

    [/td]


    [/tr]


    [tr]


    [td]

    OGB - User

    [/td]


    [td]

    Saroj

    [/td]


    [/tr]


    [tr]


    [td]

    OGA Shift - Department User

    [/td]


    [td]

    Marlon

    [/td]


    [/tr]


    [/TABLE]
    etc....

    [/td]


    [/tr]


    [tr]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [/TABLE]

    Files

    • Data V3.xlsm

      (35.76 kB, downloaded 70 times, last: )

    Re: Return Total value from one sheet to another sheet depend on criteria ?


    KjBox,


    I tested mention formula and it is working fine, but however the only problem for example if I would have WO Status with "CAN" then this formula also will calculate everything except COMP & it is including CAN Status which I don't want.


    Will it work If I modify it not equal to comp or CAN ?

    Re: Return Total value from one sheet to another sheet depend on criteria ?


    KjBox,


    I tested this formula for Pending Projects and the output was as following after changing the line instead of 500 to 140005.


    Pending Projects 64


    Code
    1. [COLOR=#0000ff]=SUMPRODUCT((Sheet1!$H$2:$H$140005= 'Total Summary'!F5)*(Sheet1!$E$2:$E$140005={"WSCH","INPRG","APPR"}))

    [/COLOR]

    Re: Return Total value from one sheet to another sheet depend on criteria ?


    Carim,


    I tested formula for complete it worked fine, but for pending for example if data almost 500 it is working if I changed data to larger rows this is what happen:


    Example:


    Yahya is having 2592 Completed Projects , 86 Pending Projects.


    After using formula with this much of rows I got the following output :


    Completed Projects 92
    Pending Projects 1


    For the Completed I changed value of 500 to 14005, and the output was correct GIVEN 2592 . However for the Pending Projects I try it but it did not gave correct output GIVEN 64 ONLY why ?


    Complete Formula

    Code
    1. =SUMPRODUCT((Sheet1!$H$2:$H$14005='Total Summary'!F5)*(Sheet1!$E$2:$E$14005="COMP"))


    Pending Formula

    Code
    1. =SUMPRODUCT((Sheet1!$H$2:$H$14005='Total Summary'!F5)*(Sheet1!$E$2:$E$14005="WSCH")+(Sheet1!$H$2:$H$14005='Total Summary'!F5)*(Sheet1!$E$2:$E$14005="INPRG")+(Sheet1!$H$2:$H$14005='Total Summary'!F5)*(Sheet1!$E$2:$E$14005="APPR"))

    Re: Return Total value from one sheet to another sheet depend on criteria ?


    KjBox,


    I removed = sign but it didn't work still gave me 0 result.
    The code I used the following

    Code
    1. =COUNTIFS(Sheet1!E:E,"COMP",Sheet1!H:H,"Mr Anoop Krishna Ravella")


    Carim,


    I tested formula u mention and it worked, So this formula will search by Project Manager Name right in Colmun H and then check WO Status How Many Complete rights ?


    What If I want to do same formula but for non-complete projects. There are those status instead of complete for example :


    - WSCH
    - INPRG
    - APPR


    I try to use same formula but there is syntax error


    Code
    1. =SUMPRODUCT((Sheet1!$H$2:$H$500= 'Total Summary'!F5)*(Sheet1!$E$2:$E$500="WSCH","INPRG","APPR"))

    Re: Return Total value from one sheet to another sheet depend on criteria ?


    Dear NBVC,


    I tested mention formula over one workbook which I am currently working on it, I try to return total COMP or total pending for each owner.


    I use this formula as u suggest.


    Code
    1. =COUNTIFS(Sheet1!E:E,"COMP",Sheet1!H:H,"=Mr Anoop Krishna Ravella")


    I use the above code but it does not work, I am telling formula to check for me this owner Anoop how many times does he complete a project and to return that value to Summary page. Why the result is 0.

    Files

    • Data V3.xlsm

      (119 kB, downloaded 61 times, last: )

    Re: Separate data from each owner into separate sheet?


    Dear All,



    I have another question if u could help me it will be great, if not It will be fine. My question is.



    Q: After pasting data into Sheet 1 & run Split by Project Manager Function; is it possible in the same time taking each owner name to the Summary Page and to calculate total completed (COMP) & Total Pending (WSCH+INPRG+APPR) from WO Status column?



    The Function I know to return values of occurrence from another sheet is the following:



    Code
    1. =COUNTIF('Sheet Name'!$K$3:$K$17,"COMP")
    2. =SUMPRODUCT(COUNTIFS('Sheet Name'!$K$3:$K$17,{“WSCH”,”INPRG”,”APPR”}))



    I know how to use the above formulas if for example I have already sheets available, but If I want it auto done by running split by project manager function this which I don’t know.



    I attached expected result should be showing as example in Summary sheet.



    Note: Thanks to KjBox which he is the one who done Split By Project Manager Function and works perfectly.

    Files

    • Data V3.xlsm

      (118.34 kB, downloaded 62 times, last: )

    Re: Separate data from each owner into separate sheet?


    I follow up the instruction u mention, And I got an error message indicating


    Run-time error '1004':


    You typed an invalid name for a sheet or chart. Make sure that:


    * The name that you type does not exceed 31 characters.
    * The name does not contain any of the following characters: : \ / ?
    [ or ]
    * You did not leave the name blank.


    After clicking debug it highlight yellow color this line


    Code
    1. Sheets(Sheets.Count).Name = kee


    &&&


    The whole code in the Module 1 is :



    I think that happens due to WO Owner Name is a lot charachters than Project Manager Name right ?

    Re: Separate data from each owner into separate sheet?


    KjBox,


    Thank you it worked perfectly, Just a simple question as I know it is separate by Project Manager Name, If I wanted to change it by myself to split by something else like for example WO Owner Name I should change which Line in Code ?


    This is only for my knowledge since I want to learn lol.

    Re: Separate data from each owner into separate sheet?


    Dear KjBox


    Thank you for the attachment, I checked your workbook and I got the following yellow error message.


    Code
    1. .[a2].Resize(UBound(oDic.Item(kee), 2), 14) = Application.Transpose(oDic.Item(kee))


    The steps I follow as the following:


    I copy my original data and paste it into Sheet 1. (I did not change or remove the headers existed in Sheet 1).


    Almost 8676 rows been pasted into Sheet 1.


    After running Macro I found split done for Yahya Splited, OGA Shift Splited, Anoop showing Empty Form, the rest did not split).


    After decreasing the amount of rows, it worked again.


    Kindly advise if I did any wrong.


    Note: I could not attach original data due to size limitation of website, even though after archive into Zip file still large as 2.67MB