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

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

2. ## 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,""))))

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

4. ## Re: Formula for data sorting

Thank you Nishant94, this appears to work.

## User Tag List

#### Posting Permissions

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