Vlookup To Return Second Occurrence Of String

Mar 14, 2007

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

View 2 Replies


ADVERTISEMENT

VLookup To Compare Two String Values And Return Value Of A Cell

Oct 22, 2012

I need to compare a string from say A1 to a range of cells in another workbook and if found return the value of a cell on the same row.

Something like - look up string value in A1 in other workbook.sheet1.A1:A65535 if matched return the value of matched row column b.

OR

IF Otherworkbook.sheet1.A20 = A1 return the value of B20

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

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

First Occurrence Of A Word After The Searched String

May 8, 2007

I need to find the first occurrence of the word grandsal after each employee details thru VBA. Is it possible to do that. If yes how to construct the if loops.

I take this time to thank all the members of the ozgrid for their exceptional.

View 9 Replies View Related

Position Of Last Occurrence Of Character In String

Jun 29, 2008

on the web I've found this formula, which returns the postion of the last occurrence of the character in cell B1 in the string in cell A1:

=MAX((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)=B1)*ROW(INDIRECT("A1:A"&LEN(A1))))

How do I turn this into a VBA function?

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

Search For A String. Then Copy All Cells Between Each Occurrence

Apr 4, 2007

I'm just new here as well as in Excel-VBA stuffs. I will appreciate if someone could help me. Here is the scenario. I have a data piled in a single column and different rows. Like this one:

spot.return
23,54,56..
532,677,755...
..
..
..
876,989,999;
spot.return
54,58,60..
..
..
spot.return

I need to select and copy all rows in between the occurence of "spot.return". There are a total of 80 occurences of "spot.return".

View 9 Replies View Related

Excel 2007 :: Reference Last Occurrence Of Text String

Feb 8, 2011

I am running EXCEL 2007 on XP. What is the EXCEL cell formula to put in a cell (for example"E5") that will reference the "last" occurrence of a specific text string in column A (For example Chard ) but will show its corresponding column B content (for example its PH number 3.45) regardless of the number of data entries that will be inputted in the future for both column A and B.

A B C D E
1WINEPH
2Chard3.24
3Merlot3.36
4Cab 3.44
5Merlot3.38 xxx
6Chard3.26
7Chard3.45
8Cab 3.41
9 Merlot3.33
10

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

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

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

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

Using VLookup - Search A2 And Copy B2 And C2 Every Occurrence

Jul 15, 2012

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.

Schedule.xls

View 5 Replies View Related

VLOOKUP Multiple Occurrence For Same Lookup Value?

Feb 14, 2014

accomplish VLOOKUP for multiple instances of a same Lookup value

First let me explain about my file:-

Table~1:-

1.B2:B19 I have list of items which required to build a product

2.D2:D19 I have list of shortages against each item

3.C2:C19 I have list of dates when each item shortage will get fulfilled

Table~2:-

1.C22:C31 I have picked the largest 10 different dates from Table1 by using LARGE formula.

Against each date which is update C22:C31, respective shortage qty has to get VLOOKUPED.

When there is a same lookup value, it has to keep pick next value (Shortage Qty) for same date.

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

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

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

Search Row For Text And Return Column Letter Of First Occurrence?

Dec 14, 2011

Is there a way to have a formula search an entire row containing certian text, for example "x" and have the formula return the letter of the first column that text is found?

View 8 Replies View Related

Lookup Nth Occurrence Of Date & Return Adjacent Cell

Jun 5, 2008

I have a list of dates in column AF. I would like to retrieve a value from the 2nd occurrence of a date, so I used the function for the Nth Occurrence on this website as follows:

