Formatting From The Second Occurrence Of A Number
Dec 2, 2008
I have an worksheet where I use sequential numbers in column B but it often happens that we insert some more rows with the same number in the B column.
Ex.:
01
02
03
04
04
04
04
05
06
06
Now we would like to have a macro that will format the text color (change to white) in columns B & D as soon as there is a second (or more) occurrence(s) of the same preceding number.
Ex.:
01 – Black (unchanged)
02 – Black (unchanged)
03 – Black (unchanged)
04 – Black (unchanged)
04 – White (changed)
04 – White (changed)
04 – White (changed)
05 – Black (unchanged)
06 – Black (unchanged)
06 – White (changed)
View 9 Replies
ADVERTISEMENT
Dec 24, 2013
I'm getting an export from a CDR. This export contains the date and times people log on and off from a queue. For logging in they dial 511, for logging out they dial 512. They get a voice prompt and type their password. I need to know how much time they daily spend in this queue
At first this looks pretty easy. I just make a sum of all the times they called to 511, then a sum of all time they called to 512 and finally I substract those values and I end up with the correct time spend in the queue.
The problem comes when they call multiple times to 512 without actually logging off. For example, they type the wrong password or simply hang up.
Result is this in the CDR
FROM TO DATE TIME
101 511 23/12 08:34
101 512 23/12 11:58
101 511 23/12 12:34
101 512 23/12 14:45
101 512 23/12 14:47
101 512 23/12 15:00
The actual time spend in the queue is 5 hours and 50 minutes. But Excel calculates this as 35 hours and 22 minutes, because it counts the 512 values no matter what.
How can I make sure that Excel only calculates the values of they are either the last value in the row OR if they are preceded by 511?
View 2 Replies
View Related
Dec 15, 2008
What Formula will return the above request (Preferable as "simple" as possible) ...
View 13 Replies
View Related
Nov 24, 2009
In my worksheet, I have a column of ascending dates from BD7 to BD22, with BD22 being the current date. In column BN, I track whether a process is "Over" or "Under" a limit.
The values in column BN are text with either of those names.
I need to return two values in cells BO7 and BO8 respectively:
1) The number of consecutive days back from the most current day that have been "Over" the limit
2) The date going from the most current day that the first "Over" occurred
View 9 Replies
View Related
Aug 15, 2008
I've found Nth_Occurrence and it's frankly brilliant but I need to be able to return the occurrence number of a value in a range and it doesn't quite do what I need.
Using = COUNTIF(A$1:A1,A1) gives me exactly the answer I need but as I'm working with big ranges it's taking a lot of calculation time.
View 9 Replies
View Related
Sep 25, 2013
how to search a defined range (using namned range and offset to last non empty row) and register row number for the first occurance of each ID using VBA. I made an example file where I need to search column A (search range using namned range "ID" and offset to last non empty row) and register rownumber for the first occurance of each ID in Column C.
example.xlsm
View 1 Replies
View Related
Aug 14, 2014
I'm trying to extract all the numbers from the left of the delimiter ":" . They can occur once or more.
E.g. cell F2 contains BP2.2.1:40 BP2.2.1:50 BP2.2.1:60 BP2.2.1:70 BP2.4.1:80
and what i want as a result is : BP2.2.1 BP2.2.1 BP2.2.1 BP2.2.1 BP2.4.1.
I've tried =IFERROR(LEFT(F2,FIND(":",F2)-2), " ") but only displays 1 out of a possible 4 in the cell.
View 12 Replies
View Related
Feb 12, 2009
I wrote an excel program in Excel'03 for a dental office to manage state assistance patients and one of the table columns is the state assistance number. The problem is that the program defaults to a number format when it is entered instead of a text format, which is what I want. The issue is that state assistance numbers are always 8 digits and when it defaults to a number field the program drops the required preceeding zeros.
For example the number 00123456 will sometimes show up as 123456, which is wrong. It's like sometimes it will show up right and other times it gets a butterfly in its brain and deletes those zeros irritating the receptionist here. So, how do I get the table to either stay in text format or set up a number format that keeps the preceeding zeros?
View 3 Replies
View Related
Nov 28, 2008
I having a bit of trouble entering numbers in Excel, every time I try to enter a number it seems to divide it by 100. For example : - 1 entered in to a cell becomes 0.01, 100 becomes 1....etc. I've tried formatting the cells and had no luck, tried Tool>Options but not sure where to look.
View 2 Replies
View Related
Apr 28, 2012
Is it possible to assign a number format to a cell based on a different cell's value? For example, say cell value of A1 is "BB-". Can I make it where if I enter a "2" in cell A2 it will show "BB-2"?
View 9 Replies
View Related
Jun 17, 2008
I have an excel spreadsheet, that is 11,000 lines (give or take) and in column A, I have a sequence of numbers on every line set up like this:
11-22-333-44W5
What I want to do is add the number "100/" to the begining of every number sequence. If I have to manually add it, I have to go down 11,000 Lines and add it to every line, is there a way to just tell excel to add the number "100/" and down fill it for everyline - end result will look like:
100/11-22-333-44W5
View 9 Replies
View Related
May 31, 2009
I have two columns of cells that need to be reformatted: B and C. In the B column excel is reading the cells as time (0:41:55.0), I need it to display those cells as a number like this: 00 41 55.0. In column C the cells look like this: 41 : 09 : 47.3. I need the C column cells to look just like the formatted B column cells (41 09 47.3) Also, even though they have the spaces, the numbers should be read by excel as one whole number (i.e. 41 09 47.3 would be used in a calculation as 410947.3)
View 3 Replies
View Related
May 9, 2009
I have a dropdown box to select sizes.
e.g.
0929
0930
0931
09.2529
09.2530
09.2531
09.529
09.530
09.531
My issue is if I format the number to accept 4 decimal places the 09.529 number becomes 09.5290. I don't know what I can do to fix this as some need 4 decimal places and others will require none.
View 5 Replies
View Related
Jun 19, 2009
Is there a way to format a number within a sentence? i.e.
In cell A1 I have 2,000 entered and formatted as a number. In cell A2 I type in ="How can I format "&A1&" ?"
When I do this, the result in A2 is How can I format 2000?
Is there a way to format the 2000 to show the thousand separator comma? If the number were -2,000 instead, is there a way to show both the comma and parentheses around the number? (without putting the parentheses manually into the formula)?
View 4 Replies
View Related
Feb 21, 2013
I am trying to figure out why my number will not format correctly... I have a number thats value is pulled in via external data that gets updated everyday.
Everything works fine there, but I needed the format of that number that was pulled in to have parenthesis around it...
I know that one can add the parenthesis to the number by using the ="("&(Number)&")" function...
What the problem is is that when I add the parenthesis to the number it will not allow me to view it as a percentage; it will leave it as a full general number (Ex. (0.0006), instead of (0.06%)
Is there a way to get the percentage number format back?
View 2 Replies
View Related
Apr 11, 2014
How do I use a function that gives me a number with the thousands separator and no decimals?
View 1 Replies
View Related
Jun 22, 2006
I have a cell that NEEDS to be of the following format:
#### ######
I have set this up under formatting, custom... and when I type a number like this ...
01304999999 I get the result 01304 999999 PERFECT!
Unfortunately if I type this....
01304 999 999 I get this result 01304 999 999 which ain't good as it mucks up my Macro, whih incidentally is this...
appWD.ActiveDocument.Bookmarks("PMTelephone").Range = Format(strPMTelephone, "0#### ######")
View 10 Replies
View Related
Aug 9, 2012
If I want to include the value of cell C4 in a formula, is it possible to keep the formatting ?
Cell C4 = 10,000.50
If I use a formula :="Profit for the Year ="&C4
ResultProfit for the Year =10000.5
View 2 Replies
View Related
Dec 14, 2006
Is there any way to turn a number that is in general/text format into a date?
IE:
82005
92005
102005
to read:
8/2005 or Aug'05
9/2005 or Sep'05
10/2005 or Oct'05
I have tried using IF statements and it looks like there's a limit in the amount of scenerios you can have. I get data in text format that comes to me in numbers and I'm just looking to turn those numbers into dates so I can sort them properly.
View 9 Replies
View Related
Dec 19, 2006
Sub CommentThem()
Dim cell As Range
On Error Resume Next
Selection.ClearComments
On Error GoTo 0
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If cell.Formula "" Then
cell.AddComment
cell.Comment.Visible = False
On Error Resume Next 'fails on invalid formula
cell.Comment.Text Text:=" Days: " & cell.Value & Chr(10)
On Error GoTo 0
End If
Next cell
End Sub
I am using this formula to extract the [h]:mm value from a cell and place it into the cell comments. The current result gives me a result of Days: 3.9339797008547 for a cell with the value of 94:24. Is there anyway to change this to only 2 decimal places (3.93) without changing the initial cell value.
View 5 Replies
View Related
May 6, 2007
I have two columns, one is with number and one is with text, what i have to do concate both columns data in one column with formatting. but when i m adding with & operator or by concatenate, formatting disappear.
View 9 Replies
View Related
Oct 4, 2007
How do you custom format a number so that 0.99 appears 99, 0.5 appears 50, 0.108 appears 108 and so on
View 9 Replies
View Related
Jul 22, 2008
I have a few rows in an excel sheet and I want to highlight the highest number(s) in each row.
I've been playing around with conditional formatting, and using the formula:
=$C23=MAX($C$23:$C$55)
in the conditional formatting box and selecting a formatting type, I can automatically highlight the highest number in column C (row 23 to 55). However, this formula only seems to work when I'm selecting columns of data rather than rows. If I want to highlight the highest number in row 23, the formula I'm using is:
=$C23=MAX($C$23:$M$23)
Which I think should highlight the highest number From C23 to M23, but it doesn't - nothing happens.
What am I doing wrong?
View 9 Replies
View Related
Jul 7, 2009
I have a list of telephone numbers... in every format you can imagine
123.456.7892
(123) 456 - 7892
1234567892
123-456-7892
etc...
is there any way to extract JUST the numbers...
so the format all phone numbers would take would be:
1234567892
?
View 9 Replies
View Related
Jul 26, 2009
I would to show the number like this if it hasn't decimal like 2 showing in 2 not 2.0 and if it has decimal like 1.5 show 1.5, 1.75 show 1.75....
1
1.5
0.5
2
1.75
0.25
Now I try this "_(* #,##0.0_);_(* (#,##0);_(* "-"??_);_(@_)"
View 9 Replies
View Related
Sep 14, 2006
I need a custom number format to do the following: When a whole number, shows only whole number (no decimal place) When decimal, show decimal. For example, have this data show as typed:
45 (not 45.0)
32
6.7
43.5
87
I can't figure out a format to allow this. The closest I get is:
??.?
but that gives me:
45.
32.
6.7
43.5
87.
View 3 Replies
View Related
Jul 10, 2014
Some of my numeric values are with single decimals .....
Eg:
1542.2
9856.5
659855.9
2589.7
When I format in Excel with Number with 2 decimals my results remain the same
But the result which is need is:
1542.20
9856.50
659855.90
2589.70
View 4 Replies
View Related
Feb 25, 2009
I need to reformat phone numbers in Excel, and I am having trouble doing so. I do not have VBA experience, but I am somewhat familiar with functions in Excel. I need to go from 555-555-5555 to (555) 555-5555
View 4 Replies
View Related
Dec 5, 2009
Whenever I make one with numbers, I get formatting for a number as below
1234567.78(no commas and 2 decimal places)
I normally want the formatting as 1,234,568 (comma inserted, no decimals,), (the last digit is changed just becasue of round off, other wise number in both cases is same).
Now I can double click the field, goto number--> number and then apply this formatting.
My question is whether there is a setting in excel somewhere so that this formatting will come as default (after I create the pivot table each time)?
View 6 Replies
View Related
Feb 28, 2013
I have a row of data I want to be blacked out if there isnt a reference number in the first column
So if I type the reference number i can in theory then use the rest of the row but if there is no reference number in the first column i cant see any of the row ...
View 1 Replies
View Related
