Macros code
Closed
Gokulcgs
Posts
3
Registration date
Wednesday January 7, 2015
Status
Member
Last seen
January 8, 2015
-
Jan 7, 2015 at 03:31 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Feb 3, 2015 at 12:08 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Feb 3, 2015 at 12:08 PM
Related:
- Macros code
- Samsung keypad reset code - Guide
- Battery reset code - Guide
- Samsung volume increase code - Guide
- How to get whatsapp verification code online - Guide
- Macros in excel download - Download - Spreadsheets
3 responses
You should post your macro so we can see what you have already. It will help to debug it.
As it is, ANYTHING could be causing it to fail.
As it is, ANYTHING could be causing it to fail.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jan 20, 2015 at 12:01 PM
Jan 20, 2015 at 12:01 PM
Hi Gokulcgs,
No need for macro's so far (I'm at point 3).
Sheet "Daily Trend Analysiz".
C5: =SUM(('Feasibility Tracker'!$B:$B=$B5)*('Feasibility Tracker'!J:J=C$4))
This is an array formula and needs to be confirmed by hitting Ctrl+Shift+Enter, when done correctly the formula will be enclosed with curly brackets.
D5: drag the formula from C5 here.
E5: =C5+D5
F5: =SUM(('Daily Tracker'!$B:$B=$B5)*(('Daily Tracker'!$V:$V="Withdrawn")+('Daily Tracker'!$V:$V="Completed"))*('Daily Tracker'!I:I=F$4))
This is also an array formula.
G5: Drag the formula from F5 here.
H5: =F5+G5
Now select all the above formula's and drag them down as far as needed.
Taking a quick look at the rest of your points, it seems the same kind of formula's would do the job.
So give it a try and let me know if you get stuck.
Best regards,
Trowa
No need for macro's so far (I'm at point 3).
Sheet "Daily Trend Analysiz".
C5: =SUM(('Feasibility Tracker'!$B:$B=$B5)*('Feasibility Tracker'!J:J=C$4))
This is an array formula and needs to be confirmed by hitting Ctrl+Shift+Enter, when done correctly the formula will be enclosed with curly brackets.
D5: drag the formula from C5 here.
E5: =C5+D5
F5: =SUM(('Daily Tracker'!$B:$B=$B5)*(('Daily Tracker'!$V:$V="Withdrawn")+('Daily Tracker'!$V:$V="Completed"))*('Daily Tracker'!I:I=F$4))
This is also an array formula.
G5: Drag the formula from F5 here.
H5: =F5+G5
Now select all the above formula's and drag them down as far as needed.
Taking a quick look at the rest of your points, it seems the same kind of formula's would do the job.
So give it a try and let me know if you get stuck.
Best regards,
Trowa
Hi TrowaD,
Wow Worked!!!
Really appreciate your efforts on this work. Love your work!!! Thanks a heap!!!
A last request to you with regard to same spreadsheet,
In pending Tracker, we have to update the day by day pending orders alone.
so the source should come from Daily tracker.
Each days pending order should be updated in pending tracker and duplication should be removed. Also same day's completed order should be checked in pending tracker, if the order is exist then it should removed from pending tracker.
For example:
Daily tracker:
Date 25/01/2015
order - 123456
status - Pending
order - 987456
status - completed
In pending tracker:
order - 123456 is to be REPLACED if already existing - if not then the order should be ADDED NEWLY.
order - 987456 is to be DELETED if already existing - if not then no action required.
Thanks in advance.
Wow Worked!!!
Really appreciate your efforts on this work. Love your work!!! Thanks a heap!!!
A last request to you with regard to same spreadsheet,
In pending Tracker, we have to update the day by day pending orders alone.
so the source should come from Daily tracker.
Each days pending order should be updated in pending tracker and duplication should be removed. Also same day's completed order should be checked in pending tracker, if the order is exist then it should removed from pending tracker.
For example:
Daily tracker:
Date 25/01/2015
order - 123456
status - Pending
order - 987456
status - completed
In pending tracker:
order - 123456 is to be REPLACED if already existing - if not then the order should be ADDED NEWLY.
order - 987456 is to be DELETED if already existing - if not then no action required.
Thanks in advance.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jan 27, 2015 at 12:03 PM
Jan 27, 2015 at 12:03 PM
Hi Gokulcgs,
Good to see it worked out for you.
In regards to your new request, it is unclear to me which data from Daily tracker should go to Pending tracker, since the heading don't match.
Something like:
Daily tracker - Column B (Date) --> Pending tracker - Column D (Order created)
Could you do this for all necessary columns?
Since this one needs to be done with macro, let me ask you in advance if you know how to implement and use a macro code?
Best regards,
Trowa
Good to see it worked out for you.
In regards to your new request, it is unclear to me which data from Daily tracker should go to Pending tracker, since the heading don't match.
Something like:
Daily tracker - Column B (Date) --> Pending tracker - Column D (Order created)
Could you do this for all necessary columns?
Since this one needs to be done with macro, let me ask you in advance if you know how to implement and use a macro code?
Best regards,
Trowa
Gokulcgs
>
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
Jan 27, 2015 at 07:42 PM
Jan 27, 2015 at 07:42 PM
Hi TrowaD,
My apology for giving you unclear request.
Daily tracker - Column C (NNN) --> Pending tracker - Column B (NNN Orders)
Daily tracker - Column D (Product Type) --> Pending tracker - Column C (Product)
Daily tracker - Column F (NNN logged date and time) --> Pending tracker - Column D (Order created)
Daily tracker - Column P (Pending Time) --> Pending tracker - Column E (Order Recently Parked)
Daily tracker - Column U (Enginner) --> Pending tracker - Column G (Enginner)
Daily tracker - Column V (Current NNN Status) --> Pending tracker - Column H (Status of the NNN)
*********************************************************************************************************
In pending Tracker, we have to update the day by day pending orders alone.
so the source should come from Daily tracker.
Each days pending order should be updated in pending tracker and duplication should be removed. Also same day's completed order should be checked in pending tracker, if the order is exist then it should removed from pending tracker.
-------------------------------------------------------------------------------------------------------------------------------
Pending orders --- is to be REPLACED if already existing - if not then the order should be ADDED NEWLY.
Completed orders --- is to be DELETED if already existing - if not then no action required.
-------------------------------------------------------------------------------------------------------------------------------
For example:
Daily tracker:
Date 25/01/2015
order - 123456
status - Pending
order - 987456
status - completed
In pending tracker:
order - 123456(pending) is to be REPLACED if already existing - if not then the order should be ADDED NEWLY.
order - 987456(completed) is to be DELETED if already existing - if not then no action required.
Thanks in advance, appreciate your help as always:)
My apology for giving you unclear request.
Daily tracker - Column C (NNN) --> Pending tracker - Column B (NNN Orders)
Daily tracker - Column D (Product Type) --> Pending tracker - Column C (Product)
Daily tracker - Column F (NNN logged date and time) --> Pending tracker - Column D (Order created)
Daily tracker - Column P (Pending Time) --> Pending tracker - Column E (Order Recently Parked)
Daily tracker - Column U (Enginner) --> Pending tracker - Column G (Enginner)
Daily tracker - Column V (Current NNN Status) --> Pending tracker - Column H (Status of the NNN)
*********************************************************************************************************
In pending Tracker, we have to update the day by day pending orders alone.
so the source should come from Daily tracker.
Each days pending order should be updated in pending tracker and duplication should be removed. Also same day's completed order should be checked in pending tracker, if the order is exist then it should removed from pending tracker.
-------------------------------------------------------------------------------------------------------------------------------
Pending orders --- is to be REPLACED if already existing - if not then the order should be ADDED NEWLY.
Completed orders --- is to be DELETED if already existing - if not then no action required.
-------------------------------------------------------------------------------------------------------------------------------
For example:
Daily tracker:
Date 25/01/2015
order - 123456
status - Pending
order - 987456
status - completed
In pending tracker:
order - 123456(pending) is to be REPLACED if already existing - if not then the order should be ADDED NEWLY.
order - 987456(completed) is to be DELETED if already existing - if not then no action required.
Thanks in advance, appreciate your help as always:)
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Feb 3, 2015 at 12:08 PM
Feb 3, 2015 at 12:08 PM
Hi Gokulcgs,
Yes, I have got a code for you to try.
But I noticed some duplicates in the order numbers. Not sure if that is there on purpose or that you were testing something for yourself.
Your Column A row numbers may get messed up because of the deletion of completed orders. May I advise you to use a formula for that: =ROW(A2)-1 (to be placed in A2). Drag the formula down as far as desired. Or a more clean version: =IF(B2<>"",ROW(A2)-1,"")
Let me know how the code is to your liking:
Best regards,
Trowa
Yes, I have got a code for you to try.
But I noticed some duplicates in the order numbers. Not sure if that is there on purpose or that you were testing something for yourself.
Your Column A row numbers may get messed up because of the deletion of completed orders. May I advise you to use a formula for that: =ROW(A2)-1 (to be placed in A2). Drag the formula down as far as desired. Or a more clean version: =IF(B2<>"",ROW(A2)-1,"")
Let me know how the code is to your liking:
Sub RunMe()
Dim lRow, lRow2, lRow3 As Integer
Dim fOrder As Range
Sheets("Daily Tracker").Select
lRow = Range("C" & Rows.Count).End(xlUp).Row
lRow2 = Sheets("Pending Tracker").Range("B" & Rows.Count).End(xlUp).Row
For Each cell In Range("C2:C" & lRow)
If cell.Offset(0, 19) = "Pending" Then
With Sheets("Pending Tracker").Range("B2:B" & lRow2)
Set fOrder = .Find(cell.Value, LookIn:=xlValues)
If fOrder Is Nothing Then
lRow3 = Sheets("Pending Tracker").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row
Sheets("Pending Tracker").Cells(lRow3, "B") = cell.Value
Sheets("Pending Tracker").Cells(lRow3, "C") = cell.Offset(0, 1).Value
Sheets("Pending Tracker").Cells(lRow3, "D") = cell.Offset(0, 3).Value
Sheets("Pending Tracker").Cells(lRow3, "E") = cell.Offset(0, 13).Value
Sheets("Pending Tracker").Cells(lRow3, "G") = cell.Offset(0, 18).Value
Sheets("Pending Tracker").Cells(lRow3, "H") = cell.Offset(0, 19).Value
Else
Sheets("Pending Tracker").Cells(fOrder.Row, "C") = cell.Offset(0, 1).Value
Sheets("Pending Tracker").Cells(fOrder.Row, "D") = cell.Offset(0, 3).Value
Sheets("Pending Tracker").Cells(fOrder.Row, "E") = cell.Offset(0, 13).Value
Sheets("Pending Tracker").Cells(fOrder.Row, "G") = cell.Offset(0, 18).Value
Sheets("Pending Tracker").Cells(fOrder.Row, "H") = cell.Offset(0, 19).Value
End If
End With
ElseIf cell.Offset(0, 19) = "Completed" Then
With Sheets("Pending Tracker").Range("B2:B" & lRow2)
Set fOrder = .Find(cell.Value, LookIn:=xlValues)
If Not fOrder Is Nothing Then
fOrder.EntireRow.Delete
End If
End With
End If
Next cell
End Sub
Best regards,
Trowa
Jan 7, 2015 at 11:56 PM
First of all, I really thank you for responding my quiries.
How to attach my excel sheet here?
Jan 8, 2015 at 11:33 AM
Files can be shared using free file sharing sites like www.speedyshare.com or ge.tt. Once uploaded you will get a download link, which you then paste in your next post here. Always be careful with sensitive information.
Best regards,
Trowa
Jan 8, 2015 at 12:08 PM
I have attached the excel sheet..
I have no problem,if you can write a new macros code and I will tell you what is the actuall requirement.
1) In daily trend analysiz sheet, we require datas with respect to date column.
2) In incoming there are two type - Simplex and Complex
2)i)In simplex column, data should come from feasiblity tracker matching date and simplex - only count should be in daily trend analysiz
2)ii) same for complex column, data should come from feasiblity tracker matching date and complex - only count should be in daily trend analysiz
3)In closed per day column, two type - Simplex and Complex
3)i)for this we have go to daily tracker - filter date, FNC status - only should be withdrawn and completed and count number of simplex and complex and pasting the count in daily trend analysiz respective coulms with date.
4)In Pending per day column,
4)i)for this we have go to daily tracker - filter date, FNC status - only should be In-progress and pending and count number of simplex and complex and pasting the count in daily trend analysiz respective coulms with date.
5)Pending till date - total count in pending tracker
6)out of hours - simplex and complex
6)i)for this we have go to daily tracker - filter date, FNC status column - only should be completed, Out of Scope/ Out of Hour column - only OOH and count number of simplex and complex and pasting the count in daily trend analysiz respective coulms with date.
7)Out of Scope - simplex and complex
7)i)for this we have go to daily tracker - filter date, FNC status - only should be Re-allocted and count number of simplex and complex and pasting the count in daily trend analysiz respective coulms with date.
8)Simplex ( 4 Hrs ) - Met SPT - value should come from closed per day - simplex Missed SPT - should always be zero
9Complex ( 4 Hrs ) - Met SPT - value should come from closed per day - complex Missed SPT - should always be zero
Above all requires only counts(number of orders per day) in Daily trend analysiz.
The same should come day by day with Macros..
I know its long,but really I would appreciate if this can solved...Hoping the best result..
Thanks in advance.