Filter Array Data using Formulas

[Pc1x1]

Hi Guys, I didn't find exactly what I was looking for, and maybe you can help.

Basically I have a document with thousands of lines of data. The data is organized across per row.

Columns; A, B, C, D, E, F, G.

A: Has a Name, we'll call it "Apple"

My array is from A2:G2000

Example:
[TABLE="width: 970"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]10/24[/TD]
[TD]Crate Lot 1[/TD]
[TD]AX1[/TD]
[TD]CA[/TD]
[TD]CA[/TD]
[TD]$35[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]10/24[/TD]
[TD]Create Lot 2[/TD]
[TD]AX1[/TD]
[TD]MD[/TD]
[TD]MD[/TD]
[TD]$35[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]10/26[/TD]
[TD]Box 1[/TD]
[TD]AX1[/TD]
[TD]FL[/TD]
[TD]FL[/TD]
[TD]$15[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]10/24[/TD]
[TD]Create Lot 7[/TD]
[TD]AX1[/TD]
[TD]IA[/TD]
[TD]IA[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]10/28[/TD]
[TD]Packet 1[/TD]
[TD]BX1[/TD]
[TD]TX[/TD]
[TD]TX[/TD]
[TD]$15[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]10/22[/TD]
[TD]Packet 5[/TD]
[TD]BX1[/TD]
[TD]CA[/TD]
[TD]CA[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD]... So on so forth for 2000 lines[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I'd like to create a formula that going down, will search the entire array from A2 to G2000,

And If I search for "Apple" and go down and organize the rows together. Then I'd do the same thing for Pear, Banana etc.

So If I was searching for "Apple" using the data above my output would need to be;


Apple 10/24 Crate Lot 1 AX1 CA CA $35
Apple 10/24 Create Lot 2 AX1 MD MD $35
Apple 10/24 Create Lot 7 AX1 IA IA $10

It will output in order, going down.

Hopefully this makes sense.


My Current Low Tech Solution:
Filter by "Apple" on Column A, then copy and paste the filtered values into the sheet.
Same thing for Pear, Banana etc.

I'd like to automate the process a bit further.

Thanks!

 

[inactiveuserps07]

@Pc1x1, maybe I'm missing something, but can't you just select the entire range of data and then use Filter & Sort > Custom Sort on Column A, Ascending?

Or you could format the entire range (including headers) as a table and then sort using the filter dropdown on Column A.

 

[Pc1x1]

@Pc1x1, maybe I'm missing something, but can't you just select the entire range of data and then use Filter & Sort > Custom Sort on Column A, Ascending?

Or you could format the entire range (including headers) as a table and then sort using the filter dropdown on Column A.

Hi Erik, correct, that is my current solution to the problem, however that makes me have to paste the results as values in another sheet, whereas if I could use a formula, it could be automated.

So it's not like I don't have a way to reach my goal, I'd just like a better way.

Thanks!

 

[inactiveuserps07]

@Pc1x1, I must not be clear on what you're trying to do. Sorting is "permanent" if you want it to be. Why are you needing to copy it to another sheet, if you can permanently rearrange it on the first sheet? Are you saying that you're trying to create a different sheet for each unique item name from Sheet 1 Column A?

 

[Pc1x1]

Because the data is from an output and is dynamic it changes every day. Theres alot of formulas and formatting that goes on. So on the actual output, or in the case I sort at a specific time, I do so on another sheet not to interfer with the raw data.