Find the latest info based on last used date when ids are repeated

[dibyendu.sharma]

Hi All - everyday now and then I have to check for the latest values and the latest item where someone has booked his/her timesheet. They can book under x number of unquie IDs which can also be used by n number of people. The idea is to find the last used unquie ID and related details of that ID , the date when an individual have used it. This runs for say 3000+ people and 500+ unquie IDs. I use a Pivot from the raw data to plot EmpID, Unique ID, Date and the Hours booked under these ID by the Individuals.


I am looking for a formula to make me easily recognize the latest value for a range of time.

Need your help on this immediately. Can't attach a file here..so giving a structure below:

[pre]

EmpID      UniqueID    1.1.13  2.1.13  3.1.13  4.1.13 5.1.13  6.1.13 ...........
12345678    1           9       9       9
12345678    2                                  9      9        9
12345678    24          3       3              3      3
12345678    15
12345678    14
12345678    16
51515621    3
51515621    1
51515621    6
51515621    7
51515621    8
23215501    1
23215501    3
23278658    8
23278658    1
23144486    8
23150913    9
23165529    13
23161445    12
23161445    11
23161445    5
23161445    6

[/pre]

 

[SirJB7]

Hi, dibyendu.sharma!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


In your example you want to find UniqueID=24, don't you? And which are the related details, the whole row 4 (3rd of data)?


Regards!

 

[dibyendu.sharma]

Hi SirJB7- Thanks for your reply, point noted on the Excel forum rules and other aspect of this beautiful forum.


Regarding my questions and your response. I am looking for the value 2 for Employee number 1234567 since the last time booked by him was 6.1.13 so this would be his latest Unique ID. This would apply for each and every individual. I think I was not clear in putting the question correctly. So I posted on a similar lookup query post. I have provided my requirement there as well as it seemed similar. Below is the link.


http://chandoo.org/wp/2010/11/08/vlookup-array-formula/


I have put in the scenario there, would really appreciate if you could take a look at my posting there.

 

[dibyendu.sharma]

Hi- I see this post being closed[SOLVED] . Is this being addressed in the other post. Just wanted to ensure I don't miss the solution.


Thanks

Dibs

 

[SirJB7]

@dibyendu.sharma

Hi!

Precisely for that I marked this as solved because of the new one created, so as to not disperse efforts from the contributors.

Regards!


EDITED (topic not closed, my mistake)

 

[NARAYANK991]

Hi Pablo ,


Can you please recheck your link ?


Narayan

 

[SirJB7]

@NARAYANK991

Hi!

Thanks for the catch, fixed.

Regards!