# Thread: Count Specific Occurrence (2003)

1. ## 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. ## 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. ## 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
•