Help filtering data

[alexdrums123]

Hi all,

I have a big headache of an excel problem which I need help with.

Sample of the file is attached.

I have IDs on the left and what I want to do is pull out the Maximum energy consumption (in column J) for each ID. (I have highlighted the rows i would want to extract in the example sheet). I also need to extract out all the other column information in the row so that I have all the values related to that ID. (i.e i need the whole row!)

I can get the max values for each ID using a pivot table, but cannot get the rest of the values that match it using this method. It is a very big file (i have given a small sample for size ease of solving) so i need to find an automated method.

Please help! It would be such a life saver.

Thankyou

Alex

[jeffreybrown]

Hi Alex,

Here's one way without having to resort to any VBA code.

In V2 copied down...

=IF(J2=MAX(IF($A$2:$A$25=A2,$J$2:$J$25)),J2,"")

IMPORTANT

• This is an array formula
• Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
• If entered correctly, the formula will be enclosed in {brackets}
• Do not enter the {brackets} manually

Now filter Column V and delete

EDIT:

I only have 2007, but I think the Function MAXIF is in 2010 plus. This way you don't need the CSE.

http://www.excelfunctions.net/excel-...-function.html

[alexdrums123]

Thanks Jeff - this works on a small sample, but on a very large dataset seems to be breaking down.

[jeffreybrown]

What exactly do you mean by breaking down? Large dataset, how large?

Did you try what I offered or switch to the MAXIF? The MAXIF could be faster than the CSE formula which can bog down a big dataset.

[benishiryo]

@jeffrey:
i think MAXIF is only available in Excel 2016 onwards. i hope that doesn't make us less useful here.

@alex:
you can also make use of the PivotTable to give you the Max value of ID, and then vlookup from there.
=VLOOKUP(A2,PT!A:B,2,0)=J2
filter TRUE

otherwise, record a macro.
1. sort data by ID, then by column J in descending order.
2. remove duplicates by ID

this is the raw code recorded (based on a copy of the worksheet Sheet1):

Please Login or Register  to view this content.

3. you just need to change a little bit of the code to make the rows dynamic according to the last row and based on the activesheet.
add in this to find last row

Please Login or Register  to view this content.

change ActiveWorkbook.Worksheets("Sheet1 (2)") to activesheet
change 25 (your current last row) to a variable & LR

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[alexdrums123]

Hi Jeffrey,

It worked in the end - it was the processing which was taking so long - it took around 2 hours for the formula to work over the 300,000 + cells. Ben thank you also for your very helpful suggestion, i will be trying to integrate this as well to make things easier.