Keeping certain rows

[Darko_Giac]

Hi all,

I'm having some issues restructuring my data so that it will only display certain rows.

I have 3 columns (one for ID, one for program and one for end date). Since IDs will have multiple end dates, I would like to only keep the latest one within that program group by student.

I've attached a copy of a workbook with the current data set as well as the desired result.

Any help would be greatly appreciated.

Thanks!

D.

[kev_]

Here is one way

See attached worksheet with desired results returned

Create a helper column D in sheet "Sample" (MAX formula much simpler with name of student available on each row)
fornula in cell D2 copied down

Please Login or Register  to view this content.

in Sheet "Desired Result"
formula in cell D2 copied down

Please Login or Register  to view this content.

this is an array formula and must be confirmed {CTRL}{SHIFT}{ENTER}

and will look like this in the cell
{=MAX(IF(Sample!D:D=A2,Sample!C:C))}

you cannot enter the { } brackets manually - it will not work

MAXIFS
Function MAXIFS avoids the need for an array formula, but is not available in all versions of Excel
Try this formula (in sheet "Desired Result") in cell E2 copy down

Please Login or Register  to view this content.

It returns same as column D

[Darko_Giac]

Here is one way

See attached worksheet with desired results returned

Create a helper column D in sheet "Sample" (MAX formula much simpler with name of student available on each row)
fornula in cell D2 copied down

Please Login or Register  to view this content.

in Sheet "Desired Result"
formula in cell D2 copied down

Please Login or Register  to view this content.

this is an array formula and must be confirmed {CTRL}{SHIFT}{ENTER}

and will look like this in the cell
{=MAX(IF(Sample!D:D=A2,Sample!C:C))}

you cannot enter the { } brackets manually - it will not work

MAXIFS
Function MAXIFS avoids the need for an array formula, but is not available in all versions of Excel
Try this formula (in sheet "Desired Result") in cell E2 copy down

Please Login or Register  to view this content.

It returns same as column D

Thanks so much for the guidance! Helped a great deal