How to pull first number in a series, based on group

[kbianco]

Hi! I am hoping for some help with the following. I'd like be able to return a price (column A) for the first row within a given group to a certain threshold (column C). In the attached, i'd like a code that returns the first % change that is below -10%. Then continue on to look at the next group.

For example, looking at the orange group we want to identify the orange cell. In the yellow group, we want to identify the yellow cell. Note, i copied the below table over from my spreadsheet.

Any thoughts?

Thanks!

Price	Group	%	% filter
19.26	Red	0%	0%
19.4	Orange	0%	0%
19.25	Orange	-1%	0%
18.98	Orange	-2%	0%
18.93	Orange	-2%	0%
18.97	Orange	-2%	0%
18.92	Orange	-2%	0%
18.83	Orange	-3%	0%
19	Orange	-2%	0%
19.16	Orange	-1%	0%
19.14	Orange	-1%	0%
18.11	Orange	-7%	0%
17.91	Orange	-8%	0%
18.11	Orange	-7%	0%
17.44	Orange	-10%	-10%
17.69	Orange	-9%	0%
17.64	Orange	-9%	0%
17.81	Orange	-8%	0%
17.91	Orange	-8%	0%
17.67	Orange	-9%	0%
17.59	Orange	-9%	0%
17.32	Orange	-11%	-11%
16.87	Orange	-13%	-13%
16.69	Orange	-14%	-14%
16.87	Orange	-13%	-13%
16.68	Orange	-14%	-14%
17.06	Orange	-12%	-12%
17.36	Orange	-11%	-11%
17.61	Orange	-9%	0%
17.59	Orange	-9%	0%
48.56	Yellow	-2%	0%
48.61	Yellow	-2%	0%
48.61	Yellow	-2%	0%
48.45	Yellow	-3%	0%
47.92	Yellow	-4%	0%
47.92	Yellow	-4%	0%
47.91	Yellow	-4%	0%
47.79	Yellow	-4%	0%
47.68	Yellow	-4%	0%
47.26	Yellow	-5%	0%
46.67	Yellow	-6%	0%
47.03	Yellow	-5%	0%
46.9	Yellow	-6%	0%
46.92	Yellow	-6%	0%
46.33	Yellow	-7%	0%
46.3	Yellow	-7%	0%
45.73	Yellow	-8%	0%
45.75	Yellow	-8%	0%
45.83	Yellow	-8%	0%
44.91	Yellow	-9.7%	0%
45.29	Yellow	-9%	0%
45.49	Yellow	-9%	0%
45.16	Yellow	-9%	0%
44.51	Yellow	-11%	-11%
43.89	Yellow	-12%	-12%
44.61	Yellow	-10%	-10%
44.64	Yellow	-10%	-10%
44.46	Yellow	-11%	-11%

 

[hagia_sofia]

Hello, could you please describe how the output should look like? Is it a column D or just a list of group + value?

 

[fahad_ibnfurjan]

conditional format
1 red
2 Orange
3 yelloww

ROW($A2)=MIN(IFERROR(ROW($A$2:$A$59)/((($B$2:$B$59=TRANSPOSE(INDEX(UNIQUE($B$2:$B$59),1)))*$D$2:$D$59)<>0),""))

hhhhhh.xlsx
	A	B	C	D
