Automation

jlowry81

New member
Joined
Aug 15, 2014
Messages
1
Reaction score
0
Points
0
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.

Donna56677889
Jake23344556
Matt98876554
Bert54433221

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

Donna56677889Jake23344556
Jake23344556Matt98876554
Matt98876554
Bert54433221

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!
 
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.
 
Back
Top