I am trying to create a shift roster for my team. My team work in general shift (GS), second shift (SS), third shift (TS). There are situations where some team members come in any of these shift but had to leave midway due to feeling well, so that should be counted as half day. This half day scenario may be in any case GS/SS/TS. I am using COUNTIF to get the sum of GS/SS/TS for each employee which is giving a whole number. I also want to incorporate 3 different variations of half day as Half Day GS (HG), Half Day SS (HS), Half Day TS (HT). My expectation is that for each Half Day only 0.5 should be added in a appropriate shift, but COUNTIF is always counting this as 1. How I can achieve this using countif or any other Excel function? Sample sheet is attached.
I want to the formula to look at the 6th and 6th digit (numeric, alpha-numeric, or Alpha) and compare it with the cells under "'PSCCR Queue - Manager" TAB from C4 thru C7.
Below formula is only looking at the first value when comparing.
I had an "If" formula that would grab my numeric data from one cell and dump it into the corresponding cell depending on my letter code. I had to change my code vales from apha to numeric. I enclosed the attached spreadsheet and highlighted the cells in yellow that are in my formula that don't work any longer. I was hoping someone may have a solution.
19352510 C084111X AA 24253081 A001290U AA 19599291 48413321
I want to write an If Statement is a column next to each entry that denotes two options either Broker or Agent. Where the code is say 19352510 then Broker and where it is say C084111X AA then Agent.
Broker codes will never contain a letter. The Agent code will always start and finish with a letter.
In a column I have data like 2W, 2BM, 4W, 6BM, 10W and 15BM. Question is how can I total all W and all BM. Total for W = 2W + 4W+10W = 16W and total for BM = 2BM +6BM +15BM = 23BM.
I have an application where there is a column A with values of F1 through F83. There is data in columns B,C,D,E,F AND G.
From time to time there is an addition in column A, the value being F1A or F2A etc. The rows then have to be sorted so that F1A follows F1 and the next row would start with F2 rather than F10.
In other words, I would like product codes where the last character is a letter to be sorted ahead of product codes where the last character is a digit. The way Excel is normally set up, it sorts in just the opposite way.
I have cells with alphanumeric values. I need to extract the numeric values based on certain criteria and put these values in adjescent rows.
The sheet I am using has 3 rows . Row B contains data. Row C is Amount, Row D is Month, Row E is Action
To put values in Rows C, D and E , I need the data from row B in following criteria.
data in B row is as follows :-
RS 555/STMT DT 170807/BP12/TO PAY MEMBERSHIP FEE RS.555/STMT 020907/BP12/ OFFER ANNFEE RS 555/- / STMT 020907 / BP 12 / FIRST TIME RS595/STMT 0807/BP12/ first time MEM FEES RS 525 /STMT 120807 / BP12/1ST TIM
criteria for row C if the cell contains "AMT" or "RS" or "INR" or "AMNT" or "RS."then extract the value till first "/" or "" and put it under Amount row. ( E.g: "AMT 595 /" or "Inr 595/" or " Rs. 595")
criteria for row D if the cell contains "stmt" or begins with "0" or endswith "07" then extract the value till first "/" or "" and put it under Month row.
criteria for row E
if the cell contains "BP" or "OP" then extarct the value till first "/" or "" and put it under department row. ( e.g " BP01 /" or "OP 02 ")
My VBA form for excel currently only accepts numeric. I want two of its fields to accept alphanumerics. Those two textboxe fields are cboIns and TextCmt in the code below.
Code for the form is given below.
VB: Dim dic As ObjectDim Ctrls Dim myTBoxes() As clsTxtBoxes
In have a worksheet with about 5,000 rows. In column there is a unique identifier for each row, however the unique identifier can take one of 12 different formats, namely:
L1314LAND00001 through to L1314LAND0XXXX L1314CUST00001 through to L1314CUST0XXXX L1314COMM00001 through to L1314COMM0XXXX
M1314LAND00001 through to M1314LAND0XXXX M1314CUST00001 through to M1314CUST0XXXX M1314COMM00001 through to M1314COMM0XXXX
N1314LAND00001 through to N1314LAND0XXXX N1314CUST00001 through to N1314CUST0XXXX N1314COMM00001 through to N1314COMM0XXXX
S1314LAND00001 through to S1314LAND0XXXX S1314CUST00001 through to S1314CUST0XXXX S1314COMM00001 through to S1314COMM0XXXX
These unique identifiers won't always appear in sequentiual order. In Cells B2 to M2, I want to be able to determine what the next entry for each of the twelve variations will be, so for example, if the current highest value in column A for the 'S1314LAND' range S131400421, in cell K2, I would want it to display S1314LAND00422, and if the current highest value in column A for the 'M1314CUST' range M1314CUST0036, I would want cell C2 to display M1314CUST0037, so anyone at a glance can see the next highest unique reference for the relevant set, rather than having to go through the unique identifiers in column A manually to determine the next entry in the series.
I'm not really sure where to start, when there can be more than one aplhanumeric entries ending in the same number, e.g. S1314CUST00500 and M1314LAND00500.
With the numbers first, in ascending order. Then the letters, grouped by letter ascending, and the number following them. But I can't have it where it looks at the first number after the letter (A1,A186,A20). It has to look at the WHOLE number and then sort it.
With the numbers first, in ascending order. Then the letters, grouped by letter ascending, and the number following them. But I can't have it where it looks at the first number after the letter (A1,A186,A20). It has to look at the WHOLE number and then sort it.
I am trying to figure out a formula that will allow me to compare the contents of cells within a range and let me know if there is a value that is not the same as the others. For example: Columns a,b,c,d,e and f and say "Joe"- this result is acceptable Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is unacceptable
I want a formula or function that will tell me this wihout me having to visually scan the data to determine this.
I have a set of text strings that have some number of alpha characters followed by some number of numeric characters. I need to separate the data into two cells.
Problem: String is broken into 2 parts within a delimeter. Goal: To keep each String/Segment intact regardless of Alpha/Numeric.
I have a .csv file with 26 segments (commas). This file contains customer data: PO#, Order#, Carton ID, Address 1, Address 2, Address 3, etc.
I've written code to capture all 26 segments as: Input #1, Seg1, Seg2, ... ,Seg26. The variables work fine until it encounter a string with Numeric first then Alpha within the same segment.
For this example we have a string such as (...,187 Main Street, PO Box 369,...) Seg4 = Address Seg5 = PO Box
The Segment Variables in Input #1, get shifted when it encounters the numeric portion of the address...so the result becomes: Segment4 = 187 Segment5 = Main Street Segment 6 = PO Box 369.
So on my next loop when ReadLine is executed...all the variables are shifted and I can't focus on the specific segment that I need to do something to it.
It delimits when it encounters Numeric then Alpha within the string, but accepts the entire string into the variable when its Alpha then Numeric within a delimeter.
How I can keep the entire string in tact within each delimeter?
I want to record the amount of people attending a venue over a given period. The information presents names and did they attend. In column "D' from D5 to D45 the information will be yes or no. I want a formula that calculates the amount of YES and shows the answer in a numeric format
Aguirre: The Wrath of God 552 Ai No Corrida 619 Aileen Wuornos: The Selling of a Serial Killer 821 Airplane! 671 Akira 764 Albero degli Zoccoli, L' 641
[etc]
I want to extract the page numbers (the final numbers) and place them into a new, separate column.
I am using Microsoft Excel X for Max Service Release 1.
I am competent on basic excel but not familiar with complicated formula etc.
how to retain the values of variables even after closing the workbook...Ref:have created workbook and renamed sheets as Day1,Day2,..,DayN..as and whn new worksheet is created.after saving and closing this application, next time when we open the application and create new worksheet it should create DayN+1.
My spreadsheet contains a tasklist. It is generally sorted by priority (number); each row also contains a task ID ("T0100"). When I add a new task I need to know the last used task ID so I can assign the next one.
I've tried various built in functions but each expects numbers.
How can I insert in a cell (or display in a msgbox) the maximum text value?
For example, Given the data below I'd like to have show "T0888".
What I am doing is setting up a product ratecard sheet to run with our CRM system in work.
Each product has 10 quantities, with 10 different prices, but each product must have the same product code running down in column A
I have thousands of products to put in to dont want to manually type each code in.
I am ok with Excel, but I can not program in VBA, so i am looking for a formula to use.
The code is ZTRA-00001, which will stay the same for 10 rows, then there will be a space of 1 row and the next code for the next 10 rows in the column would be ZTRA-00002 etc.
I have created a simple UserForm to enter new customer details to the Customer List in the spreadsheet, form works fine except for one little thing, which is New Customer ID, which works, but only with numeric values like 1, 2, 3 and so on.
Basically what I need this for to do is once form is opened/called new customer ID need to be created, which could be and Alfa numerical set of characters like AA-01234, AA-01235, AA-01236 and so on.
Also, is there a way of posting newly added Customer ID in the MsgBox along with MsgBox "One record added to Customers List. New Customer ID is "
All of my attempts to create this are failing and causing errors.Here is my code, Customer ID is TextBox1.
I have another problem with this damn address file. Column H and I have data in them that is often mixed. As shown below, I have used A and B below, but its normaly in Column H and I. I would be greatul if some could write a macro to split the data into the two columns.
Rows 2-8 is what Im presented with. I would like them to look like 11-17
Note that the number in row 8 does not match, so is left for manual intervention....
I am currently using a color count function to both count by color and count by color and cells containing certain characters, such as # or %. This works great.
However, I need to modify the UDF to count all the cells in a range in columns C:Z that contain alpha, AND IF the Interior.ColorIndex of a cell in column B within the same row of the counting formula equals the Interior.ColorIndex of a cell in column B within the same row of the selected cell in the range.
Basically, Column B is a header row, and I want to count the cells in a range in each column C:Z if they contain a name AND their corresponding header cell's color in column B matches the color of the header cell in the row containing the formula.
