Take whole parts of a list/table, based on one criteria?

Hi all,

So, I have this list showing different information based on three people names.
Each person has done some different contracts at a specific time and some other data.. the information is irellevant for now.
What I am struggling with is, to make a function which can pick out an entire line of information, from the table containing information for all people, and put that into a seperate sheet.

For instance.
I have three people: Christian, John and Louise.
The master table contains data showing what deals Christian, John and Louise have done individually - keeping an overview.

However, i need to have a sheet for each individual person --> A sheet for Christian, showing all the deals Christian has done. Likewise a seperate sheet for John and one for Louise.

Does it make sense?
Basically, what I need is that each individual sheet for each person, looks up the persons name in the "master" table, and then only picks the lines/data that the respective person is responsible for.
I need a function that picks the information based on the persons name, and puts the information in a seperate sheet, which will show a total overview of the persons data.

I tried to use VLOOKUP, but that doesn't really apply to this situation as I need an entire row of data to be shown, and not a specific cell only.

I've made a basic workbook which shows the conundrum (attached).

Does it have to be a formula?

Easiest way to do this would be to have
Christian!A1 = Sheet!A1
Then pull across & down to F43
Then filter column A for "Christian"

Ditto for other sheets; copy the table whole-sale and then just filter.



Another option might be, to create a Pivot table on the data, arrange it the way you want, and use "Person" as a filter; copy the tab with the Pivot Table and then just change who you filter it with.



Formula-wise is possible, but honestly, they'd be work-arounds to accomplish what you can do with these other methods. That's not to say it's impossible that you need to do it with functions, but I want to double-check that's really the case first.

hi ben_hensel,

Yeah, I also figured out that could be a way that works.
However, I am not a big fan of hidden cells/rows which will happen when you import the entire master list and then sort/filter on name

...I think you're trading half-a-dozen for five, but that's up to you, I suppose.

Try the following: It uses MATCH to list appropriate row numbers sequentially using INDIRECT to migrate ranges so you're always looking for the next one after the one above it.

Then it's just running an INDEX off that row number for each column.