Excel Gurus

[abaez]

I have a worksheet that contains information like this in two separate columns:

Name 1 Securities & Capital Markets
Name 2 Employment
Name 3 Employment
Name 4 Global Projects
Name 5 Global Projects
Name 6 ERISA
Name 7 Intellectual Property
Name 8 Investment Management
Name 9 ERISA


In a separate worksheet, what I would like to get is a list of all of the names associated with a certain group from the first worksheet. So for the ERISA worksheet, I would get back Name 6 and Name 9 only and none of the others. So it would look like:

ERISA Names header
Name 6
Name 9

I've tried vlookups and just regular lookups, but I keep running into the problem of getting the same name and never getting the second name on the list. Is there some kind of programming I have to do for this?

 

[mb]

I don't have an easy answer for that at the moment.. but couldn't you just use autofilter for now?

 

[abaez]

Yes, that would work, but there are hundreds of names and the groups associated with them change constantly, so I'd have to do the filter each time I wanted to see the names in a group. Was hoping I could get the worksheets updated as the group associated with the name changes.

 

[abaez]

Bump.. anyone else have an idea? Or another way to do this?

 

[NeoV]

vlookup is always going to return the first value that it finds, so yah, that's not going to work

Pivot table for the win

PM me if you need help with a pivot table

 

[PowerEngineer]

Here's how I would approach this, but I warn you that it's not pretty...

I'll assume that your master worksheet with the names and groups are on Sheet 1 in columns A and B, starting in row 2.

On a second worksheet, put the group name you want in A1. Put this formula in column B, starting in row 2 (leave B1 empty) and going down as many rows as you (will ever) have entries on the master worksheet (call that NMAX). For B2:

=if(len(trim('Sheet1'!B2))>0,if(trim('Sheet1'!B2)=A1,max(b$1:b2)+1,0),0)

This should put a nonzero number in column B for every row where the group name matches the one you are looking for (and a zero in all other rows).

In C1:

=max(B:B)

should give you the number of matches you have.

Leave D1 empty. In D2:

=if(D1<C$1,D1+1,"")

Copy that down through NMAX rows. This should give you numbered rows from 1 to the number of matches.

in E2:

=if(D2>0,index('Sheet1'!A:A,match(D2,B:B,0),1),"")

Copy that down through NMAX rows. This should the list of names that match the group.

I apologize for any typos or grammerical errors; I didn't verify the formulas in excel.

The approach here is to sequentially number the matches in a column on the second worksheet (i.e. B) that is essentially parallel to the entries in the master worksheet. You can then easily form a table sized to the number of matches, starting with the indexes in column D and finishing up with the names in column E. Finding the row in column B where its value matches that of the 'numbered row' in the table gives you a row in the master worksheet that has the matching group. Using that to index into column A on the master gives you the name.

Good luck

 

[NeoV]

I'd add a third column, call it "counter" or something like that, and just put a "1" on each row...a pivot table would return this:

Sum of counter
Type Cat Total
Employment Name 2 1
Name 3 1
ERISA Name 6 1
Name 9 1
Global Projects Name 4 1
Name 5 1
Inttual Property Name 7 1
Inv Mgement Name 8 1
Sec & Cap Mkts Name 1 1
Grand Total 9

 

[abaez]

THANK YOU both. I used a mixture of the pivot table and formulas to get something pretty fancy (at least for me).