Compressing a table

[Vikas02d]

Hi, I have another problem I am trying to deal with.

I have a table that I am trying to compress to capture the latest instance based on each ID.

For example, let us say this is my data set:

ID___ | Name | Level_ | Score | Date
ID-32 | John | Beginr | 56.00 | 27-Jan
ID-33 | Jill | Beginr | 32.00 | 28-Jan
ID-34 | Bill | Beginr | 78.00 | 28-Jan
ID-34 | Bill | Interm | 39.00 | 29-Jan *
ID-33 | Jill | Beginr | 62.00 | 31-Jan
ID-32 | John | Interm | 61.00 | 02-Feb
ID-33 | Jill | Interm | 67.00 | 03-Feb *
ID-35 | Mike | Beginr | 82.00 | 03-Feb
ID-35 | Mike | Interm | 85.00 | 04-Feb
ID-35 | Mike | Advncd | 89.00 | 05-Feb *
ID-32 | John | Advncd | 24.00 | 05-Feb
ID-32 | John | Advncd | 52.00 | 08-Feb *

From this data set I wish to extract only the 4 entries with a "*" next to them. These entries give me the updated latest status for each student.

So the output table would look like:

ID___ | Name | Level_ | Score | Date
ID-32 | John | Advncd | 52.00 | 08-Feb
ID-33 | Jill | Interm | 67.00 | 03-Feb
ID-34 | Bill | Interm | 39.00 | 29-Jan
ID-35 | Mike | Advncd | 89.00 | 05-Feb

Thanks again for the help,

Best regards
Vikas

 

[nightcrawler23]

Is the Star in another column. If yes Then just apply filter to that column.

 

[Vikas02d]

No - The star is not available in any column; that is placed just to help the reader identify the result desired.

Thanks
Vikas

 

[nightcrawler23]

=OFFSET(B$1,MATCH(1,($A$2:$A$13=$A17)*($E$2:$E$13=MAX(--($A$2:$A$13=$A17)*$E$2:$E$13)),0),0)

Use the above formula in cell beside ID-32 and drag down and sideways(4 columns). I have assumed your data is in the range A1 to E13 including headers.

 

[nightcrawler23]

You have to enter the formula as an array.....Ctrl+Shift+Enter.
then drag down or sideways

Also the output table starts from A17(Header in A16).

 

[Vikas02d]

Thanks Nightcrawler; I have a few questions (pl pardon my inexperience here).

1. I use Excel 2010; through some internet search I gather that 2007 needed Conditional sum wizard to enabled manually. Do I need to worry about this aspect when using 2010? If so how? I could not find anything relevant.

2. Data with header in A1 to E13 is right. Shall I place the formula from F2? or A16? My current efforts all yeild in "#N/A".

Many Thanks
Vikas

 

[nightcrawler23]

1. I am not sure about your first doubt as I am using 2007

2. Your output will start from B17 as in A17 u will have ID-32

[A17]ID-32 [B17]Given Formula [C17-E17] drag B17 formula
....
....
[A20]ID-34 [B20-E20] Drag B17-E17 down

make sure to Ctrl+Shift+Enter the formula

 

[Vikas02d]

Thank you Very very much for your help!! This has been extremely helpful.

Best regards
Vikas