Help Sorting Data From One Tab To Another

Hello,
I’m in the process of creating a multilayered spreadsheet that sorts and organizes data but I lack the ability to make it a reality. I’m seeking help with creating the formulas to make the worksheet work the way I intend it to. I’m breaking down what I need into three phases to make it easier to understand how the it’s supposed to work. Below is a question pertaining to the first phase.

Phase 1
The first phase of the worksheet is to transfer a portion of data from one tab to another. The worksheet consists of two tabs, Original and Sort. I want to import a certain portion of data from the Original tab to the Sort tab.

As in the example below:
You can see two tabs in the same worksheet and as stated I only want a portion of the data from the Original tab to be imported into the Sort tab. That portion being the point on the Original tab labeled “START” to “FINISH”. All of column “D” to column “O” with the one caveat being that only the rows from Column “D” that have the “TRUE” value in them are to be transferred. As you can see in the example of the Sort tab the point from Colum “D” to column “O” are the only portion showing along with only the “TRUE” value rows taken from column “D”. I would like to be able to achieve this result using formulas only and not using VBA. The red portion is reserved for additional calculations that I plan on working on after the first portion is completed.

I envision a formula that checks if the value is TRUE is column "D" of the Original Tab and if not it looks for the next TRUE value and displays what that value is in the current cell. For example, "D4" and "D5" in the Original tab are FALSE but "D6" is TRUE so the formula would display the result for the pertaining cell which would be "D4" to "O4" being the result on the Sort tab. This would continue from row to row until for all rows on the Original tab. A formula that could be dropped and dragged for any number of cells would work best but I'm open to any suggestions to make this work most effectively.

Original Tab
1.jpg

Sort Tab
2A.jpg

Final note: Please see the attached spreadsheet for more information and a deeper breakdown of what I am trying to accomplish with a couple of Sort tab examples of how the data should be decided calculated based upon the Original tab. Also the actually spreadsheet contains much more data and factors. So I need a solution that can calculate the formulations quickly. Everything in the attached spreadsheet is calculated and entered by hand so it may contain errors. Hopefully my breakdown of what I require is sufficient explanation. Also, ignore the areas above and next to the sorted data on the Sort tab, it's for the other two phases.

Thank you for any and all help you can provide it would be greatly appreciated. Please feel free to ask any and all questions.

Hi,

In mandating no VBA you are seriously restricting yourself and complicating the whole process.
You could achieve all you want with a single line of VBA code which uses an Advanced Filter to extract your data.

You can try this formula. Copy it in cell A9 of your Sort sheet and and its editing with CTRL+SHIFT+ENTER keys (because this is an array formula) and copy it down and across.

=IFERROR(INDEX('Original Sample - Phase 1'!D$4:D$14,SMALL(IF('Original Sample - Phase 1'!$D$4:$D$14=TRUE,ROW('Original Sample - Phase 1'!$D$4:$D$14)-ROW(INDEX('Original Sample - Phase 1'!$D$4:$D$14,1,1))+1),ROW(A1))),"")

Note that this is an array formula and that if you have to copy it in a lot of cells, it will take some time calculating and it may make Excel to be slow.

As Richard stated, VBA would be the way to go. But this formula will give the results you wanted.

Originally Posted by artiststevens

I wanted to start by saying thank you for all of the input and help with solving this problem. Both Richard and Pierre are correct about using VBA and I am aware of the virtues of it and how it has been much easier to accomplish my desired result but I lack the skills to make changes if needed. Thank you all very much for help, especially Pete, the formulas you created worked exactly how I needed them to and even with the larger data set the calculating speed wasn't sluggish. The assistance with this is greatly appreciated.

Hi,

I understand your reluctance to get to grips with VBA, we've all been there. Nevertheless I'd urge you to give it a chance.
As I said a single line of code like

Range("your_data").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("your_output_range"), CriteriaRange:=Range("your_criteria_range")

will do the job.

In plain English this simply says "take my 'your_data range' and copy it to my 'your_output_range' using the range called 'your_criteria_range' to filter only the rows which meet the selection criteria.

will almost certainly do the job. And if you make the name 'your_data' a dynamic range name you needn't worry about having to change the range as new data is added so you will avoid any need to change the code at all.