CODEWITHSUNDEEP
excelexcel-formula
shieldfire
shieldfire

Reputation: 1

Excel: get last date based on value in column

I've looked for some time and am confused as to how to do this properly. I need to get the lastest a value occured. I have this is two columns

Dates   User Ids
01/01/2019  abc10
01/01/2019  rim344
01/01/2019  foo770
01/01/2019  hocpoc
01/01/2019  con587
01/01/2019  chk123

Etc, this goes on for thousands of lines, with a total of 300 hundred different User Ids.

Now, I want to get the latest date user abc10 logged in. Preferably presented with the unique set of User Ids rather than the raw data. How the ... do I do this?

Upvotes: 0

Views: 1271

Answers (5)

shieldfire
shieldfire

Reputation: 1

Thanks all, I eventually got this working using the following 

=LOOKUP(2,1/(A2:A17040=E2),B2:B17040)

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

With data like:

enter image description here

The first step is to copy column B into column C. Then use the RemoveDuplicates feature in the Data tab to get the unique IDs. Then in D1 enter the array formula:

=MAX(IF(B:B=C1,A:A,""))

and copy downward:

enter image description here

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

Upvotes: 1

Maya Amiad
Maya Amiad

Reputation: 46

you can create a pivot table, where 'user_id' is in rows and 'dates' is in values, then go to value field settings (by clicking on 'dates' in the pivot table fields window) and change the setting to 'Max' (instead of the default setting of 'count'). this way you will get a list of all users and their Max ride date :)

Upvotes: 1

Jerry
Jerry

Reputation: 71538

If you can use a pivot table, it will be more convenient I believe:

enter image description here

You basically need to select the data, insert a pivot table where you want it to be, put the User Ids under Rows and Dates under Values. Then Change the summurization for values to Max (right click on the values in the pivot table or in the value field settings in the right panel) and format as date.

You will instantly get a list of all users with their respective latest date.

Upvotes: 0

jsheeran
jsheeran

Reputation: 3037

If your version of Excel supports it (Office 365/Excel 2019), you can use MAXIFS:

=MAXIFS($A$2:$A$301,$B$2:$B301,"abc10")

Alternatively, you can use the following array formula (enter it with Ctrl+Shift+Enter):

=MAX($A$2:$A$301*--($B$2:$B$301="abc10"))

Upvotes: 0

Related Questions