Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count Specific Occurrence (2003)

    I download bank data to a spreadsheet that will list dates in one column, and the amount of the deposit in the next. Often, there are multiple deposits on the same date. Manually, I have been concatenating a Customer identifier to the date, and appending a letter on the end of that date, to create a unique identifier for each transaction (to be uploaded in an accounting system). Thus, if I have 3 deposits for May 2nd, and my Customer ID is CBO, I would have the following, CBO050207A, CBO050207B, and CBO050207C.

    I am trying to figure out how to take the manual steps out, even if I have to add a couple of columns with formulas to do it. I see where I can use COUNT, or COUNTIF, to get the number of times a date appears in the range of dates, but not sure how to get the number of occurrence of, say the third time 05/02/2007 appears in the range. My thinking is that if I can get a formula to tell me that, referring the example above, that THIS occurrence of 05/02/2007 is the = 3, I can easily lookup "C" and append it to my unique identifier.

    Perhaps my approach is wrong. Any help appreciated.
    Steve

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Specific Occurrence (2003)

    Let's say fhe dates are in column A, starting in A2 (with A1 being a column header).
    Enter the following formula in a cell in row 2:
    <code>
    ="CBO"&TEXT(A2,"mmddyy")&CHAR(65+COUNTIF(A$1:A1,A2 ))
    </code>
    and fill down as far as needed.

  3. #3
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Specific Occurrence (2003)

    Wow! Perfect. I really have a lot to learn.

    Thanks for the help--never would have used that CHAR part. Lovely solution
    Steve

Posting Permissions

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