Calculate Only Last Occurrence Or Occurrence Before Specified Number

Dec 24, 2013

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?

View 2 Replies


ADVERTISEMENT

Formatting From The Second Occurrence Of A Number

Dec 2, 2008

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.

Ex.:

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.

Ex.:

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)

View 9 Replies View Related

Return The Last Occurrence Of A Number In Range

Dec 15, 2008

What Formula will return the above request (Preferable as "simple" as possible) ...

View 13 Replies View Related

Number Of Consecutive Days And First Occurrence

Nov 24, 2009

In my worksheet, I have a column of ascending dates from BD7 to BD22, with BD22 being the current date. In column BN, I track whether a process is "Over" or "Under" a limit.

The values in column BN are text with either of those names.

I need to return two values in cells BO7 and BO8 respectively:

1) The number of consecutive days back from the most current day that have been "Over" the limit

2) The date going from the most current day that the first "Over" occurred

View 9 Replies View Related

Return Occurrence Number Of Lookup

Aug 15, 2008

I've found Nth_Occurrence and it's frankly brilliant but I need to be able to return the occurrence number of a value in a range and it doesn't quite do what I need.

Using = COUNTIF(A$1:A1,A1) gives me exactly the answer I need but as I'm working with big ranges it's taking a lot of calculation time.

View 9 Replies View Related

Using VBA To Search Range And Register Row Number Of First Occurrence Of Each Value

Sep 25, 2013

how to search a defined range (using namned range and offset to last non empty row) and register row number for the first occurance of each ID using VBA. I made an example file where I need to search column A (search range using namned range "ID" and offset to last non empty row) and register rownumber for the first occurance of each ID in Column C.

example.xlsm

View 1 Replies View Related

Extract Number From Multiple Occurrence Delimiter Of String Excel

Aug 14, 2014

I'm trying to extract all the numbers from the left of the delimiter ":" . They can occur once or more.

E.g. cell F2 contains BP2.2.1:40 BP2.2.1:50 BP2.2.1:60 BP2.2.1:70 BP2.4.1:80

and what i want as a result is : BP2.2.1 BP2.2.1 BP2.2.1 BP2.2.1 BP2.4.1.

I've tried =IFERROR(LEFT(F2,FIND(":",F2)-2), " ") but only displays 1 out of a possible 4 in the cell.

View 12 Replies View Related

Last Occurrence Of A Particular Value

Oct 14, 2006

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.

View 9 Replies View Related

Last Occurrence From Many

Jan 9, 2007

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.

View 9 Replies View Related

Count Occurrence Of Value

Feb 20, 2014

I have two sheets:

Sheet 1
Col A
A
B
C
D

Sheet 2
Col ACol B
AXX
BYY
AXX
AZ
DD
DD

I want to count the number of unique occurance of value in Col B in Sheet 2 as per value in Col A in sheet 1 and Sheet2.

My output result should be like following:

Result
Col ACount of col B value in Sheet 2
A2
B1
C 0
D1

View 3 Replies View Related

Last Occurrence Of Value In A Period

May 26, 2014

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 have tried using array formula

