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"
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
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] ........
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
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
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
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.
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.
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).
I want to put "Jan" (as in January) in a cell on Sheet1 and in cell A1. In cell A2 I want to dispay the start of the month and in cell A3 the end. So if I change A1 to "Feb" or any other month it will display the correct start and end dates.
I then want to use either the month or the dates to do a SUMIF statement. It a simple one like -
=IF(C5="","",SUMIF(April!R:R,"="&C5,April!G:G))
But I don't want April in the formula as I just want to update the cell in A1 to read in the data for a different month. BTW, April is on another sheet along with a sheet for each month.
I have data as shown in the attached image file. Though the example image shows that the data starts from cell D8 but it could start from J30 or any other cell for that matter.
View 9 Replies View RelatedI have a list of employees and the dates on which they were absence from work for a set period (i.e. one month). Some employees have been off for a day, some for longer. This data has been pulled from a large datasheet and now needs formatting to add in the start and end dates of absence, ready to upload to a payroll system.
The Problem:
I have been able to get a macro working on a simple list of names, however the problem arrises where I have 2 employees off on the same day - I was using a loop to find the date last used, but this no longer works.
My Request:
A copy of the worksheet in question can be found here : http://www.carpe-luna.com/other/AbsenceQueryHelp.xls
But I'll try and describe the layout as much as possible
This is how my raw data is set up. (Up to row 50 but potentially more)
A.........B...........C..............D...........E.........F
EmpNo.....EmpName.....Start Date.....End Date....NoDays....NoHours
612.......Paul........26/01/07.............................12......
612.......Paul........27/01/07.............................12......
615.......Ian.........27/01/07.............................12......
758.......Peter.......16/01/07.............................12......
758.......Peter.......17/01/07.............................12......
758.......Peter.......18/01/07.............................12......
...........................................
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 RelatedIs there a way to force Excel to look at only the first characters in a field when searching?
If I use:
MySearch = Range("C3").Value
Cells.Find(What:=MySearch, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
It will find any instance of the characters entered in C3; however, I want it to find only fields that START with those characters. E.g. if I enter TRA in the search box it will come up with CITRATE when I want TRACLEER, etc.
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
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 RelatedWhy 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))
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.
I have words in cell range (i.e. A1:A1000) and I want them to became upper letters.
Excel forum to EXCEL FORUM
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.
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] ......
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?
I need to calculate the percentage of cells in one column that have data in them compared to those that do not. This is easy enough to do by itself but here's where it gets tricky for me. I need this percentage calculated only when cells in a different column contain data.
Here's the specifics. All of the row and column references are exactly what I'm looking to have in my worksheet. If A5:A200 contain any value then I want to know the % of the cells in F5:F200 that have a value in them compared to the same number in F5:F200 that contain no value. I would like the answer to show up in B2.
Part of the issue is that cells A5:A200 already have a formula in them so I think that would count as a value. The one thing that might help is the fact that A5:A200 values that I'm looking for ALWAYS start with TN so that could be used as the search function. The reason why I need to base it on a value in A5:A200 is that the sheet is blank until I enter data in the rows. I can't have the blank rows below the last data that I've entered on the sheet count towards the percentage that I'm looking for.
Basically it's a sheet that I enter installations and service calls on. The information always has a TN# associated with it. Which through a formula is automatically populated in the same row in the A column. I track the installation or service call and when it's completed I enter a completion date in the F column in the same row.
I am trying to be able to keep track of the percentage of jobs completed (completion date entered in column F) compared to incomplete (cells in column F that have no completion date) but it has to only look at rows that actually have information entered in them which I think should be done by column A. column D5:D200 is where I enter the main information and there is no formula entered in the cells in column D so if it's easier to base it on that column then that's fine.
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 RelatedI 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.
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)))
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............
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.
I currently have a number of spreadsheets with number data in it. I need to import it into a third party program. Unfortunately when this program imports it it counts numbers such as 1 as 1.0. This third party program says the only way around this is to put a ' before the numbers and this seems to fix the problem. My problem is that I have so much data I cannot go through ever cell and add a ' to the start. IS there a quick way to add ' to the start of certain cells?
View 4 Replies View RelatedI have 7 cells containing strings but not numbers on a row.
Now I want to list out all the combinations of drawing out 3 cells out of these 7 cells while the remaining cells that haven't been drawn out could also be listed out one column next to the drawn cells.
For example, I got 7 cells like this.
A B C D E F G (each letter in ONE cell)
And I want to list out all the combinations like this:
ABC DEFG
ABD CEFG
ABE CDFG
ABF CDEG
etc.
For more details, please refer to the attached sample (an .xls file being zipped).