Filtering based on multiple columns

[shahab.arif]

I have a large spreadsheet with test data and I'm trying to extract rows with max values. The below example will help explain better

To start I have a table like this, each cycle has multiple readings and I only want the readings from the max "Cap Ah" values.

Cycle	Cap Ah	V	A
1	0.3	a	b
1	0.8	c	d
2	1	e	f
2	1.2	f	g
3	0.2	h	i
3	0.8	k	m

The end result would look like this

Cycle	Cap Ah	V	A
1	0.8	c	d
2	1.2	f	g
3	0.8	k	m

I can't figure out where to start with this, I have thousands of rows so doing it manually isn't an options.

[TMS]

Use an AutoFilter. It has an option for "Top 10" but you can adjust the number that you want.

[shahab.arif]

Use an AutoFilter. It has an option for "Top 10" but you can adjust the number that you want.

Thanks for your reply TMS. The filter does give me the highest values but I need it restricted to only one for each Cycle. In the above example, for Top 3 it'd give me rows 3,4,5 instead of row 3,5,9

[finalazy]

Hi, I have attached a sample for you. see if it works out for you.

[shahab.arif]

Hi, I have attached a sample for you. see if it works out for you.

Thanks for your reply, I appreciate the time you spent developing the example.

This works exactly as I need it but seems to break after 1000 rows. When I change =MAX(IF($A$2:$A$1000=F2,$B$2:$B$1000,"")) to =MAX(IF($A$2:$A$3000=F2,$B$2:$B$3000,"")) to include more rows it comes up with #VALUE! error. The Cycle column is fine but Cap Ah error's out. I've tried lots of combinations but can't figure out why that error comes up when the rows are increased

[finalazy]

Did you ctrl shift enter after you change the row number? Its an array formula.

[shahab.arif]

Thanks finalazy, that was it. It's working as I need it.

Thank you very much for your help.

[finalazy]

Thanks finalazy, that was it. It's working as I need it.

Thank you very much for your help.

Glad it helps. You are welcome =)

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.