Developer Forum

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

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

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


> Bob Recon>ile Cash
> Tim Do>Sales Report
> Bob r>ate presentation
> Tim Prepare>financial statements
> Bob > gt;Hire staff person
>
> Now on tab two, I want to create another list>that pulls all the tasks
> together by person. For example, Ta> 2 would look like this: >> Bob econcile Cash
>>Bob reate presentati>n
> Bob ire staff>person
> Tim o Sales Report
>>Ti> repare financial statement.
>
> I realize I can just sort this lis> 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 movin> 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."

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,SMALL(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