Row 2 to 5 are of same item no. The result that is required is in Column C. The formula to check same item numbers in column A and give the result as "FRA" in column C3 with C2, C4, C5 showing as not applicable. Similarly for the next item no
i am trying to remove the unknown character and extra space from the name. Though i use formula as trim or proper(trim), it is not removing the Unknown character / extra space. I have attached the few name as sample. Formula to remove these Unknown character / extra space, double space, special character from selected cell?
I'm looking for a VBA Macro that will do the following:
1. Count number of cells in a given row that contain special character "*" 2. If there is an asterisk in a given row, return the column header(s) contained in row 1 of the spreadsheet that correspond to each instance of cell(s) that contains the "*".
Is there a way to do this? I've attached an example of what I'm talking about, (Column A shows the desired result.)
I have 1200 doctor/patient records to input into an excel spreadsheet for import to an online EHR database. I can set up all the normal formulas and formatting but for the life of me not figure out how to create a custom formula to take the first letter of the patient first name and last name and add 6 figures to create a unique patient identifier.
ie. James + Smith+ random 6 figures = JS245318.
In my spreadsheet the first name is under Column 1, Last name Column 3 and the unique number generated in column 4.
the above information have following header ie. Location, P.O.Box, Address, Tel, Fax and Category.
every time one of the header inforamtion is missing ie. Address or P.O.Box, Or Fax, due to this problem every time cell address of information aganist header is change. supose in first copy/paste Tel inforamtion is in A15, but second time it may be on A13, and 3rd time it may be on A17 and so on.
I want to make a macro which search header ("Location", "P.O.Box", "Address", "Tel", "Fax", "Category" ) from a Range A1:A30. than down two steps to copy header information (75325412) and paste in fixed cell location C1:C5 (C1=Name, C2=Location, C3: P.O.Box, C4=Address, C5=Tel, C6=Fax, C7=Category) in same sheet. and repeat the steps to copy information aganist header from all header in Range A1:A30.
Need a formula/code that will determine what the corrected part number should be (insert dashes if they are missing) by comparing to other values in the list.
I have a new sale structure to put in place the commission is paid in the following way:
below 1500 zero commission between 1501 and 3000, commission at 16% between 3001 and 8000, commission at 23% above 8001, commission paid at 30% Ergo if you generate 5000 you would be paid 700 ie nothing for the first 1500, 16% of the second 1500 and 23% of the remaining 2000. ( I hope my maths is correct! )
I have tried to manipulate other solutions using sumproduct but my knowledge is poor, the formula I have tried manipulating is =SUMPRODUCT( (A2 > {0,1500,3000,8000}) * (A2 - {0,1500,3000,8000}) * {0,0.16,0.23,0.3}). I prefer single line formula rather than lookups as staff will not be able to see commission rates easily.
I have a fairly complicated working time calculation I need to make, the sheet calculates how much 'actual working time' is spent on a product. 'Actual working time' means just that, breaks & any other non working time removed from the total.
My sheet set up is as follows:
Column Z = Start Date Column AA = Start Time Column AB = Finish Date Column AC = Finish Time Column AD = Actual working Time (hh:mm) Column AE = Planned stop time (hh:mm) Standard working times are:
Monday to Thursday 07:30 to 16:15 Friday 0730 to 1200 First tea break is 10:00 until 10:10 Lunch is 12:30 to 13:00 (except friday) Last tea break is 14:40 to 14:50
1) Jobs can be 'in production' for many days, sometimes weeks 2) Overtime may be run before or after shift, also weekends 3) We can have 'Planned stop time' which will also need subtracting
I have a cell that has approx 22000 characters. I'm trying to remove a specific character string from a cell by doing a find and replace with "". It works for characters in the first part of the cell but not for characters in the last part of the cell.
Example: I do a find/replace for the characters 21242 to "" in column A and I get the expected results. I do a find/replace on 69294 to "", again I get the expected results, but if I do a find/replace on 85203 to "", I get "Excel cannot find what I am searching for" (but its there!)
My cell size is within the max size of 32,767 characters so not sure why its not working.
I would like to find the position of the last occurrence of a character in a string. For example, I have a string with the following:
"c:wwgpeToolbidsTest File.GP$".
I would like to find out the position of the last '' in the string because I want to pull the filename, 'Test File.GP$' into a variable. I would like to avoid writing code to do this. Are there some Excel functions I can use for this?
I have attached the 2 workbook, master & transaction. From master workbook column F column name "EXIST DOCUMENT" values need to find in the transaction workbook column D column name "Documents". If match found in the column D substring field, it need to replace with column G column name "NEW DOCUMENT" value from master workbook.
The transaction workbook need to be given as input workbook, when the macro is get executed from master workbook.
I have a macro that asks the user to enter a file name and have written a small function to search the name given to the file to find any invalid characters, trouble is I must be doing something wrong as I doesn't work. Everytime I write something in the box I'm told it's invalid, regardless of weather it actually is or not.
Cells A1:A5 all have a vlookup formula that will pull one of the following into it:
1. The word "NONE" 2. #N/A error since it doesn't exist in the table 3. A text value that has the format 13.55.46.91
I want a formula that will look at these 5 cells and give me #3. That #3 value can only appear once in the 5 cells in any of those 5. The other 4 cells will contain either 1 or 2.
3 always has length equal to 11. It always has periods in those places. The digits do change.
I can use the FIND function to find the first occurrence of ":" and therefore select everything to the right of it, but how do I locate the second occurrence of ":"?
I want to segregate the 7985 Medicare. What if the data varies, i.e. some rows have one occurrence of ":" and others have two?
7900 Personnel: 7970 Bonus. I always want to segregate the last 4-digit account number and description.
I am trying to work out how to get nth position of a character in a string. For example in this
Code:
kjishdfiuayigdscka if I use the formula Code: =FIND("i",I$1),
it will give me answer 3. But I want the position of 3rd "i" in the string. The answer should be 12. How can I change this formula to get the correct result?
I am trying to remove the last 6 character from a variable. I worked out how to do it using formula's in XL, but want to move this in to VBA. Currently I have this
I'm building a table (see attached) where I could have a value in cells D6 to P6 that ranges from 0 upwards. In the respective cell beneath each 'value' cell (D7 to P7) I need a formula that will return a figure of 10 if the value in the cell above is 0.4 or below reducing to 0 if the value in the cell above is 1.0 or above.
Need formula to pic required data from Validation,
In the excel sheet Orange color (Data Validation) , If i select the data validation(Like, Jan-Orange- consumption) and corresponding data should display, so i required formula for that.
A pal at work runs a football predictions spreadsheet but due to a recent surge in numbers, it is taking longer to adminster each week.
Basically, you get 3pts for a spot on home score, 4pts for a spot on draw, 5pts for a spot on away win and only 1pt if you get a correct result but not the actual scoreline, with no points being scored for being completely wrong.
At the moment, he manually inputs 0,1,3,4 or 5 pts each Monday, of course this is open to human error. Is there a way of getting excel to calculate this for him once he inputs the correct score in the first column?
