login id change during the day?

[ze4lot]

Hi

Please help me, I wanna check that my employee's have changed their login id during the day at least once. Which formula can I use for that?

[ze4lot]

This is an example (sorry I dont know how to attach excel file):
Where the Expected result is 2 it means that there were two different logins on that day, where the result is 1, there was no change during the day.

Date transaction time Login id Expected result
2016.06.01 7:27 604 2
2016.06.01 8:24 604 2
2016.06.01 9:44 604 2
2016.06.01 11:28 604 2
2016.06.01 12:36 604 2
2016.06.01 14:12 602 2
2016.06.01 14:57 602 2
2016.06.01 15:08 602 2
2016.06.01 15:16 602 2
2016.06.01 15:58 602 2
2016.06.01 16:02 602 2
2016.06.01 16:16 602 2
2016.06.01 17:17 602 2
2016.06.01 17:55 602 2
2016.06.01 20:21 602 2
2016.06.01 20:25 602 2
2016.06.02 7:21 604 2
2016.06.02 7:50 604 2
2016.06.02 7:59 604 2
2016.06.02 8:53 604 2
2016.06.02 9:57 604 2
2016.06.02 10:34 604 2
2016.06.02 10:42 604 2
2016.06.02 11:10 604 2
2016.06.02 11:19 604 2
2016.06.02 11:35 604 2
2016.06.02 13:22 604 2
2016.06.02 13:53 606 2
2016.06.02 13:54 606 2
2016.06.02 14:24 606 2
2016.06.02 15:14 606 2
2016.06.02 15:33 606 2
2016.06.02 16:04 606 2
2016.06.02 16:14 606 2
2016.06.02 17:08 606 2
2016.06.02 18:55 606 2
2016.06.02 19:20 606 2
2016.06.02 19:47 606 2
2016.06.02 21:11 606 2
2016.06.02 21:28 606 2
2016.06.03 6:19 602 1
2016.06.03 7:30 602 1
2016.06.03 8:22 602 1
2016.06.03 8:27 602 1
2016.06.03 8:28 602 1
2016.06.03 8:53 602 1
2016.06.03 8:58 602 1
2016.06.03 9:08 602 1
2016.06.03 11:13 602 1
2016.06.03 11:17 602 1
2016.06.03 12:12 602 1
2016.06.03 12:25 602 1
2016.06.03 12:29 602 1
2016.06.03 13:11 602 1
2016.06.03 13:15 602 1
2016.06.03 13:48 602 1
2016.06.03 14:02 602 1
2016.06.03 14:14 602 1
2016.06.03 14:49 602 1
2016.06.03 16:06 602 1
2016.06.03 16:51 602 1
2016.06.03 16:53 602 1
2016.06.03 17:05 602 1
2016.06.03 17:31 602 1
2016.06.03 17:44 602 1
2016.06.03 17:47 602 1
2016.06.03 18:21 602 1
2016.06.03 18:31 602 1
2016.06.03 18:49 602 1
2016.06.03 18:52 602 1
2016.06.03 18:55 602 1
2016.06.03 19:01 602 1
2016.06.03 19:32 602 1
2016.06.03 20:05 602 1
2016.06.03 20:05 602 1
2016.06.03 20:21 602 1
2016.06.03 20:47 602 1

[AliGW]

T attach a sample workbook:

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Please explain your expected results above.

[shukla.ankur281190]

Assuming your Dates & Time in A column and login id is in B column then try

C1=COUNTIFS($A$1:A1,A1,$B$1:B1,B1) and drag down.

It will take care if Same Employee loging in same date twice then it will deliver 2.

If you have any problem the please attach workbook with expected result.


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[ze4lot]

Thanks attachment here with the Countifs its still not giving me the good result :/

[AliGW]

What COUNTIFS formula is giving the wrong result? How did you arrive at your expected results?

[shukla.ankur281190]

Try

D2=SUMPRODUCT(--(IF(FREQUENCY(OFFSET($A$1,MATCH($A2,$A$2:$A$78,0),2,COUNTIFS($A$2:$A$78,$A2)),OFFSET($A$1,MATCH($A2,$A$2:$A$78,0),2,COUNTIFS($A$2:$A$78,$A2)))>0,1))) and drag down!!

[ze4lot]

yes that monster works :D thank you

[shukla.ankur281190]

Hppy to help you

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED and add reputation by clicking star * icon on your left corner of screen. Thanks.

[jason.b75]

=SUMPRODUCT(--(IF(FREQUENCY(OFFSET($A$1,MATCH($A2,$A$2:$A$78,0),2,COUNTIFS($A$2:$A$78,$A2)),OFFSET($A$1,MATCH($A2,$A$2:$A$78,0),2,COUNTIFS($A$2:$A$78,$A2)))>0,1)))

You have an unhealthy penchant for OFFSET.

Have you ever read http://www.decisionmodels.com/calcsecretsi.htm

Here's a non-volatile equivalent array formula

=SUM(IFERROR(1/COUNTIFS($A$2:$A$78,$A$2:$A$78,$A$2:$A$78,$A2,$C$2:$C$78,$C$2:$C$78),0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Or, if you just need to check if it has been changed on any given day rather than count the changes. (Note that this one only checks the first and last ID on any given day, so if they change during the day, then return to the original one, this formula will not see it).

=IF(INDEX(C:C,MATCH(A2,A:A,0))<>LOOKUP(A2,A:A,C:C),"Changed","Not Changed")

There are probably a few other methods that would work as well, I expect that the FREQUENCY method that Ankur suggested can be done without the use of OFFSET, but I haven't looked at it in detail.

[shukla.ankur281190]

You have an unhealthy penchant for OFFSET.

Have you ever read http://www.decisionmodels.com/calcsecretsi.htm

.

Actual I really didn't know about it thanks for letting me know. Let me also explain about your countifs function how did you do it??

Thanks a lot

[jason.b75]

The countifs method is not the best one to use, but it does work.

Frequency with out offset would be preferable, one way

=SUM(SIGN(FREQUENCY(IF($A$2:$A$78=A2,$C$2:$C$78),$C$2:$C$78)))

Array confirmed with shift ctrl enter.

For the countifs method to work, you have to compare each entire range to itself, then compare a key to a range (the date is the key so column A is referenced twice, comparing A2:A78 to A2:A78, then A2:A78 to A2), then trap #DIV/0 errors, and it gets even more messy when you have empty cells to contend with

Probably best left not explained in too much detail, it is something best practiced on a simpler scale first. (Running before you can walk kind of scenario).