1	Price	Group	%	% filter
2	19.3	Red	0%	0%
3	19.4	Orange	0%	0%
4	19.3	Orange	-1%	0%
5	19	Orange	-2%	0%
6	18.9	Orange	-2%	0%
7	19	Orange	-2%	0%
8	18.9	Orange	-2%	0%
9	18.8	Orange	-3%	0%
10	19	Orange	-2%	0%
11	19.2	Orange	-1%	0%
12	19.1	Orange	-1%	0%
13	18.1	Orange	-7%	0%
14	17.9	Orange	-8%	0%
15	18.1	Orange	-7%	0%
16	17.4	Orange	-10%	-10%
17	17.7	Orange	-9%	0%
18	17.6	Orange	-9%	0%
19	17.8	Orange	-8%	0%
20	17.9	Orange	-8%	0%
21	17.7	Orange	-9%	0%
22	17.6	Orange	-9%	0%
23	17.3	Orange	-11%	-11%
24	16.9	Orange	-13%	-13%
25	16.7	Orange	-14%	-14%
26	16.9	Orange	-13%	-13%
27	16.7	Orange	-14%	-14%
28	17.1	Orange	-12%	-12%
29	17.4	Orange	-11%	-11%
30	17.6	Orange	-9%	0%
31	17.6	Orange	-9%	0%
32	48.6	Yellow	-2%	0%
33	48.6	Yellow	-2%	0%
34	48.6	Yellow	-2%	0%
35	48.5	Yellow	-3%	0%
36	47.9	Yellow	-4%	0%
37	47.9	Yellow	-4%	0%
38	47.9	Yellow	-4%	0%
39	47.8	Yellow	-4%	0%
40	47.7	Yellow	-4%	0%
41	47.3	Yellow	-5%	0%
42	46.7	Yellow	-6%	0%
43	47	Yellow	-5%	0%
44	46.9	Yellow	-6%	0%
45	46.9	Yellow	-6%	0%
46	46.3	Yellow	-7%	0%
47	46.3	Yellow	-7%	0%
48	45.7	Yellow	-8%	0%
49	45.8	Yellow	-8%	0%
50	45.8	Yellow	-8%	0%
51	44.9	Yellow	-9.70%	0%
52	45.3	Yellow	-9%	0%
53	45.5	Yellow	-9%	0%
54	45.2	Yellow	-9%	0%
55	44.5	Yellow	-11%	-11%
56	43.9	Yellow	-12%	-12%
57	44.6	Yellow	-10%	-10%
58	44.6	Yellow	-10%	-10%
59	44.5	Yellow	-11%	-11%
ورقة2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D59	Expression	=ROW($A2)=MIN(IFERROR(ROW($A$2:$A$59)/((($B$2:$B$59=TRANSPOSE(INDEX(UNIQUE($B$2:$B$59),3)))*$D$2:$D$59)<>0),""))	text	NO
A2:D59	Expression	=ROW($A2)=MIN(IFERROR(ROW($A$2:$A$59)/((($B$2:$B$59=TRANSPOSE(INDEX(UNIQUE($B$2:$B$59),2)))*$D$2:$D$59)<>0),""))	text	NO
A2:D59	Expression	=ROW($A2)=MIN(IFERROR(ROW($A$2:$A$59)/((($B$2:$B$59=TRANSPOSE(INDEX(UNIQUE($B$2:$B$59),1)))*$D$2:$D$59)<>0),""))	text	NO

 

[Peter_SSs]

I'd like be able to return a price (column A) for the first row within a given group to a certain threshold (column C). In the attached, i'd like a code that returns the first % change that is below -10%. Then continue on to look at the next group.

For example, looking at the orange group we want to identify the orange cell. In the yellow group, we want to identify the yellow cell.

Something like this?

25 03 21.xlsm
	A	B	C	D	E	F	G
1	Price	Group	%	% filter		Price	Group
2	19.26	Red	0%	0%		-	Red
3	19.4	Orange	0%	0%		17.44	Orange
4	19.25	Orange	-1%	0%		44.51	Yellow
5	18.98	Orange	-2%	0%
6	18.93	Orange	-2%	0%
7	18.97	Orange	-2%	0%
8	18.92	Orange	-2%	0%
9	18.83	Orange	-3%	0%
10	19	Orange	-2%	0%
11	19.16	Orange	-1%	0%
12	19.14	Orange	-1%	0%
13	18.11	Orange	-7%	0%
14	17.91	Orange	-8%	0%
15	18.11	Orange	-7%	0%
16	17.44	Orange	-10%	-10%
17	17.69	Orange	-9%	0%
18	17.64	Orange	-9%	0%
19	17.81	Orange	-8%	0%
20	17.91	Orange	-8%	0%
21	17.67	Orange	-9%	0%
22	17.59	Orange	-9%	0%
23	17.32	Orange	-11%	-11%
24	16.87	Orange	-13%	-13%
25	16.69	Orange	-14%	-14%
26	16.87	Orange	-13%	-13%
27	16.68	Orange	-14%	-14%
28	17.06	Orange	-12%	-12%
29	17.36	Orange	-11%	-11%
30	17.61	Orange	-9%	0%
31	17.59	Orange	-9%	0%
32	48.56	Yellow	-2%	0%
33	48.61	Yellow	-2%	0%
34	48.61	Yellow	-2%	0%
35	48.45	Yellow	-3%	0%
36	47.92	Yellow	-4%	0%
37	47.92	Yellow	-4%	0%
38	47.91	Yellow	-4%	0%
39	47.79	Yellow	-4%	0%
40	47.68	Yellow	-4%	0%
41	47.26	Yellow	-5%	0%
42	46.67	Yellow	-6%	0%
43	47.03	Yellow	-5%	0%
44	46.9	Yellow	-6%	0%
45	46.92	Yellow	-6%	0%
46	46.33	Yellow	-7%	0%
47	46.3	Yellow	-7%	0%
48	45.73	Yellow	-8%	0%
49	45.75	Yellow	-8%	0%
50	45.83	Yellow	-8%	0%
51	44.91	Yellow	-9.70%	0%
52	45.29	Yellow	-9%	0%
53	45.49	Yellow	-9%	0%
54	45.16	Yellow	-9%	0%
55	44.51	Yellow	-11%	-11%
56	43.89	Yellow	-12%	-12%
57	44.61	Yellow	-10%	-10%
58	44.64	Yellow	-10%	-10%
59	44.46	Yellow	-11%	-11%
kbianco

