Results 1 to 7 of 7

Thread: Formula for data sorting

  1. #1
    Seeker sv29's Avatar
    Join Date
    Feb 2017
    Posts
    6
    Articles
    0
    Excel Version
    2016

    Formula for data sorting



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

    Hello

    I am looking for an excel formula to help me with data sorting, please see attached excel example;

    - the data table on the left is produced and dumped from internal software
    - what i am hoping to do is create a formula, which based on the document #'s lists the respective data dates along the same line
    - example for document T1; in cell I2 the formula would look for the document #, and if it contains IFR within the transmittal number (on the table at the left), it would produce IFR issued date (from table at the left). Thus the value of I2 would be 2009-08-09.
    - value in K3 would be 2009-01-09, etc.

    If anyone could provide some advise or possible other options for sorting the date, it would be greatly appreciated.

    Thanks
    Attached Files Attached Files

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    184
    Articles
    0
    Excel Version
    2019
    Highlight the first row of your table and on the Data Tab, select filter
    Click on the drop down arrow in Column A.
    Click on Text Filters
    Select Contains. In the new window, type in IFR
    Click OK.
    Copy your results and paste them where ever you require.

  3. #3
    Seeker sv29's Avatar
    Join Date
    Feb 2017
    Posts
    6
    Articles
    0
    Excel Version
    2016
    Thanks for the response alansidman, I agree this would work however very manual. Unfortunately the actual table I am working with contains thousands of rows of data and needs to be updated on a daily basis, thus require something more automated, hopefully a formula so can just dump the data in the left hand table and it automatically sorts to the desired output in the right hand table.

  4. #4
    Seeker sv29's Avatar
    Join Date
    Feb 2017
    Posts
    6
    Articles
    0
    Excel Version
    2016
    Does anyone else have any inputs/ideas?

    Thanks

  5. #5
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    184
    Articles
    0
    Excel Version
    2019
    Use the Macro recorder to follow the steps outlined. Post the code that the recorder provides and tell us what needs to be changed in the code to make it more malleable as there will be constants that you will need to change to variables.

  6. #6
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    184
    Articles
    0
    Excel Version
    2019
    Create a helper column in Column E Type =LEFT(A2,3) and copy down.

    In I2 type =INDEX($C$2:$C$6,MATCH(H2 & "IFR",$B$2:$B$6 &$E$2:$E$6,0)) and enter it by clicking on Control Shift and Enter to make it an array. You will end up with curly brackets {} surrounding the formula. Do not type these brackets as that will not work. Adjust the formula for the remaining cells across and then copy them down.

    You will need to add an IFError statement to the formula for blank results to show as nulls/blanks
    Attached Files Attached Files

  7. #7
    Seeker sv29's Avatar
    Join Date
    Feb 2017
    Posts
    6
    Articles
    0
    Excel Version
    2016
    Thank you Alansidman, i like you last suggestion the best, i believe this will work.

Posting Permissions

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