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

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

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


.

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

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