Re: Combine Project Managers into separate sheets?
Dear nyatiaju,
What I mean for example After I run the split MACRO, It should combine ENG + Mr Ahmed Mokhtar Ahmed Soliman INTO one sheet called ENG, same goes for the rest as you will see in Name Change Sheet.
Because at the moment if I run the split MACRO it will split ENG into one sheet, split Mr Ahmed Mokhtar Ahmed Soliman into one sheet and so on.
Is that clear or not ?!
Re: Combine Project Managers into separate sheets?
Dear iwrk4dedpr,
Can you advise which line of code need to be edit in order to work what I want ?
Re: Combine Project Managers into separate sheets?
Dear iwrk4dedpr,
Thank you for your reply, the problem am not the one who code or familiar with this code.
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
Dear Sir, Madam
I would like to calculate date difference between Reported Date and Actual Finish.
Example: In the Duration Field output should be like
0 Day, 4 Hours, 0 Second
Attached file FYI.
Re: Combine Owners into one sheet and then re-name sheet?
Is this task difficult to do ? or maybe require a lot of time to do ?
Please find in the attached file modify version of previous file.
I hope someone can help me through this.
Re: Combine Owners into one sheet and then re-name sheet?
Up ...
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 Sheet – Column 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.
Re: Search and Change Sheets name to A Specific name for each Sheet ?
KjBox,
Works perfectely, thank you very much.
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"]
[TABLE="class: grid, width: 500, align: center"]
Current Project Manager Name (H)
Change To
Mr Anoop Krishna Ravella
Anoop
OGB - User
Saroj
OGA Shift - Department User
Marlon
[/TABLE]
etc....
[/TABLE]
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
[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
=SUMPRODUCT((Sheet1!$H$2:$H$14005='Total Summary'!F5)*(Sheet1!$E$2:$E$14005="COMP"))Pending Formula
=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
=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
=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.
=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.
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:
=COUNTIF('Sheet Name'!$K$3:$K$17,"COMP")
=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.
Re: Separate data from each owner into separate sheet?
Yes exactly, after changing same work order owners name to a few characters it worked perfectly
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
Sheets(Sheets.Count).Name = kee&&&
The whole code in the Module 1 is :
Option Explicit
Sub SplitByOwner()
Dim x, y(), z, kee, i As Long, ii As Long, iii As Long, iv As Long, oDic As Object
Set oDic = CreateObject("scripting.dictionary")
oDic.CompareMode = 1
x = Sheets("sheet1").Cells(1).CurrentRegion
For i = 2 To UBound(x, 1)
If Len(x(i, 10)) Then
kee = oDic.Item(x(i, 10))
If IsEmpty(kee) Then
For ii = 2 To UBound(x, 1)
If x(ii, 10) = x(i, 10) Then
iv = iv + 1: ReDim Preserve y(1 To 14, 1 To iv)
For iii = 1 To 3
y(iii, iv) = x(ii, iii)
Next
y(4, iv) = x(ii, 5): y(5, iv) = x(ii, 10): y(14, iv) = x(ii, 31)
For iii = 6 To 13
y(iii, iv) = x(ii, iii + 16)
Next
End If
Next
oDic.Item(x(i, 10)) = y
End If
End If
Erase y: iv = 0
Next
Application.ScreenUpdating = 0
For Each kee In oDic.keys
If Not SheetExists(CStr(kee)) Then
Sheets("Template").Copy , Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = kee
End If
z = TransposeArray(oDic.Item(kee))
With Sheets(kee)
.Rows(2).Resize(.UsedRange.Rows.Count).Delete
.[a2].Resize(UBound(z, 1), 14) = z
With .UsedRange.Offset(1)
.Columns(2).WrapText = True
.VerticalAlignment = -4108
End With
.Columns.AutoFit
End With
Next
Sheets("sheet1").Activate
Application.ScreenUpdating = 1
MsgBox "Data successfully copied to Owner Sheets.", 64, "Completed"
End Sub
Function SheetExists(sName As String) As Boolean
SheetExists = Evaluate("ISREF('" & sName & "'!A1)")
End Function
Function TransposeArray(Arr As Variant) As Variant
Dim temp, a As Long, b As Long, i As Long, ii As Long
i = UBound(Arr, 2): ii = UBound(Arr, 1)
ReDim temp(1 To i, 1 To ii)
For a = 1 To i
For b = 1 To ii
temp(a, b) = Arr(b, a)
Next
Next
TransposeArray = temp
End FunctionI think that happens due to WO Owner Name is a lot charachters than Project Manager Name right ?