Listing via vlookup in Microsoft Excel?

Question

I have a table in excel with three columns: name, organization, contract duration (YES / NO).

I have 20 more sheets, one for each organization. On each sheet, I need the names of people whose contracts expire from this company. I can do vlookup for the first one listed, but I need a complete list for each organization. Any suggestions?

Answer 1

Create a pivot table for your table.

Page Fields = Organization

Line Fields = Name

Column Fields = Contract Duration (YES / NO)

Data Elements = Number of Expiring Contracts (YES / NO)

"".

, .

Answer 2

, "" :

   |    A     |    B    |    C     |
------------------------------------
 1 |   name   | company | expired? |
 2 |   mary   |   XYZ   |    NO    |
 3 |   fred   |   ABC   |   YES    |
 4 |    ..    |   ...   |    ..    |

"XYZ":

   |    A    |  B   |  C   |   D  |
------------------------------------
 1 |         |      |      |      |
 2 |    1    |  f1  |  f2  |  f3  |
 3 |  =A2+1  |  f1  |  f2  |  f3  |
 4 |  =A3+1  |  f1  |  f2  |  f3  |
 5 |   ...   |  ..  |  ..  |  ..  |

f1: =IF(AND(List!B2="XYZ",List!C2="YES"),B1+1,B1)
f2: =IF(A2>MAX(B:B),"",MATCH(A2,B:B,0))
f3: =IF(C2="","",INDEX(List!A:A,C2))

Answer 3

How about creating a list of only contacts ending and then using the free DataPig Excel Explosion add-in add-in. [wizzard to split column-based data into separate organization workbooks]? I don't think there is a version for excel 2007, but I saw examples of macros about the network that do the same.