Posts by Xani

Xani · Aug 25th 2017

Re: Separate data from each owner into separate sheet?

Allright let me check it and see if it works

Xani · Aug 25th 2017

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.

Xani · Aug 25th 2017

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

.[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

Xani · Aug 24th 2017

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

Thank you, works perfectly.

Xani · Aug 24th 2017

Hello,

I would like to return for example total value of COMP from Manufactured Status Cell in Cars Information Sheet to Summary Sheet, The rest status is the same.

Output Should Be as Following:

Total Cars Complete ( COMP): 3

Total Cars Pending (APPR+INPRG+WAPPR): 12

Total Canceled Cars(CAN): 3

Attached workbook for your reference.

Xani · Aug 20th 2017

Re: Need to calculate status of each owner?

AliGW

Code

=COUNTIFS(E:E,"COMP",K:K,K2)

I try above it work but calculate not correctly, If i do manual it will show wrong count.

Code

=IF(COUNTIF(K$2:K2,K2)>1,"",COUNTIFS(E:E,"COMP",K:K,K2))

It worked Ok.

Code

=IF(COUNTIF(K$2:K2,K2)>1,"",COUNTIFS(E:E,"<>COMP",K:K,K2))

It didn't work, It should calculate all values except for COMP

What I did is used Pivot Table and I manipluate the sheet.

Xani · Aug 20th 2017

Re: Separate data from each owner into separate sheet?

KjBox,

I checked your code with attached workbook, It worked with the current data, If i put the original data which it is a lot of rows it will highlight this code in yellow:

Code

With Sheets(yy(LBound(yy(0)))(i))

Carim,

Your code work , But I notice the following, After changing data with large data it will show additional sheets but without Project Manager Name, for example Sheet1, Sheet 2, Sheet 3 + The other problem is for example instead of having OGA Shift Department User in one sheet there is a lot of sheets differently but the owner is OGA Shift ! They should be all project manager same into one sheet , not separaetly.

For example
Project Manager A in one sheet
Project Manager B in one sheet

what happening is for example Project Manager A some rows and columns in one sheet, some of them in another sheets.

Xani · Aug 20th 2017

Re: Need to calculate status of each owner?

AliGW
I couldn't reply due to I have work and there was no time, Sorry.
I will try to check the code ASAP and give you feedback regarding output.

Xani · Aug 19th 2017

Re: Need to calculate status of each owner?

Hello,

I uploaded the spreadsheet again, I highlight the columns which I think there should be a formula to get the count of "Total Complete WO Per Owner" & "Total Open WO Per Owner"

I know I can do it manually using the filter of WO Status, But it is stressful is there a better way to do it than manual ?

I hopy my explanation and spreadsheet is clear enough

Xani · Aug 19th 2017

Re: Need to calculate status of each owner?

Dear AliGW,

Thank you for your reply, Please find attached file as you requested.
I hope It will helped.

Xani · Aug 19th 2017

Hello,
I want to write a formula to enable me calculate the statusof each completed (COMP) or open (APPR+COMP+INPRG+WAPPR) for each owner as sumof COMP or Open
Is that possible, and if how can I do that ? "I hope attached picture is large and can be seen"

Xani · Aug 18th 2017

Re: Separate data from each owner into separate sheet?

I try to apply below code over my sheet, The problem is that it only copy the data of "OGA Shift - Department User" to separate sheet, but for "Mr Salman Hasan Alaradi" did not copy the data to his sheet, why is that happen?

Sorry I could not upload my excel file due to size exceed current size allowed to upload excel files.

Code

Sub FilterAndPlaceData()


    'Declarations
    Dim shtSource        As Worksheet
    Dim shtDestination   As Worksheet
    Dim arrShts()        As Variant
    Dim shtLp            As Integer
    Dim filterCol        As Integer
    Dim lcol             As Integer
    Dim lrow             As Integer
    
    Set shtSource = Sheets("List of Work Orders") 'Define source worksheet
    
    arrShts = Array("OGA Shift - Department User", "Mr Salman Hasan Alaradi") 'Define destination worksheet names
    filterCol = 8 'Define Column to filter on....
    
    With shtSource
    shtSource.Activate 'Activate Sheet so filter will work properly
    lcol = .Cells(8, Columns.Count).End(xlToLeft).Column 'Define last column of source worksheet
    lrow = .Cells(Rows.Count, "A").End(xlUp).Row 'Define last row of source worksheet
    
        'Loop through names to filter on...
        For shtLp = 0 To UBound(arrShts) - 1
        
             Set shtDestination = Sheets(arrShts(shtLp))
             
                 With .Range(Cells(1, 1), Cells(lrow, lcol))
                     .AutoFilter filterCol, "=" & arrShts(shtLp) & "*" 'Filter on X Employee (Notice the asterisk as a wildcard)
                     .Offset(1).SpecialCells(xlCellTypeVisible).Copy shtDestination.Range("A2") 'Copy All Employee Data to Appropriate Worksheet
                     .AutoFilter 'UnFilter
                 End With
                         
         Next shtLp 'Go to Next Employee Name / Worksheet
     
    End With
    
    ActiveWorkbook.RefreshAll 'Refresh All Pivot Tables in workbook


End Sub

Display More

Xani · Aug 18th 2017

Re: Separate data from each owner into separate sheet?

Dear All,

I try to apply the above but I did not successed, Is it possible for someone to help me on my excel sheet?

Xani · Aug 15th 2017

Re: Separate data from each owner into separate sheet?

Ok I try to decrease size of workbook, Please find attached file.

What I am planning to do is the following:

To take specific columns from the data sheet and transfer it to each owner sheet in the below.

Example: Engineering Department - User data owner data should be transfer to Engineering Department - User sheet.

Columns needed:
[TABLE="width: 1622"]

[tr]

[TD="class: xl64, width: 98"]Work Order
[/TD]
[TD="class: xl64, width: 351"]Description
[/TD]
[TD="class: xl64, width: 64"]Work Type
[/TD]
[TD="class: xl64, width: 64"]WO Status
[/TD]
[TD="class: xl64, width: 166"]Name
[/TD]
[TD="class: xl64, width: 108"]Target Start[/TD]
[TD="class: xl64, width: 101"]Target Finish[/TD]
[TD="class: xl64, width: 108"]Scheduled Start[/TD]
[TD="class: xl64, width: 101"]Scheduled Finish[/TD]
[TD="class: xl64, width: 108"]Actual Start
[/TD]
[TD="class: xl64, width: 108"]Actual Finish
[/TD]
[TD="class: xl64, width: 108"]WOCreationDate
[/TD]
[TD="class: xl64, width: 76"]Record ID
[/TD]
[TD="class: xl64, width: 61"]IncStatus
[/TD]

[/tr]

[/TABLE]

Xani · Aug 15th 2017

Re: Separate data from each owner into separate sheet?

AlanSidman,

I agree with u I try to upload photo but I dont know why size is small, Also I try to upload workbook but size is large !!

Xani · Aug 15th 2017

Re: Separate data from each owner into separate sheet?

http://"http://www.ozgrid.com/forum/member.php?u=247596" mrmmickle1,
I am trying to apply the above code but I did not succeed, is it possible to share with me the template working with this code to understand it better?

Xani · Aug 15th 2017

Hello,

What shall I do to be able to separate data by each owner ?
For Example see original data export from system, I need to remove unwanted columns and then separate each data by owner as you can see in second photo.

Please advise shall I use Pivot Table, VLookup, or Micro ? and How can I use them ?

Xani · Aug 7th 2017

Hello,

This is a short annual leave plan excel sheet, I would like to know which staff is going out in annual leave and it is conflict with which other staff?

Attached file for your infomration.

[ATTACH=CONFIG]73392[/ATTACH]

Xani · Jul 27th 2017

Re: Calculate time if greater than specific time in excel?

Ok I will edit the formula, Thanks again for the help.

Xani · Jul 26th 2017

Re: Calculate time if greater than specific time in excel?

Ok noted, thanks for the help.