EXCEL - EXTRACTING DATA INTO A COLUMN

[susejdrol]

Hey Guys,

I am looking at trying to create team lists and pull data from a master Spreadsheet. For Example, I could have:

Name Lead
Bob Sam
Frank Sally
Henry Sally
John Sam
Sue Sally
Betty Sam

Then on another worksheet, I will have colums headed as Sam and Sally and it should look on the first worksheet and list each person on that team. So I would end up with:

Sam Sally
Bob Frank
John Henry
Betty Sue

Does anyone have any idea how I would go about writing the formulas for the second page?

Thanks

CP

 

[janetlyn]

I believe when I typed it in, I would just go ahead and create your columns and as you enter names, put them under the right column. If you want something more complicated I would use Access for this application. JL

 

[useractive]

Another option might be to try a Pivot Table depending on it's size, use the two colums (name and lead). Underneath the layout tab have the row data be LEAD, the column data be NAME, and the DATA be count of name. Under the options you can deselect the totals for rows and colums because that won't matter. Then it's just a matter of choosing who you's team you want to view under the drop down box. Let me know if you need me to shoot this to you in an email for an example.

Good Luck,
Swish

 

[susejdrol]

I like that Pivot table idea. I am goign to play with that. I came up with a solution where I am required to have the list sorted and then I do it by matching the lead name to the lead name in the master list and then incrementing and indexing to list out the entire team until I come to a person who has a differant lead, and then I stop. Of course, this is somewhat inconvenient becuase it forces you to resort the list every time a change is made and it will destroy the results if you ever forget. The pivot table idea may be a better solution.

Thanks

CP