CODEWITHSUNDEEP
excelexcel-formula
zkvvoob
zkvvoob

Reputation: 476

Return value with INDEX/MATCH and a logical test

I've got a table with unique values displayed in rows. I need to return the value the correct first login time for the day

     A        B         C            D
1    Session  User      Date         Time
2    1000     U1        3/6/2017     10:01
3    1234     U1        3/6/2017     12:00

As you can see, it's possible for one agent to log in twice for the day, and I only need the first time.

I figured I could use INDEX and MATCH with multiple criteria for the User and the Date, but how do I include a logical test to check the session with the lower number (later sessions are always with a higher session ID number)?

My formula so far looks something like this: 

=INDEX(A:D,MATCH("U1"&"3/6/2017",B:B&C:C,0),D:D)

I'm not actually using U1 and the date directly, but with cell references, so the syntax error is not a problem.

Also, I know this is an array formula, so it's confirmed with Ctrl+Shift+Enter.

However, I don't know how to continue from here and say check U1 in B:B and Date in C:C, but return only D;D for the lowest Session in A:A

Upvotes: 3

Views: 1363

Answers (5)

user4039065
user4039065

Reputation:

Try,

=INDEX(D:D, AGGREGATE(15, 6, ROW($1:$99)/((B$1:B$99="U1")*(C$1:C$99=DATE(2017, 3, 6))*(A$1:A$99=AGGREGATE(15, 6, (A$1:A$99)/((B$1:B$99="U1")*(C$1:C$99=DATE(2017, 3, 6))), 1))), 1))

This retrieves the lowest numerical session number for the supplied date then uses that in a similar wrapping formula to retrieve the time associated with the session, the user and the date. Remember to format the result as time; it will be originally retrieved as a raw number.

The "U1" and DATE(2017, 3, 6) could refer to a cell(s) containing the value. For the second, third, etc match, replace the , 1) (the k of AGGREGATE's SMALL subfunction) with the appropriate ordinal.

Upvotes: 0

Michael
Michael

Reputation: 4848

As an alternative to the formulas, you can create a PivotTable that automatically shows you the Session number and Time of the first login for each user each day, regardless of the order of the data:

PivotTable

  1. Insert PivotTable
  2. Add User, Date and Sessions to Row Labels (You can swap User and Date, but Sessions must be last)
  3. Add Time to Values
  4. Summarize Time by Min (Sum/Max actually have same result if Sessions are unique, because there's only 1 value per Session. Min just sounds nicer here.)
  5. Change Number format of Min of Time field to Time (Right click on Field, Value Field Settings, Number Format, Time)
  6. Change Report Layout to Tabular (On PivotTable Tools > Design tab)
  7. Change Report Layout to Repeat all Item Labels
  8. Remove Subtotals
  9. Remove Grand Totals
  10. Right click on Session field and select Filter > Top 10...
  11. Set to: Show Bottom 1 Items by Min of Time (This causes only the first Session to be displayed for each User/Date combination based on the earliest Time)

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

Your instinct to use INDEX and MATCH was right. If they are in time order there's no need to find a minimum anything, just find the first occurrence - which is exactly what MATCH does.

=INDEX($B$2:$B$3,MATCH(G2&H2,$C$2:$C$3&$D$2:$D$3,0))

Entered as an array formula (Ctrl+Shift+Enter rather than just Enter)

The result looks like this (see column I)

enter image description here

Upvotes: 2

R3uK
R3uK

Reputation: 14537

Enter this as an array formula (confirmed with Ctrl+Shift+Enter) :

=INDEX(A:D,
    MATCH(
        MIN(INDEX(A:D,MATCH("U1"&"3/6/2017",B:B&C:C,0))&"U1"&"3/6/2017"
        ,D:D&B:B&C:C,0)
        ,D:D)

In fact it's just getting the minimum of your initial function and matching it with the user and the day, to get your desired result! ;)

Upvotes: 0

ttaaoossuu
ttaaoossuu

Reputation: 7884

You can include an extra helper column IsFisrtLogin with formula like this:

=COUNTIFS($B:$B,$B2,$C:$C,$C2,$A:$A,"<"&$A2)=0

Then filter out only the rows where this formula evaluates to TRUE and you will get only the first logins per corresponding user and day (i.e. the lowest session number).

Upvotes: 2

Related Questions