Conditionally Prepend Zero To Strings
Nov 23, 2009
I have a list of numbers that is imported and converted to text. I need to have all these strings beginning with zero "0" except the ones that begin with 1.
To summarize:
If the string's first caracter differs from "0"(zero) and differs from "1"(one) than prepend a "0"(zero). If the first caracter is 0(zero) or 1(one), leave the string as it is.
I am attaching a xls file with 2 columns: Column 1 is how the list is now and Column 2 is how I need it to be.
View 4 Replies
ADVERTISEMENT
Dec 20, 2012
I need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array
Essentially I imagine this involves the FIND function at some point.
Attached is an incredibly simplified example of what I'm looking for.
View 9 Replies
View Related
Dec 16, 2013
I have an excel worksheet with about 10K rows of data in column A.
I have also another list of data, about 200 rows of data, in column G.
I need to color each cell in column A that contains, anywere in the string, any of the data strings in column G.
Example:
in column A
row 1: info@duende.com
row 2: rasko@silvester.com
row 3: supportonline@fabius.com
row 4: myhelp@friday.com
in column G:
row 1: help
row 2: info
row 3: support
I need rows 1, 3 and 4 in column A to be colored.
View 9 Replies
View Related
Nov 29, 2012
I have a long row of data with words/numbers in each cell. Whenever I see a cell starting with W, I want to add up the value 2 cells to the right of that cell starting with W.
For example, if these following 9 cells in the same row read W1, 90, 100, M2, 90, 100, W2, 90, 40, whenever the cell starts with W, I want to sum up the value 2 cells to the right of that cell starting with W. In this case, I am adding up the values 2 cells to the right of W1 and W2, which is 100 + 40 = 140. How can I do that?
View 2 Replies
View Related
Dec 14, 2006
Is there a way to activate a Macro with a conditional in one of the cells? It's like this, I want that if the content of A2 changes to "True" then C2,D2 and E2 change to bold and the background color change to yellow. Is there a way to do this?
View 14 Replies
View Related
Mar 17, 2009
on the "all data" sheet, there are two rows that have a Y for file missing - these are to be marked, so that they may be focused on finding their file
currently, i am doing as such and then filtering by that column to print out a list, and would like a simplified solution (populate sheet "missing") with just those rows who have missing files - so that another user may easily view such data without having to fondle around with the master list
View 2 Replies
View Related
Apr 14, 2009
I'm building a complete part number reference for all service parts of copiers of a certain brand. Every row contains three columns; partnumber, description and machine type. Problem is, some parts are used in more than one machine. When the list is complete I want to do the next: If partnumber in (column A) is found in more than one row, I want to join the information of the other columns in two cells. (see attached example).
View 2 Replies
View Related
Oct 18, 2009
I have this file where i delete columns which are extra, in my real file most of the cells are formulas or links . Basically i need a macro which looks in row 4, and if it finds any zeros ( number 0 ) in the cell it deletes that whole column.
The zero is a indicator for me when i work on these files if it is needed or not. Included the file as an attachement.
View 2 Replies
View Related
Feb 23, 2008
I need to lock a cell (F21) if J26 is less than 100. Can't work out how in conditional formatting. Is there a different way?
This is what I need to do:
F21=(IF,J26<100,Locked,Unlocked)
View 10 Replies
View Related
Jun 2, 2009
Can I program column B to show a certain value depending on the presence of a string of text in column A? For example, if A1 contains the word "Barbie", then cell B1 shows the value: "Ken"
View 5 Replies
View Related
Feb 2, 2010
I have a spreadsheet which has general usage and summary data at the top for ease of use. Begining at Row 15 the data is a standard table which includes columns for the following.
Cust Name | Status | Loan Amt | etc.
I have created a macro to make it easier to add new data rows at the top of the data already in the file (this makes it easier to view with the newest data at the top). I would like to create a conditional sum of the data in "Loan Amt" if the value in "Status" ="Active". I had no problem achieving this with the conditional sum tool but the problem is that the range changes whenever a new row is added to the top of the list. Unfortunately the range in the conditional sum adjusts as the old data shifts down for the new row and the sum does not contain the new row data.
View 2 Replies
View Related
Jan 4, 2012
I do have a cash-sale & cash-deposit template as follows.
The cash from the sales proceed needs to be deposited to bank on the next working day but in reality the same is not happening.
The stores are depositing the cash for more than one days sale and then the same is getting submitted to bank.
I would like to populate the Dates(in the "Desired Result" column from D2:D11).
The desired formula needs to check the non-blank cell in "C" column and then fill in the dates in the "D" column.
For example the sales amount from 1st-3rd December is deposited on 3rd December and hence the desired result across D4:D2 needs to be 3rd December.
Similarly the sales amount from 4th-7th December is deposited on 7th December and hence the desired result across D8:D5 needs to be 7th December.
ABCD1Date Of SaleSale AmountDeposit AmountDesired Result212/1/201157335 12/3/2011312/2/201128879 12/3/2011412/3/20112328411883312/3/2011512/4/20116294 12/7/2011612/5/20116894 12/7/2011712/6/201138270 12/7/2011812/7/2011262787474212/7/2011912/8/201133308 12/10/20111012/9/20119279 12/10/20111112/10/201166956886512/10/201112Total236516262440
Spreadsheet FormulasCellFormulaB12=SUM(B2:B11)C12=SUM(C2:C11)
View 2 Replies
View Related
Aug 9, 2013
How I can calculate the average fix time for each error code. The fix time will not be counted if it doesn't have a fix time. The distance between errors is not always the same, and the fix time is not the same.
How can I:
-Search through the Fix time and essentially add one to the count (only when it requires fixing)
-Only count the fix time for the previous error that has occurred.
-I have tried different combinations of using the MATCH and INDEX formula but have only received errors
-I can add additional calculation cells
-I can use VBA(although it will add to the already fairly long run time)
Error Code
1
2
3
4
5
6
Error Count
1
0
2
1
0
1
Avg Fix Time
Fix Time
Error Code
1
3
4
0:32:21
3
6
0:05:26
View 2 Replies
View Related
May 13, 2009
I want to add to it so that if the "Name" entered into the target cell is longer then the maximum allowd length it inserts an abreviation of the words (and increments where nessisary)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo StandardName
If Target.Address = "$B$4" Then Sh.Name = Target
Exit Sub
StandardName:
Sh.Name = "Sheet1"
End Sub
View 9 Replies
View Related
Feb 5, 2010
I want to create a 'virtual switch' so that if I change one cell from 1 to 0, that a formula in another cell becomes active. I can't just have the formula cell * the 1/0 cell, I need to comment out the formula or it draws in too much data.
View 5 Replies
View Related
Jun 21, 2006
I have what on the outset seemed a fairly basic task. In VBA I wanted to conditionally hide worksheets based on a value in my main worksheet.
I have 1 "main interface" worksheet with quite a few "result" worksheets as part of my workbook. Instead of scrolling left and right in the tabs menu everytime I wanted to veiw a particular worksheet I wanted to rather hide all the worksheets except the "main interface" and the relevant "result worksheet", which excel would identify based on a value in my main worksheet.
I wrote the following VBA code to do this but for some reason I cannot get it to work correctly. Perhaps I have made a simple logic error? I am fairly new to VBA. Any advice? ......
View 9 Replies
View Related
Dec 12, 2006
I have a workbook (attached) which has two sheets called “Team List” & “Masterfile Data Dump”.
TEAM LIST
This is the sheet that I want to populate when the data dump is a pasted into the sheet “Masterfile Data Dump”
MASTERFILE DATA DUMP
This sheet is where my data dump will be pasted to, columns A to Q and column R is a formula to work out the FTE
Calculation
The reason I want the list to auto populate is that it will change week on week and is a time consuming task keep updating it. I want to be able to type a Team Leaders Name into cell F2 and it populate the list of the agents in that persons team Cells F4:G18 (Name & Surname) along with there FTE (full time equivalent) in Cells I4:I18.
The problem I have is I have no idea how to calculate the FTE sum as the data dump can have one to five shift patterns (rows) for one agent (See my example on the “Master file Data Dump” Sheet of Joe Bloggs. /I have tried to use a VLOOKUP which I have used but this will only return the first value or first rows value of that agents name.
View 4 Replies
View Related
Dec 14, 2006
If you look in the file you will see a records sheets and a form sheet. I want to see if a record has been broken. All the times are in seconds and the distances are in centimeters. How can I find a certain row, compare and act based on an if statment?
View 2 Replies
View Related
Oct 12, 2007
I am currently using the following code to hide each row when there is no data between a certain column range. I need to add to this code so that it will also hide each column when there is no date between the row range.
Private Sub Worksheet_Activate()
Dim HiddenRow&, RowRange As Range, RowRangeValue&
ActiveWindow.DisplayZeros = False
Application. ScreenUpdating = False
For HiddenRow = FirstRow To LastRow
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)
RowRangeValue = Application.Sum(RowRange.Value)
If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If
Next HiddenRow
Application.ScreenUpdating = True
End Sub
View 3 Replies
View Related
Dec 4, 2007
Attach is a Demo –Sample file
The formatting etc is same for real file – Note that the real file will have up to 65000 rows of data like this.
My Problem is to remove conditionally some duplicate rows - however my difficulty trying to thinking about this is that I cant use a unique value comparison as the columns I to L they all are X s so when a X is present in columns I to L need to be conditional on comparing values in other columns
What is needed is to repeat remove duplications in each of the four columns I to L (titled E1, E2, E3, and E4 ) when there is a match of string in columns F, G and M – but don’t delete if on same row there is a value in columns O to S (E5 to E9).
In the demo file – I have highlighted in yellow the rows that would be removed if the code works.
So;
IF there is an “X” in column “I” (E1) and the string in column F (personnel number) is the same as row below it, and the string in column G (person name) is same as below it, and the string in Column M (Team number) is same as row below it, and there are NO values in columns O to S (E5 to E9) on same row – Then delete the duplicate/s – retain only one.
Repeat for columns J, K and L (E2, E3 and E4)
View 4 Replies
View Related
Oct 9, 2007
I would like to display 50 sequences, S_1 ..S_50, with stacked bars. Every sequence can contain 5 different events, E_1 .. E_5, in a random order. Every event has a certain duration, t. Lets say the first two sequences are as follows (duration time is in brackets)
S_1:
E_3(200), E_1(150), E_2(300), E_4(500), E_5(300)
S_2:
E_1(200), E_4(150), E_1(300), E_5(500), E_2(300), E_2(300)
...
How do I get Excel to display all Sequences in one graph as a function of time, with the bar-stack-color displaying the kind of event. So if E_3 would be red and E_1 blue we would see
S_1:
red, blue ...
S_2:
blue, ...
View 5 Replies
View Related
Jun 4, 2014
I am attempting to create a staffing calendar that will use conditional formatting to alert the coordinator by highlighting the cell when certain thresholds are reached.
The spreadsheet has three tabs, with 'Master' being the final copy.
In the 'Magic' tab, I was able to get it work with a rolling count but I am having trouble recreating it when the count is stationary and not updating itself line by line aside the person's name.
What I'm preferably looking to have happen is for the 'Name' to light up when it appears more than 3 times in a week in the Day fields, more than 1 time a week in the O/C field and more than 7 times a month in the Night field.
View 2 Replies
View Related
Jan 14, 2009
the attached document. I wish to try and conditionally format Sheet1!D:D depending on if Sheet2!E:E displays a particular letter, when; Matching Sheet1!A:A to Sheet2!A:A Matching Sheet1!B:B if <11 to Sheet2!B:B where cell equals text "Small (7-10)" if Sheet1!B:B is >10 then where Sheet2!B:B equals text "Mid - Large (11-16)"
Matching Sheet1!C:C if cell contains "Hcap" to Sheet2!C:C where cell equals "Handicaps". If Sheet1!C:C doesnt contain "Hcap" match to Sheet2!C:C where cell equals "All Races".
To use an example, if E:E in the cell adjacent to all the matches above equals N then the cell in Sheet1!D:D turns red. Cell D52 would become red. D88, D91, D92, D93 and D96 would also become red. Here was my attempt at the formula, no idea if I was close to the answer.
View 2 Replies
View Related
Apr 3, 2009
I used to know my way around Excel pretty well back in college, but I'm drawing a blank here several years later. I know there's a way to do this, but can't remember how.
In the attached sheet, I have info on Sheet 1. Sheet 2 only displays the info with LABEL=2. How can I make this sheet 2 automatically do this and update based on changes to Sheet1?
View 4 Replies
View Related
Oct 26, 2009
In column D I have an Expiry Date and in column E I put formula “=IF(D2<=TODAY()+90,"we have less than 90 days!!!","we have more than 90 days")” the formula works fine but my problem is I need formula if item have expired in column D its have to say in column E “ ITEM EXPIRED “ or something. I highlighted line in yellow below attached sheet.
View 4 Replies
View Related
Apr 8, 2014
I've got a dataset of certain trades across different markets and traders, and on different days. The date of the trade is in column A, the contract ID is column D and the trader ID is column J.
Traders do not trade every day. I would like to insert a new row/rows for each date on which traders do not trade between their first and last trade, for each market in which they participate.
Is this possible using macros?
And, if so, then I would like to insert the high and low prices for the market on those dates from a different worksheet into the newly created blank rows. On this second workbook the contract ID is column A, the date is column C, the high price is column F and the low price is column E. I would like the low price to be inserted into column AJ on the original worksheet and the high price into column AK.
View 2 Replies
View Related
Jul 18, 2014
I'm looking for a way to apply conditional formatting to a dynamic range, as the number of rows will vary each week.
Col A could be used as a reference, as it will always have a number in it, but wherever it stops is where I would need the conditionally formatted range to cut-off as well.
The actual range that would need formatting applied to it is Columns F-I where:
For any values < 1.00 (it should also cover blanks, which should be treated as zeros), red highlighting is applied & For any text that reads "Flagged" or "Not Reported", they would need to become bold
Additionally, I would like to start the formatting on Row 2 of the sheet in question, named "1010version"
View 3 Replies
View Related
Aug 8, 2008
I have 2 columns of data, apprx. ~25,000 rows.
Col 1 is user IDs and Col 2 is there status (pending, conditional, approved, rejected)
Col1 IDs are not unique because they can have multiple statuses associated with them in Col2. An ID can go from pending to conditional to either approved/rejected and all these are included in the raw data file. I want to remove all duplicate ID rows and keep the ID row with the last known status.
For example:
View 14 Replies
View Related
Jun 2, 2009
I have workbook that expands or shrinks in number of worksheets each time and I need to gather information from each worksheet to compile a "total" spreadsheet. The location of the cells to be copied in each worksheet vary however it is always in the same column. Managed to find a macro that will collect the information if it is located in the same cell, across all worksheets but did not manage to find something that will conditionaly copy.
Need code, that will search based on text that will be found in the worksheets and then copy the values from the column next to it (same row) to the "total" worksheet. If any of the text that is to be searched in the worksheet does not exist, then it should leave the cell "blank" in the "total". I have attached a workbook of with the example of the worksheets that I have and the result that I want to have at the end, in the "total" worksheet.
View 4 Replies
View Related
Jul 28, 2009
I have about a dozen tabs, all of which have vlookups in them and calculate based on what a user pastes into Sheet 1. I need a macro that hides all tabs that have no data, essentially tabs where A1 = ""
However, it also needs to unhide tabs as soon as there is data. So if the user pastes new data into Sheet 1 the vlookups, on the other sheets, still need to run (even if they are hidden) and then check against the macro to determine whether to be hidden or unhidden.
View 3 Replies
View Related
