The attached wb will be used to schedule 10 machines. I have a dispatch ws that will have the machine # in column A, job/lot/ assy in column B, and load hrs in column C. there is a ws for each machine number. I want the worksheets for each machine to look in the dispatch ws, and return the info from B and C that has it's number in A for every occurence, currently it will return the 1st occurrence only.
I have multiple automatically updating data sets and I need to extract certain data to perform calculations on, this is the format of the data
SPLIT AB H 2B 3B HOME 20 6 1 0 AWAY 20 7 2 0 SPLIT R HR RBI HOME 2 0 1 AWAY 3 1 4
basically, what I need is to extract the data for home and away, and put them each on one line, no problem if this is always the format, but there are other lines of data that I don't need thrown in there at times which shift the data up or down a simple vlookup will extract the first occurance of home, but I need another formula to extract the second occurance so I can have them all on one line and have the data as:
SPLIT AB H 2B 3B R HR RBI HOME 20 6 1 0 2 0 1 AWAY 20 7 2 0 3 1 4
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?
I would like to lookup the last occurence of a particular cell value in a worksheet and use the cell value in the column immediately adjacent to this one in a calculation.
For example, The text "Run #" occurs several times in a sheet with a numerical value in the next column over. I would like to use the last occurence - or as far as that goes, the largest occurence of the numerical value. Unfortunately, there is additional data in between the "Run #" occurences, so a simple max() for the column will not work.
I am trying to extract the last occurence of ppp (4th column). So as long as 4th column is "ppp", i would want to extract the row wher the the last "ppp" occurence start and display in sheet2 "108-Jan-07vcdppp4". If data in 4th column is detected to be "ppp1", it would also extract the row wher the last "ppp1" occurence start and display in sheet2.
Note that the number of ocurrence is random and do not follow specific pattern. Pls see the attached for the example.
I need to find out last occurrence of price (Last Date of Occurrence). Column A contains Date; Column B contains Highest Price of Equity Stock in that date. Reference price (Price we want to look up) is calculated in Cell E1, Cell E2 contains start of evaluation period (date), Cell E3 contains end of evaluation period (date). I want to find out the date on which "last" occurrence of Reference price in Column B "during the evaluation period" was found.
I'm having a problem seperating the prices in Colomn H into three seperate cells C,E & G The first figure in c is easy enough if a little long (any possible tidier solutions?) The second Im not happy with it is clumsy using the fixed lenght figure "5" The last I cant figure out probably because Ive done the second incorrectly! Finally I can not see why figures apear in away team.
Now what I need is a formula similar to the one provided in the above referenced thread; however, I need it to sum the totals of all Children (C) from column BW in the attached excel file, and return the sum values found in BW to the corresponding employee (E), before the next E occurrence, or blank, if the row below the last row containing a C value in row M in blank.
refer to the excel file, specifically the row highlighted in RED, and the explanation to the right of it, for further clarification on what I'm needing the formula to do.
Ultimately, I have a census of employees with rates, and some employees cover their children, and I'm needing a formula that will be able to total up the monthly premium for each employee's covered children, and bring that total up to the same row that the employee's data is located.
I have a few dozen columns of data and would like from each row to count the number of negative numbers in every 3 occurrences. E.g. in row 2 I’d want to count the negative occurences in B2, E2, H2, K2 etc. Also, I’d like to be able to count the total number of occurences along each row, again counting every 3rd occurrence, though this time both negative and positive values.
I am trying to find an in-cell formula to find a cell with a specific value and return the value in column A of the same row. Typically this is handled by Lookup or VLookup or HLookup. However, these only find the first occurrence of the value, I even know how to find the last occurrence, but what about in between? In my spreadsheet I have reoccurring values that I need the value in column A of the same row, but I need more than just the first or last but every instance in between. How to do this?
I have to calculate bonus payments for people working O/S. I have a 5 year calendar in month blocks (60 months/columns). In each month there is a percentage 'time O/S' figure.
If an employee is O/S for 3 consecutive months they get bonus 'A', 6 consective months bonus 'B', etc. How can I evaluate the 60 columns, returning the number of times 100% occurs in 3 consecutive months/columns. I have found examples of similar solutions but they will return a value of 3 if there are 5 consecutive months of 100%...
ie. 100 100 100 100 100 = 100 100 100 *** *** & *** 100 100 100 *** & *** *** 100 100 100 = 3 but I need it to equal 1
I have a table about 35,000 rows. I just sorted the table so now you can see the data by date a time of occurrence. Now there is duplicate data... like almost duplicate 1 or 2 fields are different which is ok BUT I just want to extract the first occurrence so E.G.:
765432 Data data data etc etc maybe something different here 765432 data etc maybe different here 765432 data same thing 765449 data.... 765449 data... 800000 data 898427 data 898427 data etc...
OUTPUT: 765432 Data data data etc etc maybe something different here 765449 data.... 800000 data 898427 data
Notice how it just took the 1st occurrence and not all... I tried Advance filter but the data is all unique so it doesnt work it takes everything... I tried recording a macro but its crazy! and I did a pivot table took the first field... tried a vlookup and thought I could drag it but its getting too complicated...
Let's say I have numeric nonzero and zero data in A2:X2. How do I find the first, second, third, second-to-last, last occurrence of a nonzero or zero value. I am able to find the first nonzero value by using Match(true, A2:X20, 0) to find the relative position, and then using index to find the value. This method also works with the first zero value, but I can't seem to find how to do the second, third, nth occurrence.
I understand that the function FIND(find_text, within_text, start_num) will find the first occurrence of a stated string. And by adjusting the start_num, you can skip a set number of characters before the search begins.
But, is there a way to find the Last occurrence of said string? In my case the find_text string may occur multiple times in the variable length within_text cell to be searched.
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.
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.
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)
In one column, i get the ID. In the next column of the same row i get the status. My question is to find the number of occurrence of a specific ID with a specific status on another sheet. Attached worksheet sheet "count" has the source with ID at column C and status at column D. My expected result is in another sheet "expected result". I think a macro is needed, but i can't really figured out the way to do this counting.
Is there a way to build a formula that looks up the first record in a table that has a value of A and X? Value =A Value=X. The next formula would would then return the next record that was a value of A with a value of x that was greater than or equal to 4?
Table 1 A x 2 A 3 A x 4 A 5 A x
The 1st formula would return a value of 1. The 2nd formula would return a value of 5.
I have a very very long weekly table of, say, sales regions, items sold, revenue, profitability, etc.
(Very long here means 120,000 rows, and in Excel 2007 - please don't hold this against me! )
Let's assume Row 1 and Column A are for headers. So Column B is Sales Region and Column C is Item Name. The first Sales Region, beginning in B2, let's call it "America", will repeat (B3, B4....) until the Item Names are done for that region. Then the next Region starts, "Australia", and the list of Items starts again, etc.
Not all the Items will be sold in every Region each week, and even some Regions may disappear from the table one week only to reappear a few weeks down the road. The table is Sorted alphabetically, though, first by Region, then by Item.
One way to VLOOKUP week over week information from last week's report onto this week's report is to concatenate both weeks' Region and Item information (=B2&C2) seperately, and VLOOKUP by that. This returns only combinations of Region/Item that occured in the current week, and with ISERROR, 0 for combinations that occured this week, but not last week. As far as what it returns, this is fine for our purposes but with several pieces of information to VLOOKUP (#, revenue, profit, etc.) and 120,000 rows, it takes forever. Literally over an hour to copy down the week over week parts.
I had the idea that maybe it would be faster if I could define individual VLOOKUP ranges for each Region, and then look up Item within that range. These ranges would vary in placement in the table and in numbers of rows each week, as the number of Items sold in each Region waxes and wanes, hence "dynamic ranges" - I hope I've used that term correctly.
Is there an effective, efficient way to return the row numbers of the first and last instances of a given Region, and use that range for the Item VLOOKUP?
I'm trying to create a calendar overview of transactions from a raw data list. the result should be an amount, based on the number of occurrences of a specific transaction type at a particular date. Thus, the counter shall be based on two conditions. I've tried COUNTIF, INDEX, MATCH and VLOOKUP combined but I don't seem to "go all the way".
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:
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 a list of "GPC" or company identification numbers. And for every campaign being sent to the company it will have the same GPC of identification code. I want a formula that indicates if it is the first time mailing the company (as a result return nothing for column D ""), the second time mailing them, third, or fourth. So basically I need a formula to find the duplicate values and return whether it is the first, second, third, or fourth time that code appears in column A.
The current formula I have is a little off because it checks against the cell beforehand. I'm not sure how to modify the formula or what different forumla I need. Attached is an example. I am looking at column A and trying to return the data in D. You can also see the faulty formula I currently have in place.
I'm working on an attendance sheet, and have allocated certain letters for related occurrences. For example, V=Vacation day, S=Sick day, B=Bereavement, etc. Over a two-week period (eg - D19:D32), I want to total the number of times one of these values has been used, and add to work hours. Where an employee will enter 7.5 in D19 to indicate hours worked, they may instead enter a 'V' for a vacation day, and have 7.5 hours still added to their total hours in the pay period. I have a formula that works, but it is so incredibly long that I'm thinking there must be an easier way.
Right now, I'm using the following: =SUM(D19:D32)+(COUNTIF(D19:D32,"V")*7.5)+(COUNTIF(D19:D32,"Vh")*7.5)+(COUNTIF(D19:D32,"S")*7.5)+(COU NTIF(D19:D32,"Sh")*7.5)+(COUNTIF(D19:D32,"H")*7.5)+(COUNTIF(D19:D32,"B")*7.5)+(COUNTIF(D19:D32,"A")* 7.5)
What is the easiest way to find the last occurrence of a value in a table using functions. I want to avoid VB if at all possible and note the row number and use it in an index function to report text adjacent to that last occurrence. I would normally use match but match only records the first match and not the last.
Will it be possible to Count continuous occurrence of each signs 1-X-2 and arrange them in corresponding column From Q To AE as shown below in example table: 1
Draw result is shown in cells A2:N11 and result of Count continuous occurrence of each signs 1-X-2 is shown in cells Q2:AE11
For example we take a look row 7 where first sign-2 has occurred 3 times, as
Column Q is for to place result of sign1 And column R is for sign-X So far count of sign-2, which is 3 times, must be paced in cell S7, And then count of sign-1, which is 1 time, must be placed in cell T7, And then count of sign-2, which is 5 times, must be placed in cell V7, And finally count of sign-X, which is, also 5 times, must be placed in cell X7,
This section of code I am working on requires me to find an exact text (I tried lookat:=xlWhole, but I can't get it to work right). The problem being is when it searches for Q1, Q10 is an acceptable answer ans so on. I need to get the 4th occurrence, but my research into Nth occurrence stuff is confusing.
The search is in a single column.
With Workbooks("FY12-Q3 Data Tables.xlsx").Sheets("PBA Crosstabs").Columns(1) Set c = .Find(rCell, LookIn:=xlValues, lookat:=xlWhole) Set c = .Find(rCell, After:=c, lookat:=xlWhole) Set c = .Find(rCell, After:=c, lookat:=xlWhole) Set c = .Find(rCell, After:=c, lookat:=xlWhole) End With
I am working with a data sheet that holds a cell containing a number of position entries with each entry separated by a carriage return within the cell. I need to determine the entry associated with Accountant. The formula below determines if the entry Accountant exists and if so, displays 14 characters of information starting after the 12th character. The entry after "Accountant" is variable, but will have a carriage return at the end of the line. How can I identify the placement of the carriage return after the Accountant: entry?