+ Reply to Thread
Results 1 to 4 of 4

Formula to assign a visit number

  1. #1
    Registered User
    Join Date
    07-04-2007
    Posts
    2

    Formula to assign a visit number

    Hi Everyone,

    Can someone help me please,
    I am trying to assign a visit number according to the date for each visitor based on their unique number.

    Visitor_ID....Visit_Date
    1................July 1/2013
    1................July 3/2013
    1................Aug 2/2013
    2................july 10/2013
    2................July 15/2013

    I would like to add a column to assign a visit number:

    Visitor_ID....Visit_Date...........Visit_number
    1................July 1/2013.........1
    1................July 3/2013.........2
    1................Aug 2/2013.........3
    2................july 10/2013........1
    2................July 15/2013........2

    I really appreciate your help,

    Thanks,
    Amanco

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formula to assign a visit number

    Hi,

    One way

    =COUNTIF($A$2:$A2,A2)

    copied down
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    07-04-2007
    Posts
    2

    Re: Formula to assign a visit number

    Thank you, it is working!

    can I ask one more question please,
    now how can I extract or advance filter only the last visit based on the visit ID and other columns in the sheet, for example:

    Visitor_ID....Visit_Date...........Visit_number....Data
    1................July 1/2013.........1....................Red
    1................July 3/2013.........2....................Blue
    1................Aug 2/2013.........3....................Orange
    2................july 10/2013........1....................Red
    2................July 15/2013........2....................Pink

    To:
    Visitor_ID....Visit_Date...........Visit_number....Data
    1................Aug 2/2013.........3....................Orange
    2................July 15/2013........2....................Pink

    Thanks again,
    Amanco

  4. #4
    Forum Contributor
    Join Date
    08-11-2012
    Location
    bengalur
    MS-Off Ver
    Excel 2003, 2007
    Posts
    152

    Re: Formula to assign a visit number

    Hi Amanco

    First sort visit_Number in descending order. Next go to advanced filter, click on Unique Records and then click ok. You will find below results:

    Visitor_ID....Visit_Date...........Visit_number....Data
    1................Aug 2/2013.........3....................Orange
    2................July 15/2013........2....................Pink

    Thanks
    Shyam

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to assign a word to each number?
    By kwall2011 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-08-2012, 07:43 AM
  2. Replies: 3
    Last Post: 04-03-2012, 02:52 PM
  3. [SOLVED] Re : Excel Experts E-letter (No. 19) ; POWER FORMULA TECHNIQUES, Re-Visit No. 2
    By tkt_tang@hotmail.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-14-2005, 11:06 AM
  4. Re : Excel Experts E-letter (No. 19, Re-Visit) ; POWER FORMULA TECHNIQUES
    By tkt_tang@hotmail.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-13-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1