# Lookup Nth Occurrence Or Instance

Aug 23, 2007

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?

## Populating 1st And 2nd Highest Occurrence Of Instance Per Category

Feb 13, 2013

I am trying to set up a template that displays (per selected team) the highest and second highest number of employee occurrences per a specific category. My main attempts at solving for this involved using an array formula to return this information, but, I couldn't get one together that worked.. Unfortunately I don't have any vba background or else I'd go that route

I've attached an Excel workbook to this thread outlining what I am attempting to put together on the template (sample outline). Tab 1 is the template which has a drop down in Cell C3:G3 where a user can select a team, Cell columns G6:G10 and H6:H10 (upon team selection) would look to the Data Entry table on Tab 2 and return the employee name (per the selected team on tab 1) and matching the designated category (displayed in B6:B10 on tab1) that has the highest and second highest occurrence of that category "infraction" tied to them.

## Lookup Each Instance Of Repeated Data

Nov 10, 2006

In the sample file attached, I want to do a lookup with the employee id in the master sheet and find the multiple results in the "open_text" sheet. From there, I want to display the open text results in one row per employee on the master sheet. If there are multiple comments, then the results will be displayed in separate cells to the right of each other (filling comment1 through comment5 if needed).

My main goal at the end of this is to be able to do a mail merge out of the master file. This is why I want the results in one row per employee.

## 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 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 CITY
Line#1 ST ZIP CTY

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

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

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

## Multiple Occurrence Lookup Without Duplicates

Jul 19, 2007

Is there anyway that the UDF can remove duplicates? It's not a huge problem, but would make things a bit easier.

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

## Subtract First Instance Of Data From Last Instance In Columns

Mar 7, 2014

I need a formula in Column E to find the results for Column E or subtract A2 from C3, C3 from D3, A4 from C3, B5 from D5 and A6 from D6 .

table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
margin: 3px;
border: 1px solid #ccc;

[Code]....

## 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?

## Add 2 Instance >=.01 Of First Instance Of >=.01

Jul 29, 2006

I have the following
Private Sub test2()
Dim c As Range, t As Long

With Worksheets(3)
.Unprotect
Set c = Range("AO10", cells(Rows.Count, "AO").End(xlUp))
t = Application.WorksheetFunction.Max((Application.WorksheetFunction. CountIf(c, ">=.01") * 2), 0)
Sheets("Template").Range("D3").value = t
'Range("D3") = t
End With
End Sub

The code is suppose to get the value of >= 1 percent from AO10 then go down the column until it finds the second instance and then add that number to the tile.

Thanks for any help in advance.

Have a great Friday all! : D

Kurt

## Returning First Instance Of Zero

Jan 8, 2009

I am trying to create a formula that will search a row left to right for the first instance of zero and return the corresponding column label. I had this working for a smaller table using a couple of hidden columns with nested ifs however the new data set is approximately 25 columns wide and it will get very messy using the same approach. See attached sample with desired results.

## First Instance Of A Value In A Column

Jul 10, 2009

I need a simple formula which does the following: I have a column of numbers, and I want to find the first instance which is equal or greater than a specific number. For example,

Column A
23
41
67
89
51
21

If the specific value is 55 the formula needs to return: 67
if the specific value is 10 the formula needs to return: 23

## VLOOKUP - Last Instance

Aug 11, 2009

Using Excel 2003 SP2. The formula =vlookup("apple",table,2,false) would return the data in the second column of the array named "table" for the FIRST INSTANCE it comes across for the word "apple." Is there a formula to have it return data for the LAST INSTANCE? This is one where I say to myself "Self, there has got to be a way to do this," but I just can't figure out what it might be.

## First Instance Of A Particular Cell?

Oct 24, 2013

I have a cell..say A1...which contains a value...now I have a row say A2:A15 which contains 14 values and I want to highlight the first instance of cell A1.

## Close Instance Through VBS

Aug 19, 2009

how to close the instance of excel created by my VBS script.

I run the script to open a workbook run a macro within the workbook and save it. I'd like to also close the workbook and the instance of excel.

If I close the workbook within the end of my macro I get an unknown run time error from my script. If I elect to just save the file and close it within the end of script, the workbook closes but the instance of EXCEL.EXE is still running in task manager.

## SUM But Only The First Instance If There Are Duplicates

Dec 23, 2005

This is an export from a shopping cart. Each row is an individual SKU that was ordered. So if John Doe orders 4 items on the same order, I've got 4 rows with most of the data being the same. I want to sum my "Grand Total" column, but only take one instance when the Order-ID is repeated.

Now the sample data: ...

## One Instance Of Each Value Within Text Range

Jul 10, 2014

I have a list of text cells in column A, the list is a dynamic one that changes depending on the property selected in a cell of another sheet.

Example data... Column A is Block Name, Column B is Room Number.

---------A----------------B-------
1--Main Building-------001
2--Block A--------------001
3--Block A--------------002
4--Main Building-------002
5--Block B--------------001
6--Block A--------------003
7--Main Building-------002a
8--BSS House----------BSS
9--Main Building-------003

What I need is for a formula that lists one example of each instance included in Column A.

As per the example data above, the expected results when dragging down from C1 through to C9 would be:

