Formula for data sorting

sv29

New member
Joined
Feb 2, 2017
Messages
7
Reaction score
0
Points
1
Excel Version(s)
2016
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
 

Attachments

  • example.xlsx
    8.8 KB · Views: 49
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.
 
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.
 
Does anyone else have any inputs/ideas?

Thanks
 
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.
 
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
 

Attachments

  • example.xlsx
    9.5 KB · Views: 7
Thank you Alansidman, i like you last suggestion the best, i believe this will work.
 
Back
Top