Returning 2nd And 3rd Instances In Vlookup

I'd like to use vlookup, but there are multiple instances of each lookup_value. I want to use vlookup like normal to get the first instance of each lookup_value and paste these in one column. Then I'd like to remove these values from the table_array so that vlookup can find the second instance of the lookup_value and paste in the second column, and so on.

I've attached an example. I'd like to get the description data next to the class data column by matching by the ID column. The output should be (in columns C, D, and E):

one one' one''
two
three
four four'
five five'
six
seven
eight
nine
ten ten'

Thanks in advance for the help!

Here's what I am basically trying to do:

There is a list of data like this:

Ticker Date
goog 1/2/07
goog 1/3/07
goog 1/4/07
goog 1/5/07
goog 1/8/07
goog 1/9/07
ibm 1/2/07
ibm 1/3/07
ibm 1/4/07
ibm 1/5/07

Basically I need is VBA code to do the following:

Identify the first instance of goog, give me the the row#
Identify the last instance of goog, give me the row#

Identify the first instance of ibm, give me the the row#
Identify the last instance of ibm, give me the row#

I guess what is getting complex for me is the following:
There will not always be two unique stocks, sometimes it could be 2, sometimes it could be 10, or whatever, so do I need to build an array of some sort to get all the unique values in column A (ticker column) and then do some search for first and last instances.

Also, from the example, there won't necessarily be the same number of instances of each stock either.

I hope this makes sense and any help would be much appreciated.

~Gooser

I could just look this up, but I'll be lazy.
I want to name multiple instances of a value.


Ref.No - Date - Instance
123 01/01/2009 1st
123 06/01/2009 2nd
122 07/02/2009 1st


Column "Instance" to look at "Ref.No" and "Date", giving the result of "1st instance", etc. (Using dates order)

I've tried arrays of Match and count, but I'm stuck.

Ok so im trying to make something that is easy to update, which will be read off a big table through a dump of data.

I am graphing headcounts by date for each day of the week. It's generally a 365 period that im dealing with here so im trying to make it so that you dump the data which is fed into tables that search for each date that corresponds to a specific day and reads the count from there.

The data comes as follows

Column A - Day of Week
Column B - Date
Column C to Z - Head counts by time

So I need something that searches column A for all instances of "Monday" for example and returns each corresponding date. The rest I can take care of but it's being able to retrieve each instance without having to sort the data and manually pull them out that im trying to avoid.

Im not sure if this is even possible :/ Any advice would be greatly appreciated

Kyle

Alright, so hopefully this isn't as complicated as I think it is but here is my dilemma. I'm doing a sumif for dates. If there is a date that equals a date in my range then I'm adding the hours entered in for that date. However, with the data I'm importing there are multiple entries and only one date. Is it possible to include in my formula a variable reference for this purpose?

Here is the code I'm using..

Code:

=SUMIF('Report Data'!$D$3:$D$40000,A1,OFFSET('Report Data'!$Q$3:$Q$40000,1,0,1,1))

In column D is where the dates are at. A1 is a date I want it to look for in D3:D40000 if it finds the date I want it to add the hours for that date it finds. I had to use offset because the hours entered are for a row below the date in column Q.

Now the problem Is there can be sometimes up to 10 instances entered in for a date in column D. How would I go about writing that into the code so it adds the hours in column Q for just 1 date?


Here is a sample of the workbook:

I have a spreadsheet tracking work assignments for 7 employees. Column F has their names, column G has the date they begin an assignment, and column H has the date they finish. I want a formula for column G that will look at the name in column F, find the last instance of that name, and put the finishing date from column H into this new instance of the name, in column G.

1.....F..........G..........H
2.....John.....1 Jan....15 Jan
3.....Joe.......3 Jan....11 Jan
4.....Jack.....4 Jan....24 Jan
5.....John.....15 Jan..22 Jan
6.....John.....???

I want G6 to find the last time that John appeared in column F, which happens to be line 5, and automatically take the date from H5 and put it in G6.

