Extract rows with max values

[pthwaites]

Hi all,

In the attached picture, the first column shows a word which was used as a stimulus word for a language experiment. In the rows are response words given to these stimuli. I want to extract the row for each stimulus word which has the maximum column E value. I would like Excel to return this to me in a new sheet, which would contain only one row per stimulus word. For example, the row for the stimulus word "a" would contain only "an" (and the data from each of the other 3 columns); the row for "aardvark" would contain only "animal", and the row for "abacus" would contain only "math".

Can anyone help?

Thanks

Peter

 

[Fluff]

Hi & welcome to MrExcel.
How about
=FILTER(sheet1!A2:E25,(sheet1!E2:E25=MAXIFS(sheet1!E2:E25,sheet1!A2:A25,sheet1!A2:A25)))

 

[pthwaites]

Hi & welcome to MrExcel.
How about
=FILTER(sheet1!A2:E25,(sheet1!E2:E25=MAXIFS(sheet1!E2:E25,sheet1!A2:A25,sheet1!A2:A25)))

Thanks for the reply. This works out fine for "a", but the problem is that the complete table comprises around 480,000 rows and around 12,000 stimulus words. I can't therefore manually enter data ranges for each stimulus word (e.g. A2:A25). I need excel to figure out for itself what the appropriate range for each stimulus word is.

 

[Fluff]

No need, that formula will do it all for you

+Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	a	1	c1	d1	0.013026					a	3	c3	d3	0.084837
3	a	2	c2	d2	0.057423					b	24	c24	d24	0.096188
4	a	3	c3	d3	0.084837
5	a	4	c4	d4	0.059011
6	a	5	c5	d5	0.035917
7	a	6	c6	d6	0.064637
8	a	7	c7	d7	0.017451
9	a	8	c8	d8	0.027143
10	a	9	c9	d9	0.012106
11	a	10	c10	d10	0.009854
12	b	11	c11	d11	0.065155
13	b	12	c12	d12	0.015561
14	b	13	c13	d13	0.05744
15	b	14	c14	d14	0.037995
16	b	15	c15	d15	0.086806
17	b	16	c16	d16	0.067836
18	b	17	c17	d17	0.086431
19	b	18	c18	d18	0.052592
20	b	19	c19	d19	0.012281
21	b	20	c20	d20	0.026032
22	b	21	c21	d21	0.074172
23	b	22	c22	d22	0.086053
24	b	23	c23	d23	0.010159
25	b	24	c24	d24	0.096188
26
Report

Cell Formulas
Range	Formula
J2:N3	J2	=FILTER(A2:E25,(E2:E25=MAXIFS(E2:E25,A2:A25,A2:A25)))
Dynamic array formulas.

 

[pthwaites]

No need, that formula will do it all for you

Oh wow, that's amazing! But it now appears that I don't know how to paste the formula into my new sheet. If I simply paste it into multiple cells, all I get is the results for "a". If I fill down to other rows, I just get the same results for "a". How do I insert the formula into the sheet so that it applies to all of my stimulus words?

 

[Fluff]

You just put it into one cell, you will need to adjust the ranges to cover your entire data

 

[pthwaites]

You just put it into one cell, you will need to adjust the ranges to cover your entire data

I'm confused. I said that there is too much data for me to manually enter the data ranges - I can't adjust 12,000 formulas. But you said that the formula would do that for me. What have I misunderstood?

 

[Fluff]

You just need to extend the ranges in the formula to cover the entire data, so if your data is from A2 to E500, you would use
=FILTER(Sheet1!A2:E500,(Sheet1!E2:E500=MAXIFS(Sheet1!E2:E500,Sheet1!A2:A500,Sheet1!A2:A500)))

 

[pthwaites]

You just need to extend the ranges in the formula to cover the entire data, so if your data is from A2 to E500, you would use
=FILTER(Sheet1!A2:E500,(Sheet1!E2:E500=MAXIFS(Sheet1!E2:E500,Sheet1!A2:A500,Sheet1!A2:A500)))

Success! Thank you so much for your help!

 

[Fluff]

You're welcome & thanks for the feedback