Results 1 to 4 of 4

Thread: Formula for data sorting
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2011
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula for data sorting

    Hello

    I am looking for an excel formula to help me with data sorting, please see example below (sorry having trouble attaching an file so hopefully the table below makes sense);

    - 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.
    A B C D E F G H I J K L M N
    1 transmittal doc number issued date received date doc number IFR issue date IFR return date IFA issued date IFA return date IFC issue date IFC return date
    2 IFA-1111 T1 2009-09-09 2009-11-09 T1
    3 IFA-1234 T2 2009-01-09 2009-06-09 T2
    4 IFR-9876 T1 2009-08-09 2009-08-12
    5 IFR-4321 T2 2009-08-27
    6 IFC-1010 T1 2009-10-10
    7 IFC-0101 T2
    Note this topic is cross-posted on another site, however have been having trouble receiving a viable option;
    https://www.excelguru.ca/forums/show...r-data-sorting

    Thanks
    Last edited by svolk29; Sep 16th, 2019 at 10:57 PM.

  2. #2
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    589
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula for data sorting

    Hi svolk,

    Maybe you could use the following nested IF, note that I made all of the header names the same eg "issued" to match column C and I hard coded return date to match column D table;

    ABCDGHIJKLMN
    1transmittaldoc numberissued datereceived datedoc numberIFR issued dateIFR return dateIFA issued dateIFA return dateIFC issued dateIFC return date
    2IFA-1111T109-09-0909-11-09T109-09-0909-11-09
    3IFA-1234T209-01-0909-06-09T209-01-0909-06-09
    4IFR-9876T109-08-0912-08-09T109-08-0912-08-09
    5IFR-4321T227-08-09T227-08-09
    6IFC-1010T110-10-09T110-10-09
    7IFC-0101T2T2

    Sheet1



    Worksheet Formulas
    CellFormula
    H2=IF(B2="","",B2)
    I2=IF($C2="","",IF(LEFT($A2,3)&" "&$C$1=I$1,$C2,IF($D2="","",IF(LEFT($A2,3)&" "&"Return Date"=I$1,$D2,""))))


    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  3. #3
    Board Regular
    Join Date
    May 2015
    Location
    Kolkata, India
    Posts
    507
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for data sorting

    In cell J2:O3 use this custom format: [=0]"";yyyy-mm-dd

    ABCDEFGHIJKLMN
    1transmittaldoc numberissued datereceived datedoc numberIFR issue dateIFR return dateIFA issued dateIFA return dateIFC issue dateIFC return date
    2IFA-1111T12009-09-092009-11-09T12009-08-092009-09-092009-10-10
    3IFA-1234T22009-01-092009-06-09T22009-08-272009-01-09
    4IFR-9876T12009-08-092009-08-12
    5IFR-4321T22009-08-27
    6IFC-1010T12009-10-10
    7IFC-0101T2
    8
    9

    Sheet9



    Array Formulas
    CellFormula
    I2{=IFERROR(INDEX(INDEX($C$2:$D$7,,MATCH("*"&MID(I$1,5,5)&"*",$C$1:$D$1,0)),MATCH($H2&LEFT(I$1,3),$B$2:$B$7&LEFT($A$2:$A$7,3),0)),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Last edited by Nishant94; Sep 17th, 2019 at 01:52 AM.
    Regards,
    Nishant Ghosh

  4. #4
    New Member
    Join Date
    Jan 2011
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for data sorting

    Thank you Nishant94, this appears to work.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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