I have a spreadsheet tracking work assignments for 7 employees. Column F has their names, column G has the date they begin an assignment, and column H has the date they finish. I want a formula for column G that will look at the name in column F, find the last instance of that name, and put the finishing date from column H into this new instance of the name, in column G.

1.....F..........G..........H
2.....John.....1 Jan....15 Jan
3.....Joe.......3 Jan....11 Jan
4.....Jack.....4 Jan....24 Jan
5.....John.....15 Jan..22 Jan
6.....John.....???

I want G6 to find the last time that John appeared in column F, which happens to be line 5, and automatically take the date from H5 and put it in G6.

Hello, this is my first post on this forum and I'm glad to be here!

I was wondering if someone could assist me with something, problem is that I'm not really sure how to explain it.

Column A includes a name
Column B includes an age
Column C states a division (this is the column that contains the Vlookup function)

Column G is where I have to put a range of ages (for instance, x=<0,>3)
Column H is where the division name is.

The idea behind this exercise is to use the Vlookup function in column C to reference column G, and return a value in column H.

Here a copy of my formula in column C: =VLOOKUP(B3,$G$3:$H$12,2,TRUE)

For instance, if Mike (A) is 16 (B) then he would be in the Midget division (C)

C is calculated by comparing column B to G, then returning a value in column H.

For some odd reason, I can't get column G to reference an age range, such as 1-5 years old.

Does anyone have any ideas? I've surprisingly never ran into this problem before.

Hi all

My data shows the type of work being undertaken during a particular time period during the day. So for example, X X Y X X Z X Y spread over 8 columns would mean that the person is working for 8 hours, the first two hours are doing task X, the third is task Y, etc...

I am looking for a formula to return the column number of the first and last instance of a particular task type. I have used the MATCH function, with the final parameter of "0" to return the first instance of each task. That works fine. However, how can I return the column number of the last instance of a particular task type? So in the example above, I would like to return column 7 as the last instance of task X, or column 8 as the last instance of task Y.

Thanks in advance.

Hi all -
I've actually written out a formula to do what I need but it's a beast, so I'm hoping someone can show me a simpler way.

In one worksheet I have a column of titles. In the next columns I have start date (1), end date (1), start date (2), end date (2), etc.
In a second worksheet I have a column of dates and a column of the titles. In the third column I need to return "In" or "Out" depending on the dates in the first worksheet.
If the date in the second worksheet falls inside the range of start&end 1, start&end 2, etc. then it should say "OUT". If it falls outside any of the ranges it should say "IN".

Because the names of the date columns are the same except for the number, I thought using fuzzy logic within the VLOOKUP/MATCH would work, but it still only returns the first instance (start date (1))

The formula I've written basically goes through every set of dates inside a very complicated IF statement. Is there a way to write out the formula so it looks across the whole array of dates before returning the value?

