Formula for data sorting

svolk29

New Member
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.
ABCDEFGHIJKLMN
1transmittaldoc numberissued datereceived datedoc numberIFR issue dateIFR return dateIFA issued dateIFA return dateIFC issue dateIFC return date
2IFA-1111T12009-09-092009-11-09T1
3IFA-1234T22009-01-092009-06-09T2
4IFR-9876T12009-08-092009-08-12
5IFR-4321T22009-08-27
6IFC-1010T12009-10-10
7IFC-0101T2

<tbody>
</tbody>

<tbody>
</tbody>
Note this topic is cross-posted on another site, however have been having trouble receiving a viable option;
https://www.excelguru.ca/forums/showthread.php?10227-Formula-for-data-sorting

Thanks
 
Last edited:

RasGhul

Well-known Member
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;

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;color: #FFFFFF;background-color: #4472C4;;">transmittal</td><td style="text-align: right;color: #FFFFFF;background-color: #4472C4;;">doc number</td><td style="text-align: right;color: #FFFFFF;background-color: #4472C4;;">issued date</td><td style="text-align: right;color: #FFFFFF;background-color: #4472C4;;">received date</td><td style="text-align: right;;"></td><td style="text-align: right;color: #FFFFFF;background-color: #ED7D31;;">doc number</td><td style="text-align: right;color: #FFFFFF;background-color: #ED7D31;;">IFR issued date</td><td style="text-align: right;color: #FFFFFF;background-color: #ED7D31;;">IFR return date</td><td style="text-align: right;color: #FFFFFF;background-color: #ED7D31;;">IFA issued date</td><td style="text-align: right;color: #FFFFFF;background-color: #ED7D31;;">IFA return date</td><td style="text-align: right;color: #FFFFFF;background-color: #ED7D31;;">IFC issued date</td><td style="text-align: right;color: #FFFFFF;background-color: #ED7D31;;">IFC return date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">IFA-1111</td><td style=";">T1</td><td style="text-align: right;;">09-09-09</td><td style="text-align: right;;">09-11-09</td><td style="text-align: right;;"></td><td style=";">T1</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">09-09-09</td><td style="text-align: right;;">09-11-09</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">IFA-1234</td><td style=";">T2</td><td style="text-align: right;;">09-01-09</td><td style="text-align: right;;">09-06-09</td><td style="text-align: right;;"></td><td style=";">T2</td><td style=";"></td><td style=";"></td><td style="text-align: right;;">09-01-09</td><td style="text-align: right;;">09-06-09</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">IFR-9876</td><td style=";">T1</td><td style="text-align: right;;">09-08-09</td><td style="text-align: right;;">12-08-09</td><td style="text-align: right;;"></td><td style=";">T1</td><td style="text-align: right;;">09-08-09</td><td style="text-align: right;;">12-08-09</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">IFR-4321</td><td style=";">T2</td><td style="text-align: right;;">27-08-09</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">T2</td><td style="text-align: right;;">27-08-09</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">IFC-1010</td><td style=";">T1</td><td style="text-align: right;;">10-10-09</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">T1</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">10-10-09</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">IFC-0101</td><td style=";">T2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">T2</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=IF(<font color="Blue">B2="","",B2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=IF(<font color="Blue">$C2="","",IF(<font color="Red">LEFT(<font color="Green">$A2,3</font>)&" "&$C$1=I$1,$C2,IF(<font color="Green">$D2="","",IF(<font color="Purple">LEFT(<font color="Teal">$A2,3</font>)&" "&"Return Date"=I$1,$D2,""</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Nishant94

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">transmittal</td><td style=";">doc number</td><td style=";">issued date</td><td style=";">received date</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">doc number</td><td style=";">IFR issue date</td><td style=";">IFR return date</td><td style=";">IFA issued date</td><td style=";">IFA return date</td><td style=";">IFC issue date</td><td style=";">IFC return date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">IFA-1111</td><td style=";">T1</td><td style="text-align: right;;">2009-09-09</td><td style="text-align: right;;">2009-11-09</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">T1</td><td style="text-align: right;;">2009-08-09</td><td style=";"></td><td style="text-align: right;;">2009-09-09</td><td style=";"></td><td style="text-align: right;;">2009-10-10</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">IFA-1234</td><td style=";">T2</td><td style="text-align: right;;">2009-01-09</td><td style="text-align: right;;">2009-06-09</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">T2</td><td style="text-align: right;;">2009-08-27</td><td style=";"></td><td style="text-align: right;;">2009-01-09</td><td style=";"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">IFR-9876</td><td style=";">T1</td><td style="text-align: right;;">2009-08-09</td><td style="text-align: right;;">2009-08-12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">IFR-4321</td><td style=";">T2</td><td style="text-align: right;;">2009-08-27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">IFC-1010</td><td style=";">T1</td><td style="text-align: right;;">2009-10-10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">IFC-0101</td><td style=";">T2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet9</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">INDEX(<font color="Green">$C$2:$D$7,,MATCH(<font color="Purple">"*"&MID(<font color="Teal">I$1,5,5</font>)&"*",$C$1:$D$1,0</font>)</font>),MATCH(<font color="Green">$H2&LEFT(<font color="Purple">I$1,3</font>),$B$2:$B$7&LEFT(<font color="Purple">$A$2:$A$7,3</font>),0</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top