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


ADVERTISEMENT

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

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

Find Last Occurrence Of Character In A String

May 23, 2007

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?

View 7 Replies View Related

VBA Find Exact Match And Nth Occurrence

May 30, 2012

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

View 9 Replies View Related

Find Next Occurrence Of Carriage Return

Jan 7, 2013

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?

=IF(ISERR(FIND("Accountant:",'Dynamic Report - WIP HDCI-Qu~01'!G7)),"",MID('Dynamic Report - WIP HDCI-Qu~01'!G7,FIND("Accountant:",'Dynamic Report - WIP HDCI-Qu~01'!G7)+12,14))

View 5 Replies View Related

Using FIND To Locate Second Occurrence Of Character

May 28, 2013

7900 Personnel:7980 PR taxes:7985 Medicare

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.

View 6 Replies View Related

Function To Find Most Recent Occurrence

Mar 19, 2008

I need a function that will do the following:

Look at the name in Column H2 of the current spreadsheet
Locate that name in Column H on worksheet 'Project Info' for the match with the most recent date in column F also on 'Project Info'

Return the value of corresponding column K on 'Project Info' divided by column I on 'Project Info'

Project Info has a header line so the data starts on line 2.

I don't know how to tell it to find the most recent date.

View 9 Replies View Related

Find The Last Occurrence Of A Cell That Contains >=1% In A Column

Jul 8, 2006

I have just been thrown back into programmng after a two year break so I am obviously rusty.

I have the following
Sub NumberOfReceiptFlowsAfterInitial4()

Dim my_cell As Range
Dim first_one As Double
Dim second_one As Double
Dim running_total As Double
Dim final_total As Double
Dim result As Double

I need something similar to this routine that will find the last occurrence of a cell that contains the value greater than or = 1%.

View 9 Replies View Related

Find Every Occurrence Of Duplicated Entries

Feb 22, 2007

I've been working on my original problem and have gotten a little closer to the results I'm looking for but not quite there. Basically, I'm now running into a problem of only finding the 1st occurence in a range of cells when I want to find every occurrence and show the result. Also, the range of cells to look for in my argument has 2 criteria, the start date and the end date. Can anyone tell me if there's a way to search through a range of cells and return every instance of that cell even if it's duplicated?

I've also attached my project to better understand what I'm trying to accomplish which is the use of a Gantt Chart as an employee scheduler as opposed to a task (or project) scheduler.

View 2 Replies View Related

Find All Occurrence Of Word & Copy Offset Values

Oct 31, 2009

I am writing a program which would look for look for the word " TOTAL" in sheet1 and then assign several values in sheet 2 based on cells offset of address of "TOTAL". This is repeated and down rows of sheet 2. Everything works except that it is giving repeating the first "TOTAL" address; i.e. it doesn't seem to go to the next met criterion.

Sheets("Summary").Range("A1").FormulaR1C1 = "=COUNTIF(Sheet1!C,""TOTAL"")" ....

View 7 Replies View Related

Find Text, Go To Found Cell & Show Message If More Than 1 Occurrence

Oct 1, 2008

I have a file that the user selects and when they enter a value (in this case, a job title) into the input box, my macro looks for the value in that file. If the value is there, a msgbox pops up that lets the user know that the value was found and it then goes to that cell, displaying in the next msgbox the cell address of where the value was found. I already have the code down for this part of the macro.

My problem is what happens if the file has the same value more than once. Ideally, I'd like to display a message box that returns the addresses of both cells with the same value and then prompts the user to select one of these values as the value they are looking for. I am not sure if a msgbox or a msgbox and then an input box are most appropriate for this situation. Once the user does this, the macro continues. The rest of my macro is built on the cell where this value is, so it is crucial that I make sure there is at least one value selected. Any help is much appreciated. I have included a sample worksheet of what this situation might look like.

Here is the code I presently have for this part of my macro.

Sub GetOpenFileName()
strFind = InputBox("Please enter the job title you wish to search for:", "Search for job title in this file")

If strFind = vbNullString Then Exit Sub

With Sheets(strSheetsMainCompProfile)
If WorksheetFunction. CountIf(Range(Cells(1, 1), Cells(100, 100)), "*" & strFind & "*") = 0 Then
MsgBox strFind & " cannot be found on this sheet"
Else

View 9 Replies View Related

VB To Format Every Fourth Row

May 27, 2014

I am looking for a way to format C2 as a zip code and every 4th row after that (C2, C6, C10, C14...etc)

View 4 Replies View Related

Sum Every Fourth # In A Column

Apr 3, 2008

I have about 3000 lines of data and I want to sum every fourth field.

View 9 Replies View Related

Excel 2007 :: Dynamic Sum Of Values In Every Fourth Row

Sep 29, 2011

I am using Excel 2007. I have a table with department names and the number of employees in each department. Currently, I'm using the formula :

=SUM(C12,C16,C20,C24,C28,C32,C36,C40,C44)

However, if another section is added to the database I have to update each formula. I'm looking for a way to get my totals no matter how many sections I add to the table.

View 4 Replies View Related

Compare Text In Three Cells - Return With Yes Or No In Fourth Cell

Jul 10, 2014

I have three cells - A1, B1, C1 and each contains a value of yes or no.

If more than two of the cells say "yes", I want the fourth cell (D1) to display "yes."

If less than two columns say "yes", I want the fourth cell (D1) to display "no" .

View 3 Replies View Related

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 View Related

Find/Left Functions: Grab Everything Left Of The Last Occurrence Of "." In A String

Nov 19, 2009

I want to grab everything left of the last occurrence of "." in a string, and in the next cell everything right of the last occurrence of "."

so say the string is 111.111.1.222
column 1
111.111.1

column 2
222

my current code (which works, but its messy) for the first cell is

View 3 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

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







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