Automation

[jlowry81]

I receive data that is routinely the same, but mixed in a different order nearly every time. What I want to do is have a preset formula that will pull specific parts from the data and put them on the side. For example:

I might be given this data, but only want Jake and Matt's name and data.



Donna
56
67
78
89








Jake
23
34
45
56








Matt
98
87
65
54








Bert
54
43
32
21









So I'd like excel to transfer those Jake and Matt to one side along with their respective data, like the following.



Donna
56
67
78
89

Jake
23
34
45
56


Jake
23
34
45
56

Matt
98
87
65
54


Matt
98
87
65
54








Bert
54
43
32
21









Like I said though, names could be mixed up every time, so I need a formula that finds the Name and pulls it and its data to the side.

Thanks in advance!

[Bob Phillips]

In this suggestion, I am assuming that you have the data in A1:E5, and that you create a list of the names to include in M1:Mn

Then, elsewhere add this array formula to get an index into the data





=IFERROR(SMALL(IF(ISNUMBER(MATCH($A$1:$A$5,OFFSET($M$1,0,0,COUNTA($M:$M),1),0)),ROW($A$1:$A$5)-ROW($A$1)+1),ROW(A1)),"")

I put this in P1, which will be reference in the following formulae. Copy it down as far as you might need.

In P2, add this formula

=IF(P2="","",INDEX(A$1:A$5,$P2))

and copy down and across as far as you need.