extract all occurrences of one name and display the data on a new sheet

[superturtle]

Hi

I would like to extract from a massive list of names, all occurrences of one name and display the data on a new sheet.

for example;

Name Date Location
James 23/04 edinburgh
Jimmy 28/04 london
David 03/05 Belfast
Sarah 08/07 Glasgow
David 12/09 Birmingham
James 13/01 Manchester

So the output on the new sheet would be;

Name Date Location
James 23/04 edinburgh
James 13/01 Machester


I basically want to find where James has been and on which dates without having to scroll through 1000's of names.

Thank you for any help

[ragulduy]

Use the autofilter tool.

[superturtle]

thanks for the reply. I'm familiar with the auto-filter, but i was hoping for a solution that would automatically update sheet2 when i add new data to sheet1. I have about 100 names and it is very time consuming having to copy and paste the updated data into all sheets in order to auto-filter. I was hoping a formula could extract the information from sheet1 and display it on sheet2.

Please note for the number of names, I have a seperate sheet. 100 names = 100 sheets

[AlKey]

Use this array formula
in E2
and pull formula down and across

=IFERROR(INDEX(A:A,SMALL(IF($A$2:$A$7=$D$2,ROW($A$2:$A$7)),ROWS($A$2:A2))),"")

	A	B	C	D	E	F	G
1	Name	Date	Location		Name	Date	Location
2	James	23/04	edinburgh	James	James	23/04	edinburgh
3	Jimmy	28/04	london		James	13/01	Manchester
4	David	03/05	Belfast
5	Sarah	08/07	Glasgow
6	David	12/09	Birmingham
7	James	13/01	Manchester

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[ragulduy]

Hi - have a look at the attachment as an example. I've made the assumption that the dates are all unique in order to make the formula a bit simpler.

[superturtle]

i'm off to try it now and will update you on my success. Thanks heaps for your help.

[superturtle]

Iv'e tried it and it works. Thanks Ragulduy and Alkey for your expert help. This forum worked out better than what i thought it would after hours of trying various formulas

[AlKey]

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[Tony Valko]

Something to consider...

You already know that you're extracting the data related to "name" so why extract "name" with each record?

Maybe do it like this:

Data Range

	A	B	C
1	Data For	Date	Location
2	James	23/04	edinburgh
3		13/01	Machester

Where James in A2 is either manually entered or selected from a drop down list.

That's one less column of formulas that you'll need.