Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 577
Default Lookup Formula: Return 1st match, then 2nd match, then 3rd match

I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B
= Task.

A B
Bob Reconcile Cash
Tim Do Sales Report
Bob Create presentation
Tim Prepare financial statements
Bob Hire staff person

Now on tab two, I want to create another list that pulls all the tasks
together by person. For example, Tab 2 would look like this:
Bob Reconcile Cash
Bob Create presentation
Bob Hire staff person
Tim Do Sales Report
Tim Prepare financial statement.

I realize I can just sort this list by name but I don't want Tab 2 to work
like that. I'm frequently shuffling tasks between people so I want Tab 2 to
automatically update as I'm moving tasks around between people. I want to
have some form of lookup formula in Tab 2 that says "get the first task for
Bob and return it," and then "get the second task for Bob and return it."
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Lookup Formula: Return 1st match, then 2nd match, then 3rd match

Would one of these layouts be ok...

Bob...Reconcile Cash...Create presentation...Hire staff person
Tim...Do Sales Report...Prepare financial statement.

Or...

Bob.................................Tim
Reconcile Cash................Do Sales Report
Create presentation..........Prepare financial statement
Hire staff person

--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col
B
= Task.

A B
Bob Reconcile Cash
Tim Do Sales Report
Bob Create presentation
Tim Prepare financial statements
Bob Hire staff person

Now on tab two, I want to create another list that pulls all the tasks
together by person. For example, Tab 2 would look like this:
Bob Reconcile Cash
Bob Create presentation
Bob Hire staff person
Tim Do Sales Report
Tim Prepare financial statement.

I realize I can just sort this list by name but I don't want Tab 2 to work
like that. I'm frequently shuffling tasks between people so I want Tab 2
to
automatically update as I'm moving tasks around between people. I want to
have some form of lookup formula in Tab 2 that says "get the first task
for
Bob and return it," and then "get the second task for Bob and return it."



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 577
Default Lookup Formula: Return 1st match, then 2nd match, then 3rd mat

The second layout would work where you showed:

Bob.................................Tim
Reconcile Cash................Do Sales Report
Create presentation..........Prepare financial statement
Hire staff person


"T. Valko" wrote:

Would one of these layouts be ok...

Bob...Reconcile Cash...Create presentation...Hire staff person
Tim...Do Sales Report...Prepare financial statement.

Or...

Bob.................................Tim
Reconcile Cash................Do Sales Report
Create presentation..........Prepare financial statement
Hire staff person

--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col
B
= Task.

A B
Bob Reconcile Cash
Tim Do Sales Report
Bob Create presentation
Tim Prepare financial statements
Bob Hire staff person

Now on tab two, I want to create another list that pulls all the tasks
together by person. For example, Tab 2 would look like this:
Bob Reconcile Cash
Bob Create presentation
Bob Hire staff person
Tim Do Sales Report
Tim Prepare financial statement.

I realize I can just sort this list by name but I don't want Tab 2 to work
like that. I'm frequently shuffling tasks between people so I want Tab 2
to
automatically update as I'm moving tasks around between people. I want to
have some form of lookup formula in Tab 2 that says "get the first task
for
Bob and return it," and then "get the second task for Bob and return it."



.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default Lookup Formula: Return 1st match, then 2nd match, then 3rd match

You could use this formula in C1 of Tab1:

=A1&"_"&COUNTIF(A$1:A1,A1)

and copy this down as far as you need to. It will give you something
like this:

Bob_1
Tim_1
Bob_2
Tim_2
Bob_3

and so on, i.e. you have a sequential count of Bob's tasks, and of
Tim's tasks, and this gives you a unique reference. In your other
sheet you can then make use of an INDEX/MATCH combination, also using
the ROW() function arranged however you wish.

Hope this helps.

Pete

On Dec 10, 8:58*pm, Scott wrote:
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B
= Task.

A * * * * * * * B
Bob * * * * Reconcile Cash
Tim * * * * Do Sales Report
Bob * * * * *Create presentation
Tim * * * * Prepare financial statements
Bob * * * * Hire staff person

Now on tab two, I want to create another list that pulls all the tasks
together by person. For example, Tab 2 would look like this:
Bob * * * *Reconcile Cash
Bob * * * *Create presentation
Bob * * * *Hire staff person
Tim * * * *Do Sales Report
Tim * * * *Prepare financial statement.

I realize I can just sort this list by name but I don't want Tab 2 to work
like that. I'm frequently shuffling tasks between people so I want Tab 2 to
automatically update as I'm moving tasks around between people. I want to
have some form of lookup formula in Tab 2 that says "get the first task for
Bob and return it," and then "get the second task for Bob and return it."


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Lookup Formula: Return 1st match, then 2nd match, then 3rd match

Try this...

Data on Sheet1 in the range A2:B6

Sheet2:

A1:B1 = Employee names = Bob, Tim

In the formula:

Emp (Employee) refers to Sheet1$A$2:$A$6
Task refers to Sheet1$B$2:$B$6

Enter this array formula** on Sheet2 in cell A2:

=IF(ROWS(A$2:A2)COUNTIF(Emp,A$1),"",INDEX(Task,SM ALL(IF(Emp=A$1,ROW(Task)),ROWS(A$2:A2))-MIN(ROW(Task))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy acrosss to B2 then down until you get a full row of blanks.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Would one of these layouts be ok...

Bob...Reconcile Cash...Create presentation...Hire staff person
Tim...Do Sales Report...Prepare financial statement.

Or...

Bob.................................Tim
Reconcile Cash................Do Sales Report
Create presentation..........Prepare financial statement
Hire staff person

--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col
B
= Task.

A B
Bob Reconcile Cash
Tim Do Sales Report
Bob Create presentation
Tim Prepare financial statements
Bob Hire staff person

Now on tab two, I want to create another list that pulls all the tasks
together by person. For example, Tab 2 would look like this:
Bob Reconcile Cash
Bob Create presentation
Bob Hire staff person
Tim Do Sales Report
Tim Prepare financial statement.

I realize I can just sort this list by name but I don't want Tab 2 to
work
like that. I'm frequently shuffling tasks between people so I want Tab 2
to
automatically update as I'm moving tasks around between people. I want to
have some form of lookup formula in Tab 2 that says "get the first task
for
Bob and return it," and then "get the second task for Bob and return it."





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup formula to return all instances of match? CParker Excel Discussion (Misc queries) 9 November 19th 08 11:34 AM
need to return next match of table, only finding first match...HEL nwtf_vol[_2_] Excel Worksheet Functions 0 January 23rd 08 02:42 AM
MATCH Multiple Criteria & Return Previous / Penultimate Match Sam via OfficeKB.com Excel Worksheet Functions 27 October 6th 07 01:39 AM
Lookup? Match? pulling rows from one spreadsheet to match a text f cjax Excel Worksheet Functions 3 July 21st 06 02:51 PM
can lookup return err if no match found Kim Greenlaw Excel Worksheet Functions 12 January 12th 06 05:27 PM


All times are GMT +1. The time now is 01:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"