Making a list through vlookup in Microsoft Excel?

Question

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

I have 20 other sheets, one for each organization. On each sheet, I want the names of the people whose contracts are expiring from that company. I can do a vlookup for the first one listed, but I need the full list for each organization. Any suggestions?

Answer 1

Create a pivot table of your table.

Page fields = Organization

Row fields = Name

Column fields = Contract expiring (YES/NO)

Data items = Count of Contract expiring (YES/NO)

Use the filter on the column field and select only 'YES' values.

Use the drop down on the Page fields and select the Organization you want to view.

Answer 2

Assuming that your main sheet is called "List", and looks as follows:

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

Then, on the sheet for company "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

What about producing a list of just the contacts expiring and then using free DataPig Excel Explosion Add-in. [wizzard to split up data based on a column into the seperate organisation workbooks]? I don't think there is a version for excel 2007 but I have seen examples of macros about the web that do the same thing.