Results 1 to 2 of 2

Thread: Automation

  1. #1

    Automation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •