Formula for data sorting

svolk29

New Member
Joined
Jan 8, 2011
Messages
5
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:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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;


Book1
ABCDGHIJKLMN
1transmittaldoc numberissued datereceived datedoc numberIFR issued dateIFR return dateIFA issued dateIFA return dateIFC issued dateIFC return date
2IFA-1111T109-09-0909-11-09T1 09-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
Cell Formulas
RangeFormula
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,""))))
 
Upvote 0
In cell J2:O3 use this custom format: [=0]"";yyyy-mm-dd


Book1
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
Cell Formulas
RangeFormula
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)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top