VLOOKUP Second Occurrence
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Lookup Second Occurrence With Vlookup
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 Replies!
View Related
Vlookup To Return Second Occurrence Of String
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 Replies!
View Related
Last Occurrence From Many
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 "108Jan07vcdppp4". 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 Replies!
View Related
Last Occurrence Of A Particular Value
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 Replies!
View Related
Separating Second Occurrence
[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 Replies!
View Related
Drop Down. Return One Occurrence
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 Replies!
View Related
Consecutive Value Occurrence Count
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 Replies!
View Related
Counting The Repetitive Occurrence Value
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 Replies!
View Related
Formatting From The Second Occurrence Of A Number
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 Replies!
View Related
Count Occurrence Of Find
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 Replies!
View Related
Lookup Nth Occurrence Or Instance
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?
View Replies!
View Related
1st Occurrence Of Non Blank Cell
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 Replies!
View Related
Number Of Consecutive Days And First Occurrence
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 Replies!
View Related
Function To Find Most Recent Occurrence
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 Replies!
View Related
Counting Occurrence In Date Periods
I want to count the number of sales in three periods. prior 7 to 12 months, prior 4 to 6 months, and over the last 3 months. I have three letters that occur in column B of sheet 2. A for active, P for pending, and S for sold. The date of activity appears in column C for each event. It is in mm/dd/yyyy format. Currently I have over 5000 rows. I would like to total the sales for each period and place it on sheet 1. can you help or point me to the right place to read up on it. I can get the information by using a pivot table but there has to be a faster way.
View Replies!
View Related
Return Occurrence Number Of Lookup
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 Replies!
View Related
Find The Last Occurrence Of A Cell That Contains >=1% In A Column
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 Replies!
View Related
Counting Each Occurrence Of Multiple Values
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 twoweek 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)
View Replies!
View Related
Count / View Of Each Occurrence In Table
I have a list of names (300 now but growing every month by 100200). In the row with the name contains data I need to view: date, $, #, etc... I want to quickly see whos name appears the most, 2nd most, 3rd most, etc.....(at the end of the year I will have 2000+ names, most names will only be listed 1x, I suspect nobodies name will be listed more than 60x.) I also need the ability to view the relative data of the person's name that is listed most, 2nd most, etc....
View Replies!
View Related
Find Every Occurrence Of Duplicated Entries
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 Replies!
View Related
Return Nth Lookup Occurrence
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 Replies!
View Related
Chart: Sorting Values In Accordance To Occurrence
I have a huge set of data values given per 0,5 hour. Now I want to make a chart which shows how many hours each value has taken place. Here is what it looks like with the arranged data as yvalues. Instead of the xaxis showing the number of values, I would like it to show the above mentioned.
View Replies!
View Related
Search For A String. Then Copy All Cells Between Each Occurrence
I'm just new here as well as in ExcelVBA 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 Replies!
View Related
Making Chart/graph From Text By Occurrence
I track inventory/sales for a very small sole proprietorship. I'm looking to track the number of items that I sell based on the text that I enter in a certain column. Specifically, I want to have a visual chart/graph to show the percentage of each size/color of an item that I sell, as I sell it. When I enter the words "3T pink" I want the chart/graph/whatever to show another quantity of this category. As I sell a new item, I want the chart to reflect it. The problem I'm having, is that the graphs/charts require two data sets, but I want the graph to calculate it for me! All I want to do is enter the text for specific items, and when the text occurs subsequent times, I want this reflected in the graph/chart. It seems so much more simple in my head....basically I want a dynamic chart, not based on a static data set.
View Replies!
View Related
Occurrence Of Overlapping Date & Time
I have a spreadsheet of all of my company's long distance calls for last month  30k rows. It includes the start time of the call and the duration. From that I can calculate the end time of the call. My phone system can handle 24 concurrent calls. I'm trying to see how many times throughout the month did we have 24 or more calls going on at once. Assume Start_Time is Column_A and End_Time is Column_B Currently I can test if one call is going on at the same time a second call is by this formula
View Replies!
View Related
Counting Every Nth Occurrence (with & Without A Condition)
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 Replies!
View Related
Split Column On 2nd Occurrence Of Delimiter
I have one column in a worksheet that I want to split into 2 columns. If there is one "(" in the cell, I want to split at that point, but if there are two "(" 's, I want to split at the second occurrence. I can use the Like operator to identify the cells, but is there a way to specify which delimiter in a sequence of identical delimiters to split at? Here are templates (not actual data) of the two type of cells: Name1 (ABC) (Name2, Name3) Name4 (Name5, Name6) For the first, I want to split on the second "(", before Name2, and for the second, I want to split on the first "(", before Name5.
View Replies!
View Related
1st Occurrence Displaying Value In Cell To Left
see attached. The only reason why I include an attachment is because the computer I am using at work is crippled and I cannot download a program which would allow me to display in the message body. A10:C12 is to be a summary of the respective values in A30:C36. I want a formula which will find the first occurrence of a revision number in B30 to B36 and apply the date in the cell to the left to the respective revision number to the above summary date column [[A10 to A12]. B30 to B36 is always unsorted order. It has to allow for lines in A30:C36 which have no entries. Line 32 and Line 33 is an example of this. In other words, based on the above example, A10 would show date 02/01/06, A11 would show 03/22/06, and A12 would show 04/14/06. Each revision number in B30 to B36 is consistent with regard to the particular revision being the same date. The reason for wanting first occurence is because any one revision in the summary may consist of a number of revision amounts below, each having the same revision number. inally, although I would like to retain the particular column order in the example, if necessary, I can reverse A1 and B2.
View Replies!
View Related
Formula To Locate Second Occurrence With Multiple Conditions Met
The purpose of the formula is to locate the 2nd occurrence of an entry where criteria in other columns are met (used to determine whether people can qualify for an item based on attendance and another condition). I am using this formula but I know it needs some adjustments and can't seem to work it out or maybe am going about it completely wrong. =IF(COUNTIF(D2:D205,D2)>1,AND(T2="Y",U2="Y")*1,0) I need the formula to indicate the second occurrence of this cell with the criteria that BOTH first & second occurrences have T2="Y" and U2="Y". My formula only indicates the first occurrence and the first occurrence if T2 and U2 meet the conditions.
View Replies!
View Related
Finding The Maximum Occurrence Of Text Over Nonconsecutive Cells
I’m trying to figure out how to show the highest occurrence of text in 10 cells which are not next to each other. Some of the cells will also be blank And the other thing is if there is a draw e.g. 5 2Z and 5 1Z I'd like it to show that, either with a word e.g. "DRAW" or anything really other than just putting whichever of the 2 "notes" in it feels like Attached is a spreadsheet (all other data deleted) that shows what I am wanting to do – I have typed the results I am after directly in to the cells in columns BE & BF – BE will need some kind of countif, but when I try that is says I have too many arguments! I have to leave the columns in between blank in this case as they have other info in them, which is really annoying as if those 10 cells were contiguous then the formula
View Replies!
View Related
Find All Occurrence Of Word & Copy Offset Values
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 Replies!
View Related
Find Text, Go To Found Cell & Show Message If More Than 1 Occurrence
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 Replies!
View Related
Lookup Nth Occurrence Of Date & Return Adjacent Cell
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 Replies!
View Related
Index & Match Multiple Items/Criteria: Finding The Nth Occurrence
I've read the how to for finding the nth occurrence using index/match but the example given does not really help solve my issue. The file I've attached is a condensed version of the actual file, which has more columns but I deleted all but the necessary ones for clarity. What I am trying to accomplish: On sheet1 there are three columns, Business, Amount, and Closing Date. Not all the business names have a closing date and the spreadsheet is sorted alphabetically by business name, so sorting by closing date, and using the method used in the topic " find the nth occurrence in excel", is not an option. On sheet2, I would like to see ALL the business names that have a closing date in the respective month, as opposed to just the first. Then to the right of the business names I have the sum of all the amounts in that month, but I figured out how to get that one already.
View Replies!
View Related
Create Unique List From Matrix & List Frequency Of Each Occurrence
I've searched the forum and believe this thread to be the closest to what im looking for, but its doesnt completely apply: matrix to list conversion. I have an attachment to support my questions (see attached). I have a matrix of words (strings) that contain repeating and nonrepeating contents. The matrix also has spaces which are of no value. Essentially, the VBA needs to ' analyze' this matrix and create a consolidated frequency list (as shown in the file). The matrices are HUGE and therefore some of the clumsy VBA i am using is turning out to be a bit inefficient. The file also contains formulas and such that I cannot use AutoFilter nor can I use Insert or Delete rows...so the VBA shouldnt use those either.
View Replies!
View Related
VLOOKUP With INDIRECT (become Dynamic As The Table Array Part Of The Vlookup Will Change)
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change. So the basic vlookup is as follows: =VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0) but the data I am looking for wont always be in the range M60:P73. So I tried to make it dynamic by doing the following: =VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE) The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73 This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
View Replies!
View Related
Using Vlookup & Indirect To Ref List And Vlookup Files
I have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data. This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet. I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:
View Replies!
View Related
Vlookup Across Sheets, Nested Vlookup Possibly?
I’m trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that month’s interest payment. Can anyone help me figure this out? The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. It’d also be great if the formula can be transferred between workbooks. I’m not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.
View Replies!
View Related