Here - if you're interested - is the formula i wrote:
=IF(IF(AND('Raw Display'!$A3>=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3

I need help transposing data. Suppose I had a unique identifier (e.g., SS#) in column A and a date related to a login instance in Column B. When sorted, I might have 2 logins for the first SS# and 5 logins for the next. I would like my spreadsheet to have one one ss# in row 1 and all the login instances in subsequent columns along that row. I need some sort of transpose function that will transpose the login dates at each change in Column A. Any suggestions?

Thanks,

Can you get a VLookup to report the first value it encounters but then continue and report all other instances.
I am only aware Vlookup searches a range until it finds the first instance.
e.g.

A Apples
B Pears
C Grapes
D Oranges
A Insects
C Animals
A Bugs
B Blue

A vlookup of "A" would return Apples and I want it to return in column 1 Apples column 2 Insects and column 3 bugs.
The data is all mixed up and cannot be sorted so I could sort into blocks to change the lookup range.

I hope I am clear. Any help always appreciated.

A product number (this could be displayed 5 or six times in tab)
B

I have this formula but I need it to find all instances and total up the result found in column 10 from each instance. Right now it just finds the first one and copies whats in column 10.

Here is the formula I have
=IF(ISERROR(VLOOKUP($A6,PREInvoice!$C$22:$L$42,10,0)),0,(VLOOKUP($A6,PREInvoice!$C$22:$L$42,10,0)))

New to the forum and need some help with a macro.

On a given sheet I have a column starting with E1 that has 1000's of numbers which contain some equal values. Some of the values 251452 for example in column E have dates in the same row on another column, 11-05-2009 in A1 for example.

I need a way to find all instances of 251452 in column E then capture the date in column A on the same row if it exists for any instance of 251452 and paste that date to column A for those rows of 251452 that dont have a date.

There are many different values in E and different dates so I need it to repeat over and over down the E column. There may be some values in column E that have no dates at all so those need to skip if no values in E have no dates in column A.

This sounds easy but it has been a few years since I have worked with macros and I would appreciate any help.

Thanks,

Glocker

Someone please help me: Situation is that I'm performing a vlookup function where there is more than one instance of the lookup value. The formula returns the first instance of the value only, I want to sum the results of all instances of this value - any pointers appreciated.

I'm completely stumped on this.

i have a series of data points, that only has 5 values... (0, 1, 2, -1, -2). I'm trying to create a function that traces the instances of a binomial tree. so, the starting cell... a1, will start with 0, and from there, can take on values 1 or -1 (in cell A2). then from each of those.. 1 can go to 2, or the -1 can go to 0 (A3). once a cell reaches 2 or -2, the test ends. in column B, i want to have my formula that will, be conditional that 1 or -1 is first obtained. so, once a result other than 0 is reached, from the row the current cell, looking up from that cell.. so in b10, it looks through A1:10 for instances other than 0, if a 1 is obtained, then look for the first instance of 0 or 2. if a -1 is reached, look for a 0 or -2. then in b11, it would run the same test but in the range of a1:a11. also.. if in the cell b11, i want to look for any instance different from the previous. so, here is an example of what im hoping to do.
if column A is displaying
0,0,0,1,1,1,0,0,1,1,2... this should display 1, 2
another instance...
0,1,1,1,0,0,-1,-1,0,1,2 this should diplay 1, -1, 1, 2


any help would be greatly appreciated.

thank you in advance.

I have a range of cells A1:AF33, each one can contain one of 35 different text entries each of which corresponds to a random value. These entries can vary depending upon other circumstances.

The problem I am having is that there are multiple instances of the same text entry but I need each instance to calculate its own random value.

For example,

Cells C9, W15, AA21 all contain AF which equates to randbetween(60,120) - however each instance needs to generate its own random value.

I tried using a vlookup with formula for each text entry but realised that each instance was generating the same value as they were looking at the same formula.

Any ideas on how I can do this?

Hi,

I'm looking for a formula that will return the column header (a date) of the first instance of a number greater than 0.

For example if columns A through S had dates as in row 1 (header row), and in row 2, every column contained a 0 up until column P, I would want the date (Row 1, Column P) returned? I think this might be a sort of index/match formula, but I am not very familiar with these.

Appreciate your help.

Jason

I swear I've done this before, but I'm drawing a complete blank. Is there a formula that will allow me to sum values from one column based on corresponding text in another column? Example: column A lists the stores I buy from and column B lists how much I spend each time I go to the various stores. Entries are by date, so column A is unsorted. I want to build a summary table to the side that will find every instance of a given store name in column A, then sum the corresponding amounts in column B. The entries cannot be sorted; they must remain in date order. If I need to clarify my example, please let me know what other info will help. Thanks in advance!

Hello!

In column A i have a list of scores, and in column B i have the date associated with each score.

I want to display the overall highest score, overall lowest score, and the most recent date associated with each. I can get the highest and lowest easily, but when i do the VLOOKUP for the dates, i am always returned with the earliest date, not the most recent.

So, for instance, if the top score was 68 and i achieved that on 3rd Jan 2008, 5 May 2008 and 6th July 2008, i would want the formula to return 6th July 2008. Same for lowest.

I need to keep the data in date order, is there a way to get VLOOKUP to provide the most recent date? Or do i have to get a bit more complex?

Thanks in advance!