• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please help

M

Murari Rajagopalan

Member
Folks,
Need help. I am trying to mark the duplicate user id who have visited the stall based on the stall size.

This needs to be done at user wise. Every venue code has a different duplicate visit validity.
E.g if the user visit more than once for Stall number 75 within 24 hours from the first visit, then the second visit is not considered.

Please find attached the sample excel file with the logic for each venue. Could you please me the Macro or excel formula please ?
 

Attachments

  • Macro Sample.xlsx
    14.8 KB · Views: 1
Hi Murari,

Will your time column will have date, i.e. will you do on daily data or monthly data or yearly data.

Regards,
 
@Murari Rajagopalan

See the attached file, I had put a formula in Column E, which is an array formula, so must be confirmed with Ctrl+Shift+Enter and not just Enter.

I had also converted your logic table to a excel table and sort it in ascending order as per Venue code.

There is a discrepancy in the result of cell which I had marked as ORANGE and I feel as per the logic there is a typo in your sample.

Kindly, check the formula in your original file and write back.

Regards,
 

Attachments

  • Macro Sample (1).xlsx
    11.8 KB · Views: 5
Hello Misra,
We got another user case which was not covered earlier. Can you help me with this wherein based on the the first time visit validity period for a user id, can you help in changing all the visits irrespective of the venue code as "Not ok" if this is within the first venue code validity ? E.g Please refer 1083 user id and output required D column. the Row 6 should print OK, as the validity of 24 hours is over and hence ok visit. Row 7, Row 8 Row 9 has bee illustrated with a lesser validity period as first visit. Please help.
 

Attachments

  • Macro Sample (1).xlsx
    17.1 KB · Views: 1
Sure Misra. User 1083 came to the exhibition and visited stall 75 on 3/14/2014 10:30:32 AM. Stall 75 has a validity period of 24 hours. User 1083 then visited stall 20, 2 times and Stall 30, 1 time. All these visits are now happened in 24 hours validity window for the first time visit of the user to Stall 75.
In this case, we need to mark all the visits other than first visit as not ok and mark any visit for user 1083 after the 24 hours period as valid visits. Row 7, Row 8 Row 9 has bee illustrated with a lesser validity period as first visit. Please help. let me know, if you have further queries on this as well.
 

Attachments

  • Macro Sample (1).xlsx
    17.1 KB · Views: 0
One more question, that's means validity is first visit only, say 1083 first visited 75 so all the entries of 1083 in the entire database say 1000 line items 1083 items are 150, out of which 4 are inside 24 hours window and 146 are out of window. So 4 will be not ok and rest will be OK.

The ref. will be first visit only.

Is this correct?

Regards,
 
yes, thats correct. we need to take the first time visit for the calculation. Infact, I have uploaded the revised one sometime today. I have also uploaded with this response as well
 

Attachments

  • Macro Sample (1).xlsx
    17.1 KB · Views: 1
thank you very much Misra. Only small change. the excel is correct and red color highlighted one is for user id 1084. Also, as confirmed to you, please consider the first time visit for each user id and then take the venue code and then derive ok and not ok based on validity. so for 1084, the first visit is ok, second visit is not ok as this is less than 2 hours for the first visit venue code 20. For the third visit, its beyond 2 hours and hence it should be ok. Similarly for 1085 user id, both are ok as the first time visit validity is 8 hours and the second time visit is beyond 8 hours. Hope, I made myself clear. I have again attached the excel file based on the explanation given above.
Much appreciate with your help.
 

Attachments

  • Macro Sample (1).xlsx
    17.1 KB · Views: 3
@Murari Rajagopalan

As confirmed by you on your comment #16 the first visit is considered on basis of time and on not of appearance of first time in data. So on row 9 for 1084 visited first time as the time is 14-03-2014 00:45:32, as per this the venue code should be 75 and not 20.

Kindly, clarify.

Regards,
 
You must log in or register to reply here.
Back
Top