{=MATCH(E1,IF(A2:A21>=E2,IF(A2:A21=E2,IF(A2:A21

View 5 Replies View Related

VLOOKUP Second Occurrence

Jun 13, 2002

I am trying to find a way to return an second answer for a second occurance using a VLOOKUP function. (maybe it can be done with something else?)

ex. range= A1:C3,

A1 = 40CA B1 = 30R2 C1 = TRUCK
A2 = 40CB B2 = 30A1 C2 = CAR
A3 = 40CA C2 = 30B3 C3 = TRAILER

VLOOKUP("40CA",A1:C3,3,FALSE) Returns "TRUCK"

How can I get a formula like this to return "TRAILER" the
second occurance of "40CA"?

View 9 Replies View Related

Separating Second Occurrence

Sep 16, 2008

[data] ....

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.

View 9 Replies View Related

Match Index Nth Occurrence?

Feb 20, 2011

I have tried using the 'arbitrary lookup' function as listed here but can't seem to get it to work. [URL] .....

I have a list of all football fixtures and results in the premier league this season and I have a separate worksheet with a drop down menu to load each team.

What I am struggling to do is to get a function that will obtain the data corresponding to each of the home fixtures and the away fixtures of the team selected in the drop down menu.

I have managed to get the first result but do not know how to get the 2nd occurrence, the 3rd etc.

My formula for the first is

=INDEX(Fixtures!$A$3:$R$380,MATCH($E$1,Fixtures!$C$3:$C$380, FALSE),5).

I've attached the spreadsheet. (Unfortunately the format of the excel sheet can't be changed)

Forum Example.xls

View 7 Replies View Related

Highlight All Duplicates After First Occurrence

May 10, 2014

I have attached the excel sheet for reference. As shown in the "After" sheet, i need the 2nd to nth instance of duplicate keywords to be highlighted with some color.

View 9 Replies View Related

Sum Column Total Until Next Occurrence?

Jun 24, 2014

[URL]

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.

View 14 Replies View Related

Countifs To Count Each Occurrence Of AA Or DC By Itself

Aug 13, 2014

I have a column of data that contains a two letter identifier, which can sometimes be combined with another 2 letter indentifier: Example

Column D
AA
DC
DC,AA

How would I get countifs to count each occurrence of AA or DC by itself?

View 8 Replies View Related

Counting Every Nth Occurrence (with & Without A Condition)

Sep 24, 2009

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.

View 4 Replies View Related

Find Second Occurrence Or Third Or Fourth

Jul 13, 2013

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?

View 4 Replies View Related

Consecutive Value Occurrence Count

Apr 2, 2008

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

View 14 Replies View Related

How To Extract First Occurrence From A Table

Mar 22, 2009

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...

View 9 Replies View Related

Next Occurrence For Specific Task

Jan 11, 2012

I have 2 sheets with a formula in sheet 2 looking to pick up information from sheet 1. the formula in sheet 2 is:

=HLOOKUP(C8,'Cost-Effort'!$B$7:$AH$42,26, FALSE)

But C8 appears in W7 and X7 in sheet 1 and will only pick up the first Occurrence and I need it to pick up the second Occurrence for this specific task.

View 6 Replies View Related

Finding Nth Occurrence Of Zero And Non-Zero Character

Aug 24, 2012

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.

View 3 Replies View Related

FIND LAST Occurrence Of String

Nov 12, 2013

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.

Sample: Find the last occurrence of MDU

Code:
Sample string1 - ABDJ FFU MDU WWW AEH JJF MDU JJI LLK OOI HAHA QWAS
Sample string2 - HFUR MDU HDS IGH RJR KDHF LLDE MDU KKJ MDU GGG

In the above 2 sample strings the output of the Find() function needs to be 26 & 40. Having a cell formula would be desirable, but if it can only be done by VBA that is acceptable, also.

View 9 Replies View Related

Counting The Repetitive Occurrence Value

Sep 3, 2008

i want the formula to be used in excel to make acounter in another column for the values the occured repetitively

like to have these two column.

ex:

Oper No
Work Center
10 Paintsh
20 Paintsh
30 Paintsh
40 Paintsh
50 Paintsh
60
Paintsh
70 Paintsh
80 Paintsh
90 Paintsh

here the operation no is the counter for the repeated work center whose name is paint sh

View 9 Replies View Related

1st Occurrence Of Non Blank Cell

Jun 26, 2003

I have data across rows in which I want to find the 1st occurance of a non blank cell and return this as a reference.

The formula in col A was kindly provided by Aladin, this finds the last entry, can this be tweaked? I'll be honest and say that I dont't quite understand how it works.

Row 5 should return 2
Row 9 should return 29

View 9 Replies View Related

Drop Down. Return One Occurrence

Dec 13, 2006

I need to know how to create a drop down menu from a very large spreadsheet of Column A but have it only return 1 occurrance. NOT in VBA.

Example:
Column A - these are different Value Streams PMV, SMV and WRV but this will show up over 100 times each as there are many departments per Value Stream I would only like to see 1 occurrance

Column B - Is a list of departments per value stream but will have duplicate departments Example: PMV - PM9, PMV - PMA, PMV - PMB

I would like to have a drop down and be able to select from the three value streams (Column A) and it return one occurrance then the departments that correspond with the Value Stream (Column B).

View 9 Replies View Related

Lookup Second Occurrence With Vlookup

Dec 29, 2006

I am using vlookup to find the ORIGIN data and DESTINATION data but the subheading is the same for both- see below(site name, city...). How do I find the second occurrence for the same heading?

Is there another command that would be better?

col A col B
Line#1ORIGIN INFO:
Line#1 CONTACT NM
Line#1 CONTACT PH#
Line#1 CONTACT EML
Line#1 SITE NAME
Line#1 ADDRESS
Line#1 CITY
Line#1 ST ZIP CTY
Line#1 DAY&WINDOW
Line#1 LIVE/DROP?

Line#1DESTIN INFO:
Line#1 CONTACT NM
Line#1 CONTACT PH#
Line#1 CONTACT EML
Line#1 SITE NAME
Line#1 ADDRESS
Line#1 CITY
Line#1 ST ZIP CTY

View 5 Replies View Related

Count Occurrence Of Find

Apr 7, 2007

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.

View 5 Replies View Related

Return Nth Lookup Occurrence

Aug 16, 2007

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.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved