Formula help to extract rows meeting multiple criteria

Question

I have a spreadsheet with over 600k rows. I need to extract data based on multiple criteria and grab only the latest change numbers of each.

So an item number may have multiple entries based on quarter start dates and desc codes because it's been revised several times in that quarter but I just want the most recent one (highest change number) and that row returned or marked in a new column to then filter out.

Hope that makes sense.

I have the following columns. Column A (Desc Code) which has 12 different codes in it, then Column B (Item Number several thousand), Column C (Period Begin, Start of the quarters dating back to 1998) and then a Column H (Change Number). I need to basically pull "Each" row containing the highest change number, for each Item Number in each Period it was available for each code.

So basically The change numbers vary depending on how many changes the Item Number had in the quarter.

And each time there was a change there is a change number for each Item Number for Each Desc Code (12 rows for each).
Thanks.

Answer 1

You lost me somewhere near paragraph 4 but let's simplify things. If you just had two columns -- Item Number and Change Number -- and you had a record for each change, you could just use Excel's subtotal feature: at each change in Item Number, show the MAX of Change Number.

Use the same logic for your situation. Create a new column that combines your "category" criteria (item & desc, or item & period, or whatever), sort by it, then subtotal against that new column and return MAX of Change Number.

Edit:

Item Period Change
100    1      1
100    1      2
100    1      3
100    2      1
100    2      2

I'm not sure if this is how your data looks but let's use it as an example (and's lets forget about Desc Code for now). If you want to find the latest change by item and period, create a new column by combining the Item and Period columns. For example, insert a column (C) and use the formula: =A2&"_"&B2. Now your data looks like this:

Item Period I&P   Change
100    1    100_1    1
100    1    100_1    2
100    1    100_1    3
100    2    100_2    1
100    2    100_2    2

Now use Excel's subtotal feature (in the Data menu/ribbon, not the worksheet formula). Here's an example of what this looks like:

In your scenario, for the "At each change in" box, pick your new column (C), since that uniquely identifies the category you trying to identify (item AND period). "Use function" = Max. "Add subtotal to" = your Change Number column.

Click [OK] and Excel will add a new row with the maximum Change Number for each.