Component Life Cycle Management - How To Extract Correct Data
Apr 30, 2013
I am trying to create a life-cycle management sheet that is simple for the user but gives specific data.
The user would input "Date", "Machine type", "Component Set No.", and "Component". This would happen over a period of time and build up a picture of how often each component was lasting and it's average life in days.
I am fine from the user input side but am struggling to figure out how to extract ONLY the data I need to display in a graph.
For example: If I want to see the average no. of days "component X" is lasting before being replaced I've not been able to extract just that data. I have tried using a pivot chart but this won't do exactly what I'm after.
See the attached for a clear explanation : Component LifeCycle Example.xlsx
View 5 Replies
ADVERTISEMENT
Jul 4, 2014
I'm try to do a what IF Formula, so I can automatically add the cost into the right year for a lifecycle cost
My criteria is in column K I have either New Build or Existing and column AT I have year 12 of the lifecycle and column AZ I have year 18
And what I want the result to say is IF Column K Say New build I want the value of the first maintenance year to start in year 18 and column k say Existing I want the value of fist year Maintenance to start in Year 12
I already have this formula in the Column AT&AZ working out he value
IF(ISERROR(MROUND((AQ$1-$Q2),$P2)),"0",
IF((MROUND((AQ$1-$Q2),$P2)+$Q2) = AQ$1,$AF2,"0"))
View 4 Replies
View Related
Apr 4, 2013
My goal is to learn how to universally add specific codes to all clients in one shot.
Here's an example of the data I'm working with......
In column A - I currently have a list of 200 unique clients listed multiple times (1000 rows).
In column B - I have several different types of codes, but that are the same for each client.
Example:
A B
Client 1 Code A
Client 1 Code B
Client 1 Code C
Client 2 Code A
Client 2 Code B
Client 2 Code C
Etc. Etc. for 200 clients
So the question is: When adding a new code, How do I add a codes to all clients in one shot?
View 1 Replies
View Related
Jan 26, 2014
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################
View 4 Replies
View Related
Oct 25, 2012
On a daily basis we have to manually fill in a time sheet, and I was trying to think of a way where I could just copy and paste it into excel and it would show me on another sheet the total amount of hours I have worked over the month. This gets slightly more difficult, as different clients have different campaigns, and you can work on any variety during the day (however, not all of them do).
Typical raw data looks like this:
Client
Hours
Campaign
Rubber gloves
4.1
SEO
[Code] ....
Therefore I would like to combine the data and then send it off to another report that looks like this:
Rubber gloves - SEO
Rubber Gloves - PR
Bottled Water
1/12/12
4.1
2.3
2.8
[Code] .......
This would allow me to keep a check of how many hours I am working on each campaign over the month, and ensure that I don't run over on time. I have the boxes prepared for the 2/12/12, 3/12/12, etc. where I paste the raw data, and let excel break it down and put it in the report automatically.
I have been entering the data manually so far and I'd quite like to avoid a pivot table or chart (but i am open to it).
View 3 Replies
View Related
Jan 23, 2006
I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
View 14 Replies
View Related
Feb 19, 2008
I have a data set that contains a repeating cycle. I want excel to find the first and second occurrence of the data crossing the zero axis and then report the time between the zero crossings. The data is not purely in ascending or descending order and often just changes from positive to negative or negative to positive without hitting the actual 0 point.
View 3 Replies
View Related
Feb 15, 2013
I have a list of Users in Column A on Sheet 2 and a list of Extension Numbers in Column B.
I'm looking for a way to populate a Cell (F5) with a User and Cell (H5) with the Extension number. I then enter Data into Cell (G3), (G9) and (G10). I would then on enter on Cell G10 update the relevant columns in Sheet 2 and move on to the next user.
View 7 Replies
View Related
Oct 10, 2008
I have no idea why I am getting the #VALUE! message...
Any light shed on this would be much appricated, or an alernative formula.
View 5 Replies
View Related
May 15, 2014
I am trying to figure out a formula to calculate remaining useful life. I have the following information:
In Service Date (Cell W2)
Estimated Life (Cell X2)
Aquired Value (Cell Z2)
Is there a formula that will just calculate how much life is left based on the information i have?
View 4 Replies
View Related
Jun 20, 2009
I price life insurance policies and need a formalu for the following new Fund:
If the sum of the first two years of premium added to 10% of that total is equal to or less than 25% of the face amount, the case fits into the general parameter.
Here is what I have, but it isn't working:
=IF((U2+V2)*10%)+(U2+V2))
View 9 Replies
View Related
Jan 26, 2007
I use a scanner that initiates a f2 command and trips my macro. I would like to use multiple scanners using a 8 port hub but I am having trouble with a scanner starting my macro and another scanner dumping data into my macro before the first one is finished. This is dropping scanned data into my macros. Is there a way to prevent the scan from accepting data until the macro has completed its cycle.
View 2 Replies
View Related
Feb 15, 2010
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View 4 Replies
View Related
Jul 3, 2008
I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.
I need to see how many items processed for each set per day.
Example:
[url]
The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
View 14 Replies
View Related
Dec 18, 2008
First, ignore the colored portions. This is how I kept track of what I had completed in the form as I created it. Clients attend class once a week and pay once a week for their classes. I'm taking this one step at a time. At the initial interview of a particular client we will determine the payment per session due, and we will enter this amount in the column labeled "payment per session. We also need to keep track of how many sessions they have paid for. So in short, I'm looking for the following:
When "amount paid" equals "payment per session" set "minimum payment due" to $0 and increase "classes paid" by +1
I figured out how to enter the payment and return the minimum payment due to "0" but how can I make the "classes paid" increase by 1 every time the minimum payment equals "0"?
View 5 Replies
View Related
May 2, 2009
I have 143 macros, but I put 8 of them here b/c the post would not go through with all of them. I couldn't fit all of them on an attachment either b/c the file size was too big. However, I have this goal I have been working on for the last three months on the weekends and the last part.
I would like help with a macro that I can add onto the ones I have. I would like the macro to activate and cause a message box to appear when I open the excel doc. I would like also to be able to activate the message box and its associated macros after the document has been opened.
I would like the message box to have
- Message “The Current Time is (Macro puts current computer time here)”
- Question “How much time do you have?” then beneath this question
- Blank field labeled Hour to give answer
- Blank field labeled Minute to give answer
Depending on the current time on the computer as well as the hour and minute entered, a macro with a matching title from the list below will be selected that fits that criterion. For example, let’s say it is 5:30am and I entered 1 hr and 15 min in the empty fields of the message box. As a result the macro named “Sub Hour1Min15time530am” will be activated. Also due to the fact that the macros I have are in 15 min intervals the code would need to round to the nearest time, so if it was 5:33am when I ran the code the macro would default to 5:30am whereas if it was 5:39am when I ran the code the macro would default to 5:45am.
View 7 Replies
View Related
Feb 22, 2010
Assuming the below performance report:
Day 1
Actual 1
Target 1
Achieve 1/1 = 100%
Day 2
Actual 1
Target 1
Achieve 1/1 = 100%
Day 3
Actual 9
Target 10
Achieve 9/10 = 90%
Which method do you use to report the Achievement for the 3 days?
1. Do you take the average of 100%,100%,90% to give 97%
OR
2. Do you sum Actual (Day1+Day2+Day3 = 11) / Target (Day1+Day2+Day3 =12) to give 92%
View 4 Replies
View Related
May 7, 2008
I am working on a Property Management spreadsheet to track the availability of vacant units (and upcoming vacant units). I'm trying to write a formula to count the number of current units that are listed as vacant, so that I can ultimately report on the overall unit availability as of any given period (today, 15, 30, 60, 90-days out). All of the data in this spreadsheet is dynamic and each field can change at any time. There are also a lot of blank fields as well. I am using Excel 2003.
Here are the fields/data that I am using for my analysis:
Column A - (Reporting Period Dates): A2 = 5/20/08, A3 = 6/5/08, A4 = 6/20/08, etc.
Column B - (Unit Status: V=Vacant, O=Occupied): B2 = V, B3 = V, B4 = V, B5 = V, B6 = V, B7 = V, B8 = O, B9 = O, B10 = O, B11 = O, B12 = O, B13 = O
Column C - (M/O Date. These dates represent when the current resident will move out) - Cells C2:C13 contain dates for when each tenant will move out
Column D - (M/I Date. These dates represent when the future resident will move in) - Cells D2:D13 contain dates when the future tenant will move in
If my reporting period is 5/20/08 (cell A2), then I need to calculate how many Vacant units (B2:B13) I will have as of 5/20. The formula will need to count all of the current Vacant units (B2:B13), plus it will also have to take into account any Move Outs (C2:C13) during the reporting period. For example, let's say we have an Occupied unit that is scheduled to move out on 5/15/08. The cell will show this as "O" but the formula will need to determine if the M/O date is <= the Reporting Date of 5/20/08 (A2). If so, then Excel would count this unit as a Vacant for the period.
In addition to this, I also need the formula to look at upcoming Move IN dates (D2:D13). The formula will also have to determine if the MI date is <= the Reporting date (A2). If the MI date falls prior to the Reporting Date, then this unit should be considered as Occupied and should NOT be added to the total available units. NOTE - there are quite a few cells in column D that are blank and don't have MI dates. If there isn't a MI date, then the formula should assume that those units are Vacant if it is past the prior tenants MO date.
I'm assuming that Excel can handle multiple conditions like this, however, I am not smart enough to figure it out.
View 9 Replies
View Related
Dec 18, 2006
I'm using Excel to connect to an Oracle database using an HTTP connection.
I'm new to VBA and was wondering if it is possible to use cookies to keep the above sessions active?
View 5 Replies
View Related
Feb 24, 2013
I am required to fill up a sheet and provide to our Work force management team every week and it takes a lot of time from my day. I know this can be done easily with a code but I don't have enough experience with vb so can't write a good one myself
So here it goes -
I have a roster for my team (e.g. - sheet 2 "Roster") team members are required to work for 8 hours each day in their designated shift.
The codes in roster correspond to specific shift start time (e.g. - sheet 1 "codes")
Sheet - [URL]
I am required to fill up the sheet 3 "Staffing" each week for each half hour interval of a day that an advisor would be present for. So that means, I have to fill up 20 "P" for each member who is working on day from the time he would be starting his shift.
View 3 Replies
View Related
Jun 25, 2009
Is it possible to modify the windows power settings in excel vba. I have this example, but it doesn't do anything.
Sub testPOWERCFG()
CreateObject("WScript.Shell").Run "POWERCFG /CREATE Custom1", 0, False
CreateObject("WScript.Shell").Run "POWERCFG /CHANGE Custom1 /monitor-timeout-ac 15", 0, False
CreateObject("WScript.Shell").Run "POWERCFG /CHANGE Custom1 /monitor-timeout-dc 10", 0, False
CreateObject("WScript.Shell").Run "POWERCFG /SETACTIVE Custom1", 0, False
End Sub
I need some code to create and activate a scheme whereby everything is on and also some code to revert back to the default settings on the machine.
View 3 Replies
View Related
Nov 29, 2009
I keep getting this error message: Error 429: ActiveX Component Cannot Create Object
I fond a site that explained the problem: "If Microsoft Excel 97 is installed after an application created with Microsoft Visual Basic 5.0 that uses DAO is installed, Excel does not add the DAO design-time licensing key. This licensing key is required by Excel, but is not required by applications created with Visual Basic."
But I can not figure out how to fix it. I changed the registry and added the licensing key, but I still got the error.
View 3 Replies
View Related
Apr 15, 2005
I have been deleting some VBComponents by using the .remove command and they get tagged for deletion but do not actually get deleted until after my VBA sub ends and control is returned to excel.
I would like to save the changes before control is returned to the user. I'm not really sure why the deletion is being held up until execution ends, it even waits until all calling functions have also finished their execution.
View 9 Replies
View Related
Apr 2, 2007
I often come across a situation where I know a given total, and I know it comes from numbers within a given list, but I do not know which particular numbers they are. The onyl way to find out is to add every single combination of all the numbers in the list. I want to know if there is any formula within Excel which would help me to do that, given that the numbers of combinations are:
Number of items in list Number of combinations
1 1
2 3
3 7
4 15
5 31
6 63
7 127
8 255
9 511
10 1023
Obviously, I would like to be able to do this for any number of items.
View 4 Replies
View Related
Dec 10, 2013
I really need a comprehensive excel file which can manage my bank account id, savings, transactions, withdrawals, transfers etc etc. could be better if it can show any graphical interface with charts as well. I am willing to have a file so that i can manage more than one bank accounts. ( Excel 2003 is my version)
View 3 Replies
View Related
Jul 3, 2014
I need identifying correct title and corresponding company name. For this I need a UDF which looks for first occurrence of title having event or marketing or meeting word in data and if found the remove other titles and company names already separated with "/".
Sample Data
Producer, Target BTC@Periscope/Events Manager, Recognition Events@Minneapolis Park & Recreation Board/Event Producer@Events by JLS
Required Result
Events Manager, Recognition Events@Minneapolis Park & Recreation Board
Sample Data 2
Sales Associate@Teavana/Event Assistant@City of Saint Paul
Required Result 2
Event Assistant@City of Saint Paul
Sample Data 3
Sales and Catering Manager@Bunker Hills Golf Course at Town & Country Caterers/Marketing Coordinator@Town & Country Caterers
Required Result 3
Marketing Coordinator@Town & Country Caterers
View 3 Replies
View Related
Jul 1, 2009
I have this nested if statement, but my last IF statement needs to incorporate another component.
View 2 Replies
View Related
Feb 19, 2012
When I try to open an Excel-file from my employer with Excel UserForms I recieve following error messages
Code:
Component not correctly registered
and
Code:
Unexpected error (336)
I can see the code behind the UserForm, but not the UserForm. I've made the same mistake myself last time I created an UserForm, but I don't know what to do different.
View 2 Replies
View Related
Jul 25, 2007
I have a control sheet that needs to fetch numbers from a different worksheets depending on the worksheet name that I type into a cell. So if I type "sheet1" into cell A1 then I want the formulas on the sheet to automatically change fetch the data from sheet1. Then if I type in "sheet2" for example, the formula must then look at sheet2 for example. The source sheets are all identical in layout so not bothered about the actual cell ranges. I can do this in VBA but would like to keep this simple in Excel formulas.
View 2 Replies
View Related
Jul 14, 2009
I'm having trouble to get VLOOKUP to work. Basically I need to look up Management Teir information from Sheet1 onto Main sheet as per the file attached. Both RecordID columns are Text so they should work right? But VLOOKUP returns #NA error.
View 4 Replies
View Related