Cell Formulas
Range	Formula
F2:G4	F2	=LET(g,B2:B59,u,UNIQUE(g),HSTACK(BYROW(u,LAMBDA(r,TAKE(FILTER(A2:A59,(g=r)*(C2:C59<=-0.1),"-"),1))),u))
Dynamic array formulas.

 

[kbianco]

Something like this?

25 03 21.xlsm
	A	B	C	D	E	F	G
1	Price	Group	%	% filter		Price	Group
2	19.26	Red	0%	0%		-	Red
3	19.4	Orange	0%	0%		17.44	Orange
4	19.25	Orange	-1%	0%		44.51	Yellow
5	18.98	Orange	-2%	0%
6	18.93	Orange	-2%	0%
7	18.97	Orange	-2%	0%
8	18.92	Orange	-2%	0%
9	18.83	Orange	-3%	0%
10	19	Orange	-2%	0%
11	19.16	Orange	-1%	0%
12	19.14	Orange	-1%	0%
13	18.11	Orange	-7%	0%
14	17.91	Orange	-8%	0%
15	18.11	Orange	-7%	0%
16	17.44	Orange	-10%	-10%
17	17.69	Orange	-9%	0%
18	17.64	Orange	-9%	0%
19	17.81	Orange	-8%	0%
20	17.91	Orange	-8%	0%
21	17.67	Orange	-9%	0%
22	17.59	Orange	-9%	0%
23	17.32	Orange	-11%	-11%
24	16.87	Orange	-13%	-13%
25	16.69	Orange	-14%	-14%
26	16.87	Orange	-13%	-13%
27	16.68	Orange	-14%	-14%
28	17.06	Orange	-12%	-12%
29	17.36	Orange	-11%	-11%
30	17.61	Orange	-9%	0%
31	17.59	Orange	-9%	0%
32	48.56	Yellow	-2%	0%
33	48.61	Yellow	-2%	0%
34	48.61	Yellow	-2%	0%
35	48.45	Yellow	-3%	0%
36	47.92	Yellow	-4%	0%
37	47.92	Yellow	-4%	0%
38	47.91	Yellow	-4%	0%
39	47.79	Yellow	-4%	0%
40	47.68	Yellow	-4%	0%
41	47.26	Yellow	-5%	0%
42	46.67	Yellow	-6%	0%
43	47.03	Yellow	-5%	0%
44	46.9	Yellow	-6%	0%
45	46.92	Yellow	-6%	0%
46	46.33	Yellow	-7%	0%
47	46.3	Yellow	-7%	0%
48	45.73	Yellow	-8%	0%
49	45.75	Yellow	-8%	0%
50	45.83	Yellow	-8%	0%
51	44.91	Yellow	-9.70%	0%
52	45.29	Yellow	-9%	0%
53	45.49	Yellow	-9%	0%
54	45.16	Yellow	-9%	0%
55	44.51	Yellow	-11%	-11%
56	43.89	Yellow	-12%	-12%
57	44.61	Yellow	-10%	-10%
58	44.64	Yellow	-10%	-10%
59	44.46	Yellow	-11%	-11%
kbianco

Cell Formulas
Range	Formula
F2:G4	F2	=LET(g,B2:B59,u,UNIQUE(g),HSTACK(BYROW(u,LAMBDA(r,TAKE(FILTER(A2:A59,(g=r)*(C2:C59<=-0.1),"-"),1))),u))
Dynamic array formulas.

This is exactly what i need, thank you! do you mind sharing what the lower case letters reference? sorry i am not familair with the functions you are using.

 

[Fluff]

@kbianco In future please mark the post that contains the solution, rather than your post saying it works.
I have changed it for you this time.

 

[Georgiboy]

Here is an option I created earlier but got interrupted with work..

=LET(r,A2:C59,d,FILTER(r,INDEX(r,,3)<=-0.1),g,INDEX(d,,2),u,UNIQUE(g),HSTACK(XLOOKUP(u,g,g),XLOOKUP(u,g,INDEX(d,,1))))

 

[Peter_SSs]

This is exactly what i need, thank you!

You're welcome. Thanks for the follow-up.

do you mind sharing what the lower case letters reference?

The lower case letters (they don't have to be lower case) are names (like variables) that hold intermediate results that are used later (possibly multiple times) in the formula.
See the Help on the LET function for more information.