Can I Find Cells That Contain LETTERS During The Last 12 Months
Dec 19, 2007
Log sheet Col a = Dates
Col d = Route ( Letters and numbers)
Col F = Times (1.2-2.3- examples)
Log sheet has 1,550 rows +
How I have been finding total for the Last 12 months is the formula below
=SUMIF(LOG!A:A,">="&TODAY()-365,LOG!F:F)
Now what I would like to try and do is the same BUT.
using COL D (on LOG sheet) to find out when I went to for example "JTF"
be advised that COL D is a route, so there are many results in each cell in col d..
So examples below
COL F On Log sheet
OERT-JPF-JMF-ASG1-JTF
OERT-JTF-JTF-ADC17-ADC17-ARAB3-ARAB3-DHAMC-JSK
OERT-JTF-JTF-ASG1-R655-JPF-JMF-LCL-JTF
OERT-JTF-JTF-R655-DHAMC-HAW-DHMAC
OERT-JTF-JTF-JTF-HI4-HI4-R655-ADC38-RS55-ES76-JPF-JMF-JPF-JMF
OERT-JTF-JTF-HI4-R655-ADC38-ES76-PMT-FLIGHT TOUR VIPS
View 9 Replies
ADVERTISEMENT
Nov 7, 2002
In col A I have various text codes in no particular order: i.e.cell A2 is PM-A01, cell A3 is BTC05, cell A4 is PM-B00, etc. The first two positions are always alphabetic. I want to sum all the numbers in column B whose adjacent column A text starts with "PM".
I tried =IF(match("PM*",A2:A100,0),b2,"") but just get "NA"
View 9 Replies
View Related
Jan 6, 2014
I am trying to code a macro that will search through a selected range of cells for key letters, for instance this cell may contain any combination of B, C, Te, Tc, RH, or LH. I would preferably like to search with capitalization being a factor but it is not a deal breaker. Below is a sample of what i have if the cell has a B, C it works for B but ignores the C i need it t o recognize both.
Code:
If InStr(1, ActiveCell.Text, "B") Then Range("O" + CStr(ActiveCell.Row)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
[Code] ........
View 9 Replies
View Related
Dec 5, 2011
I can use find and replace to remove the following, however as I would be doing the same process numerous times it would be very tedious.
I would like 2 VBA scripts.
1 - replace the following letters (uppercase & lower) in Column A and C replace A, V, N, Z, X, Q
so if it finds A it would remove A from the cell.
The cells may contain other letters including these one's i.e
A123XCB235 or
123VBN7779A
2 - The second independent code would replace the characters replace A, V, N, Z, X, Q with P, M, L, R, J, H ie. A becomes P, V becomes M etc...
Again the cells may contain other letters including these one's i.e
A123XCB235 or
123VBN7779A
View 9 Replies
View Related
Apr 24, 2008
i have colums of data containing random figures containing letters and numbers.
I want to completely remove the letters from the colums, leaving me with just a numerical figure. Is there a way to do this? There is no set pattern to work with as there could be 1 cell with 2 numbers and 6 letters, and the next cell may have 6 numbers and 1 letter.
EG: COLUMN A
aaa567783a
3782dc
23388fjjas2
22dd
I want this to simply be
EG: COLUMN A
567783
3782
233882
22
View 9 Replies
View Related
May 6, 2009
I need to find a formula that will find letters in a referance, for example i have referances like - MNE DJM & ZZPAR i need to find a formula that will find me the ZZPAR looking for "ZZ" i then want this to tell me what tpYe of referance number it is and put this into column Z.
Normal referance number like DJM and MNE i want this to show as "BROMLEY"
aND ZZPAR as "Chester"
I have tried something like the following but this is not working
View 5 Replies
View Related
May 27, 2009
The content of Cell A1 looks like this attccggttaattcccccaaaattt
(only a,t,g,c -nucleotides). I want to know the max times C occurs in this cell and the position from the start. like that a, t, g.
here the answer is 5 times and distance is 13 from start.
View 9 Replies
View Related
Apr 30, 2007
I am looking for a formula that will return the lowest value from a five cell range using letters instead of numbers. If the 5 cell range is empty the cell will remain blank. Not all the 5 cells may be used - it could be anywhere from 1 to all cells.The weightings of the letters in terms of their numerical value are as follows:
F=0
P=1
M=2
D=3
Examples of desired results:
From A1 to A5 the following letters are inputted: P M M D P. Result in A6 = P as P is the lowest numerically in the above list. B1 to B3 = D D M. Result in B4 = M. C1 = F. Result in C2 = F. All cells blank from D1 to D5 = cell in D6 remains blank.
View 3 Replies
View Related
Nov 20, 2009
I would like to be able to put the year in A1 and have in column B the months that have 5 Fridays in them. For instance if I put 2010 in A1, I would like in Column B January, April, July,October, December to appear. Any help would be appreciated if I put 2005 in A1 then I would like April, July, September, December.
View 9 Replies
View Related
May 7, 2009
formula to do the following:
Assign numerical values to the letters G, A, R each having the values of 3, 2 and 1 respectively and then take an average of their values. Please be aware that in some cells there may be no letter.
So in a 5 cell range the values could be:
blank, G, A, R, blank which equates to a result of 2 for an average, (3+2+1)/3 (the two blank cells are discounted).
View 9 Replies
View Related
Jun 17, 2014
i have column A and B with list of dates. Column A has earlier dates when compared to column B.
i am trying to find a formula with which i can know the difference between the dates in "number of months".
Ex: Column A has 06/01/2014 and Column B has 08/30/2014. The difference between the dates in terms of month is 3 which i need in a formula.
View 5 Replies
View Related
Feb 21, 2009
what is the formula to convert the date in MMYYYY into some numbers so that I can derive the difference between 2 dates?
For example:
I have 2 dates 31-Jan-09 and 28-Nov-05 in Column A & Column B respectively. I would like to derive no. of months between the difference of the 2 dates which in this case is 38 months.
View 9 Replies
View Related
Jul 11, 2014
I have a column of numbers and want to make sure everything has been entered correctly from our scanning software. Basically, I want to automatically highlight any cell that has any letter in it (e.g. z12o2 instead of 21202 or R705 instead of 5705), ignoring any cells that contain only numbers. I haven't had any luck using conditions based on formulas like =ISTEXT.
View 2 Replies
View Related
Jul 7, 2009
Attached is a copy of my data and i want to find the mode of each months data without typing it out into long hand form, eg. 111122222333344444 etc. so i believe the answer will be some where around 5 for most of them, but i just don't know a formular to show it automatically on the spread sheet.
View 2 Replies
View Related
Apr 25, 2014
I'm in Excel 2010, and the cell with the date I want to work from is H22.
I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days.
Here's the latest thing I tried (that doesn't work):
=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths,
"&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))
I should also probably note that the date in H22 is the result of another function.
=EDATE(G22,12)
View 5 Replies
View Related
Jul 8, 2014
I'm working on a function that will sum a row of cells containing letters and numbers. I am currently using :
=SUMPRODUCT((0&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5:S5,"T",""),"TP",""),"TPE",""),"P",""))+0) as my base.
Right now the formula will work with T, TP, and P; however as soon as I type TPE I get #value.
Either way you get the point of what I am trying to do, however my cells can contain any one of the following combinations:
T,TP,TPE
P,PE,
E
View 9 Replies
View Related
Feb 18, 2010
I have a spreadsheet filled with either "B" or "S" in cells. Its 500 rows long (starts from row 2) and its 839 columns wide, but the cells begin to be filled with B and S after column 11. I would like to count all B's and all S's in each column, and add them....it works for B;s as it counts them up and spits the result in row 503, just as it is supposed to. But the counter for S, does not work.
View 4 Replies
View Related
Jun 5, 2014
Why does this work:
Code:
ActiveSheet.PageSetup.PrintArea = "$A$1:$J$10"
but not this?
Code:
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(10, 10))
View 3 Replies
View Related
Jul 29, 2014
i have a list of 2000 fields which have the same format IE "AB10014"
I need to remove the "AB" from every field and leave the #.
Besides putting a space and running text to columns I'm not sure how.
View 13 Replies
View Related
Dec 7, 2009
I have words in cell range (i.e. A1:A1000) and I want them to became upper letters.
Excel forum to EXCEL FORUM
View 5 Replies
View Related
Jul 24, 2012
I have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'
E.g.
frt
34.2
36
p34.5
In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.
View 3 Replies
View Related
Dec 6, 2013
I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:
1) 18 months from the date in the cell needs to be highlighted yellow
2) 24 months from the date in the cell needs to be highlighted red
View 6 Replies
View Related
Jul 30, 2014
Only have upper case letters.
I am trying to get certain cells to only have upper case letters.
each cell in the code will have a single letter typed in.
I would rather have it as an event code (change while it is typed), but I have not grasped the whole thing yet.
This code does not give me any errors, but it is not changing the lower case letters into upper case letters either.
[Code] ......
View 2 Replies
View Related
May 15, 2013
I need to format a column of numbers so that it will sort alphanumerically.
All the numbers have 4 digits, and some of them have an extra letter on the end eg A B C
How can I custom format the column so that I get the numbers in order and the letters in sequence with them?
View 9 Replies
View Related
Mar 3, 2009
I have a column in one of my spreadsheets that I would like to split. There's currently not anything that can be used as a delimiter, nor can I use fixed width. the cells in question start with numbers and end with letters.
View 7 Replies
View Related
May 20, 2013
I have an excel sheet here with the data as
1.0 Develop Vision and Strategy
1.1 Define the business concept and long-term vision
1.1.1 Assess the external environment
1.1.1.1 Analyze and evaluate competition
1.1.1.2 Identify economic trends
1.1.1.3 Identify political and regulatory issues
1.1.1.4 Assess new technology innovations
1.1.1.5 Analyze demographics
.... etc
I need to split this into two columns column A should contain 1.0 and column B should contain Develop Vision and Strategy . Ex: 1.1.1.2 Identify economic trends in column A then column B - 1.1.1.2 column C - Identify economic trends.
View 2 Replies
View Related
Feb 1, 2013
I have a sheet with part numbers, costs and wish to add varying number to the cost in each row, based on a certain aspect of the part number:
A
B
C
[Code].....
In the example, 01/1X/2X/3X are the differentiating aspects of the part number. To rows in column A containing 01 (preceding the dash) I want to add nothing, 1X+.75,2X+1.5,3X+9.
I have researched the nested IF formula and can use it in a test as long as column A contains either numbers or letters. The problem I have is because there is both numbers and letters, the logical argument won't validate "if true". Here is my formula (omitting my attempts with parenthesis etc. around the letter in the logical argument):
=IF(IF(A1=1X,B1+.75,IF(A1=2X,B1+1.5,IF(A1=3X,B1+9,B1)))
View 9 Replies
View Related
Sep 7, 2009
The code brings up the message box the way I need it to
At the bottom of the code in the standard module is what I changed.
In the message box. If the GDT button is clicked the GDT chart opens. If the Symbol button is clicked the Symbol chart opens. This all works.
But will I select a symbol from the symbols chart it will appear in the cell that is double clicked. but then end up as a letter instead of a symbol.
But, the gdt chart works perfect.
I thought Cancel = True Would do the trick but does not. Any ideas what is missing??
This code below is in the sheet module
Private Sub Worksheet_BeforeDoubleClicK(ByVal Target As range, Cancel As Boolean)
ThisWorkbook.Unprotect
ActiveSheet.Unprotect
With Application
Cancel = True............
View 9 Replies
View Related
Jun 20, 2008
I'm trying to write a macro that will find a value beginning "ber" and paste it across to a different column. I have had a go at it but I keep getting the offset flagged up.
It needs to move 15 columns across and 7 rows down if that is of any importance.
View 9 Replies
View Related
Jul 19, 2009
Cell A1 is a past date. In cell B1 I would like how many months have gone by since todays date. eg. Cell A1 = July 07, B1 would = 24 months.
View 3 Replies
View Related
