[ms office - 2010] Formula - is this possible?

[MatthewL]

Imagine I have the following table

 

date,details

1/3/14,car

1/4/14,tv

1/5/14,water

1/5/14,car

1/6/14,tv

 

If I wanted to show the date the last entry for each category was in a separate sheet can I do this?

 

i.e. for TV it would be 1/6 and car would be 1/5, obviously this would change but is there a formula to show the last date of the details column which will be always the same line of text?

[NotSoFast]

Trivial if you put it in a database like Access, but it sounds like you're talking about Excel. If so, I can't think of an easy, non-hacky way.

[howartp]

I *think* vlookup/lookup should be able to help here - might need an intermediate spreadsheet that helps it work things out.

 

It's a while since I used lookups though.

[pcstru]

Assuming all the dates are in the past, if you add an additional column which contains a concatenation of the item and a numeric representation of the date and you sort the data, you can then use vlookup to get to the closest value of the item + the numeric value of now which should be the latest.

[Seb1780]

Imagine I have the following table

 

date,details

1/3/14,car

1/4/14,tv

1/5/14,water

1/5/14,car

1/6/14,tv

 

If I wanted to show the date the last entry for each category was in a separate sheet can I do this?

 

i.e. for TV it would be 1/6 and car would be 1/5, obviously this would change but is there a formula to show the last date of the details column which will be always the same line of text?

 

I would do this with a pivot table, used the "details" column as the rows in the table and put the date in the values field. You can then change the values field to show the maximum value only and format it to date format.

[NotSoFast]

Yes. Good idea.

[MatthewL]

Cheers for the replies, not had chance to look yet but a pivot table was what kept cropping up, will do some reading up on them as never used them before.