---------A----------------B--------------C--------
1--Main Building-------001-------Main Building
2--Block A--------------001-------Block A
3--Block A--------------002-------Block B
4--Main Building-------002-------BSS House
5--Block B--------------001-------
6--Block A--------------003-------
7--Main Building-------002a-------
8--BSS House----------BSS-------
9--Main Building-------003-------

I know I could use a pivot table to do this but as I need this to be dynamic, and also to be used with Excel on an iPad Air, I can't as it doesn't support pivot tables or VB to automatically refresh it.

## Index With Multiple Instance

Jan 30, 2014

I want to find top 2 performer in cell A17 & A17 in my attached sheet. Column B2:B13 changes everyday, so the name of performer changes accordingly. But today my top 2 performers achieved same but my formula is giving the same name.

Is there anyway to find out the other name i.e Ahmmed Shawkat Hossain here instead of Khan Md. Abdul alim again?

Attached File : Index 2nd name.xlsx‎

## 2d VLookup: Unique For Each Instance

Jan 5, 2010

Column A contains numbers that repeat themselves. in column B codes are assigned to each number. numbers in column A may have multiple codes in column B. but are unique for each instance the same number may appear in column A.

what i'm trying to do is.

have the numbers in column A show only once and then have the codes for the corresponding codes appear each in seperate columns rather than all in one.

## Switch Between New Instance Windows?

Nov 15, 2011

Im having trouble switching between windows that are open in separate instances of excel.

I used to be able to switch easily between these workbooks when they were both being being run in the same instance of excel.

But since having to change it so the workbooks are opened in seperate instances, i cannot get it to work, the code just fails when it trys to activate the first window.

I currently have the below code:

Code:

Sub CloseAndSave()
Application.EnableEvents = True
'//Activate InfoHub workbook and put a tick in the checkbox.
Workbooks("403 InfoHub.xls").Activate

[Code]..

Is there anyway to do this?

## VLookup Or Index Value Of First Instance?

Jul 6, 2012

I have data on two separate tabs. Data in columns A of both tabs potentially match. I am trying to identify and pull the value in column C of the second tab where the values in columns A match and the first instance where the value in column B on the second tab is less than the value in column B of the first tab. The values in both columns B are dates.

column A first tab = column A second tab
column B first tab < column B second tab (first instance of less than)
pull value from column C on second tab to appropriate row of column C on first tab.

## Find Last Instance In A Column

Mar 31, 2006

I am in the process of designing an inventory like spreadsheet with prices. If possible, I'm trying to find a well to look at a column for every instance an item shows up (lets call them apples) & then find the LAST instance of apples showing up to pull the pricing of that row only. Is this possible?

## Find Every Instance Of 0 Within A Workbook

Mar 27, 2007

I need a Macro that will find every instance of 0 within a workbook and then replace that value with an average of the values in the two cells above it and the two cells below it?

## Multiple Instance Of Excel

Feb 5, 2010

I am working on an application for a monthly sports tournament that I run. There are three workbooks to this application: the Administration workbook, the Bracket workbook, and the Leaderboard workbook. I currently have a dual monitor setup (extended, not clone) with my laptop in front of me and a large TV on the wall for the players to see.

In order for the players to see the bracket and the leaderboard, I open excel, then I open the Bracket.xlsm workbook, then the Leaderboard.xlsm workbook, and I drag those to the TV. I then open another instance of excel on my laptop, and open the Administration workbook. This stays on my laptop and the players cannot see it.

Here's what I want it to do:

In less words, the administration workbook controls the other two workbooks. Every time a player reports a win/loss, I enter that into the Administration workbook, and that needs to update both the bracket and the leaderboard, so the players can see it on the TV. So, basically, all the data is in the administration workbook, and the other two serve as displays for that data in a format the players can understand.

Here's my problem:

When I run a macro that is supposed to update the other two workbooks, I get an error: "Runtime Error '9': Subscript out of range." I believe this is because of the seperate instances of Excel. Since Bracket and Leaderboard are in a different instance than Administration, it can't 'see' those other two workbooks. Below is the part of the code that deals with these three workbooks: ....

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

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

## Highlighting All Duplicate Values Except First Instance?

May 22, 2014

I need to highlight all the duplicate IP-addresses in my sheet except the first one.

What I did:

I chose the range (F column) -> Conditional formatting -> Highlight cell rules -> Duplicate Values. It highlighted all the duplicate values so i googled how to highlight the duplicate values except the first one so I came across a formula =COUNTIF(\$F\$2:\$F2, F2)>1

So I added the formula and now it does not highlight the first duplicate but only the second and not the third and fourth and so on.

What am I doing wrong?

Edit: I have sorted them in order and in one IP-address I have 6 duplicates but it highlights only the last one when I want it to highlight all of them except the first one.

## How To Remove Text After The Second Instance Of An Asterisk?

Jun 18, 2009

I'm trying to convert variable length strings which are being copied from a display and loaded into an array.

I have it working fine for the majority of the data, which comes in looking like "*ABC@US" or "*AABC@US"

However, some of the data looks a bit different, particularly lacking the @ symbol. So what I end up with is

"*ABC US*ABC US*ABC US"

What I need to get to is just "ABC US" FYI the US part can be 1-5 characters.

SO... I need a way to truncate anything after the second instance of the asterisk. Haven't been able to get it to work using various trim, mid, len, left, right, etc functions.