find and sort data in rows

[Calathia]

I have some data in non arranged order and I need to rearrange it in different place (see example below). The logic is:

- first row below "orange themes" cells look C1:C5 for "orange" and returns first occurrence
- second row below "orange themes" cells look C1:C5 for "orange" and returns second occurrence
- etc

here is example I made:

I have been scratching my head to figure out excel formula to do that, but with no success. All the help would be appreciated!

 

[Calathia]

anyone?

 

[Peter_SSs]

Welcome to the MrExcel board!

anyone?

You will almost certainly get faster answers in the future if you provide your sample data in a form that can copied & pasted to test with as most helpers are not that keen on manually typing out such data. My signature block below has further help about that.

Could you use something like this?
Formula in F2 is copied down.
Formula in H2 is copied across and down.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	cherries	15	red	3			orange
2	oranges	18	orange	6		2		oranges	18	orange	6
3	apples	16	green	7		5		oranges	5	orange	10
4	apples	10	red	7
5	oranges	5	orange	10
6							red
7						1		cherries	15	red	3
8						4		apples	10	red	7
9
10
11							green
12						3		apples	16	green	7
13
Sort Data

Cell Formulas
Range	Formula
F2	=IFERROR(AGGREGATE(15,6,(ROW(C$1:C$5)-ROW(C$1)+1)/(C$1:C$5=LOOKUP("zz",G$1:G1)),ROW()-LOOKUP("zz",G$1:G1,ROW(G$1:G1))),"")
H2	=IF($F2="","",INDEX(A$1:A$5,$F2))

 

[Calathia]

Thank you Peter_SSs! That worked woders!

Could that be extended into 2 field requirements?

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]cherries
[/TD]
[TD]15
[/TD]
[TD]red
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]green
[/TD]
[TD]apples
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]oranges
[/TD]
[TD]18
[/TD]
[TD]orange
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]apples
[/TD]
[TD]16
[/TD]
[TD]green
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]apples
[/TD]
[TD]16
[/TD]
[TD]green
[/TD]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]apples
[/TD]
[TD]10
[/TD]
[TD]red
[/TD]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD]red
[/TD]
[TD]apples
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]oranges
[/TD]
[TD]5
[/TD]
[TD]orange
[/TD]
[TD]10
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]apples
[/TD]
[TD]10
[/TD]
[TD]red
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]apples
[/TD]
[TD]3
[/TD]
[TD]red
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]apples
[/TD]
[TD]3
[/TD]
[TD]red
[/TD]
[TD]6
[/TD]
[/TR]
</tbody>[/TABLE]

1 datarow (in row 2) is displayed under "green apples" because it has fields "green" and "apples"
2 datarows(in row 4 and 5) are displayes under "red apples" because it has fields "red" and "apples"


I tried to extend your functions to account two requirements but couldn't succeed.

 

[Peter_SSs]

Thank you Peter_SSs! That worked woders!

Good news.
.. and thanks for providing 'copyable' data this time.

Could that be extended into 2 field requirements?

Like this?
F2 down and I2 across & down

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	cherries	15	red	3			green	apples
2	oranges	18	orange	6		3			apples	16	green	7
3	apples	16	green	7
4	apples	10	red	7			red	apples
5	oranges	5	orange	10		4			apples	10	red	7
6	apples	3	red	6		6			apples	3	red	6
7
Sort Data (2)

Cell Formulas
Range	Formula
F2	=IFERROR(AGGREGATE(15,6,(ROW(C$1:C$6)-ROW(C$1)+1)/((C$1:C$6=LOOKUP("zz",G$1:G1))*(A$1:A$6=LOOKUP("zz",H$1:H1))),ROW()-LOOKUP("zz",G$1:G1,ROW(G$1:G1))),"")
I2	=IF($F2="","",INDEX(A$1:A$6,$F2))

 

[Calathia]

That seems to work!

However, I have question:

- how do I change formula that other listing parameter is number instead of text? now first parameter is "green" and second is "apples" (result is row 3) but if wanted first parameter as number 7 and second parameter "apples" (result is rows 3 and 4)

I tried to change it myself but "zz" input for lookup table is hebrew to me :D

 

[Peter_SSs]

- how do I change formula that other listing parameter is number instead of text? now first parameter is "green" and second is "apples" (result is row 3) but if wanted first parameter as number 7 and second parameter "apples" (result is rows 3 and 4)

There are a few things that need changing
- The formula needs to be directed to look at column D for the & (or other numeric value) instead of column C for red, green etc
- When looking for numeric values the "zz" needs to be changed to a number larger than any possible in your data. I've used 10 to the power of 9.

The formula in F2 below is now
=IFERROR(AGGREGATE(15,6,(ROW(C$1:C$6)-ROW(C$1)+1)/((D$1:D$6=LOOKUP(10^9,G$1:G1))*(A$1:A$6=LOOKUP("zz",H$1:H1))),ROW()-LOOKUP(10^9,G$1:G1,ROW(G$1:G1))),"")

The formulas in columns I:L are unchanged.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	cherries	15	red	3			7	apples
2	oranges	18	orange	6		3			apples	16	green	7
3	apples	16	green	7		4			apples	10	red	7
4	apples	10	red	7			6	oranges
5	oranges	5	orange	10		2			oranges	18	orange	6
6	apples	3	red	6
Sort Data (3)

 

[Calathia]

Thank you Peter, this worked!

 

[Peter_SSs]

Thank you Peter, this worked!

Good news. Thanks for letting us know.