Function Nth_Occurrence(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range
Set rFound = range_look.Cells(1, 1)
For lCount = 1 To occurrence
Set rFound = range_look. Find(find_it, rFound, xlValues, xlWhole)
Next lCount
Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function

The excel formula I am using is as follows: =Nth_Occurrence(AF2:AF622,AE2,2,0,1)
Where AE2 is the date I am looking up. My question is this: When I put in a date ("1/12/2007") instead of AE2, i get the correct value. I tried giving AE2 and the AF column the same date formatting (3/14/2001), but this doesn't work. The date I am searching for also changes, so I cannot just type the date into the excel formula.

View 2 Replies View Related

If Text Found In String Return String

Jan 31, 2008

Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.

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

Value X - Match Occurrence In Row A With Parallel Cell In Row B / Return Row B Cell Value

Dec 21, 2013

I have a value in $ED$1. This is Value X.

Row A = $DO$7:$DV$7
Row B = $DO$10:$DV$10

[Formula location = $EE$1]

Where Value X occurs in Row A, l want to return the parallel cell in Row B.

Both rows are sorted in numerical order. There is a slight complication though: Row A might contain multiple occurrences of Value X. If Value X is repeated several times in Row A, I want to find the left-most occurrence of Value X in Row A, i.e. the one closer to Column DO.

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

Use A String In VLookup?

Apr 8, 2014

I have a standard lookup table in Sheet1 with all the zipcodes and FIPS codes for the counties in 3 states:

Col-A.....Col-B.....Col-C
Zipcode..County..State
19316........029.....PA
19317........045.....PA

In Sheet2 I have data exported from a database from which I need to generate columns of county codes and states in a third worksheet.

The problem is, most of the zipcodes in Sheet2 are 9-digit and some are 5-digit:

Col-F
Zip
191493712
19133
191203510

I can extract the 5-digit zipcode using LEFT, but I can't get it to work in a Vlookup formula in a single formula.

I tried creating an intermediate column to display just the 5 digits, but when I reference that in a Vlookup formula I get N/A. I can copy that intermediate column, paste values, and convert to number and it works fine in the Vlookup (but the end user is never going to be able to do this).

How can I--in one formula--extract the first 5 digits from each cell and use that string in a VLookup formula to display the proper FIPS codes?

View 2 Replies View Related

VLOOKUP And SUM On Part Of String?

Apr 13, 2012

My sheet contains values like these:

A/5
A/3
B/4

What I want to do is sum all the values after the / for a particalur value before the /:

A 8
B 4

I already tried VLOOKUP, but this function wants you to point to a certain cell to return instead of a just a part of the cell. It should return 5 for the first row to be able to perform a sum for every value of A.

How can I perform such a calculation without having to copy the values to separate cells? Can I contain all of this in one function?

View 5 Replies View Related

VLOOKUP For Part Of A String

Oct 4, 2012

Im sure this cant be done because it defies the logic of the LOOKUP function but I know there are some clever people on here so i thought id ask!!

I have 2 workbooks containing similar data from 2 different sources; the one common data item in both is a clients address but have the age old problem of data quality, which needs sorting eventually but i need a quick fix!

In workbook one for example the address will be recorded as RUSKIN RD

In workbook two the address will be recorded as RUSKIN ROAD

A simple look up wont work obviously as the 2 values dont match, then i thought of using the LEFT function but again this goes against the logic of the VLOOKUP.

View 2 Replies View Related

Partial String Vlookup

Nov 25, 2008

I have two columns Cust. Phone and State. The Cust. Phone column has the full 10 digit number. Some with the preceeding 1 and some without. The vlookup will sit in the State column. I am trying to perform a lookup using the area code.

I have tried the following but always recieve #N/A.

=IF(LEFT(CELL("contents",A2))="1",VLOOKUP(MID(A2,2,3),D2:E8,2,FALSE),VLOOKUP(LEFT(CELL("contents",A2),3),D2:E8,2,FALSE))

It makes sense if I walk through it but fails to work.

I am trying to use the HTML maker to upload an example but I'm running into problems. I'll update ASAP.

View 9 Replies View Related

VLOOKUP String Of Text

Nov 17, 2009

I am wanting to look for a string of text within the "lookup_value" that matches the text that's in the "table_array".

View 9 Replies View Related

VLOOKUP And STRING LEFT

Feb 12, 2010

I have a issue with VLOOKUP and LEFT STRING ETC I have a lookup table see below

AB10 Grampian
AB12 Grampian
AL8 Anglia West
AL9 Carlton LWT
B1 Central West
B10 Central West
B11 Central West
B13 Central West
B14 Central West
B15 Central West

View 9 Replies View Related







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