Excel: create sortable compound ID

All, I asked a question "Excel VBA: Sort, then Copy and Paste" and received two excellent answers. However, because I failed to provide sufficient user requirements, they won't work: I asked for a fix to the existing solution I created, instead of specifying the actual business need and seeing if anyone has a better way.

(sigh) Here goes:

My boss asked me to create a ss to log issues. He wants a compound ID that concatenates the "Assigned Date" with a number that indicates what number issue it is for that day only. A new day, the count must restart at 1. E.g.:

Assigned     Issue   Concatenated
  Date    &  Count =      ID

5/11/2011 &    1   =   5112011-1
5/11/2011 &    2   =   5112011-2
5/11/2011 &    3   =   5112011-3
5/12/2011 &    1   =   5122011-1

I solved this with a hidden column C that calculates =IF(D2<>D1,1,C1+1), thus calculating the Issue Count by incrementing the previous issue count if the assigned date in column D is the same as the previous date, and starting over at 1 when the date changes. Another column concatenates the assigned date and the issue count, and I have my issue ID.

Quick, easy, elegant, in, out, and done. Right? But when I delivered the ss, he pointed out that if you (that is, he) sorts any part of the spreadsheet, the issue ID goes out of sequence. Of course---each formula isn't referencing the previous date in sequence if the rows are sorted out of Assigned Date order.

My imme开发者_StackOverflowdiate thought, which prompted my previous question, was to first re-sort the Assigned Date order correctly, then copy and paste the value of the calculated Issue Count to lock it in, and thus preserve the concatenated ID.

The only other way I can see to do this (in VBA, natch) is to:

1. evaluate all the dates in the Assigned Date column
2. evaluate all the numbers in the Issue Count column
3. calculate the latest sequential Issue Count for an a new item assigned on a given Assigned Date
4. Assign that sequential Issue Count to the new item
5. It'd be nice to then place the cursor into the next cell that the user would ordinarily go to, which would be the one right adjacent to the just-entered Assigned Date; however, that isn't necessary

That would avoid the need to re-sort the physical ss. However, besides a hazy guess that this would involve VLOOKUP, I got nothing. I couldn't find anything through searching.

Can anyone help? Or suggest a place to go? Thanks!!!

---

Sounds like you just want to automate a Paste Special action. The following replaces the formulas in a1:a100 with their calculated values:

   Set src = ActiveSheet.Range("a1:a100")

    src.Copy
    src.Select

    Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False

---

I think the formula =IF(D2<>D1,1,C1+1) could be improved as this relies on dates being in order. The following will preserve the count for any order that is sorted

Assume

     ColA           ColB                   ColC
Row1 Assigned_Date  Issue Count            Concatenate
Row2 05/11/2011     =COUNTIF($A$1:A2,A2)   =TEXT(A2,"ddmmyyyy")&"-"&B2
Row3 05/11/2011     =COUNTIF($A$1:A3,A3)   =TEXT(A3,"ddmmyyyy")&"-"&B3
Row4 05/12/2011     =COUNTIF($A$1:A4,A4)   =TEXT(A4,"ddmmyyyy")&"-"&B4
Row5 05/11/2011     =COUNTIF($A$1:A5,A5)   =TEXT(A5,"ddmmyyyy")&"-"&B5

Essentially enter B2 and C2 formulae and drag down. You might need to swap ddmmyyyy to mmddyyyy as we use dates first rather than months :)

Also, note the locking of the first part of the range only using $ - $A$1:Ax

This works perfectly for your current question but does not work if the Issue Count is assigned in time order per date.

---

How about using a procedure? Just click a button to add the next entry.

I've assumed that the entries will be given today's date and that the sheet layout is: Rows: 1 = Title / 2 = left blank / 3 = Headings of the data block Columns: A = Date / B = Issue Count / C = Combined ID / D etc = other data

Sub AddEntry()
Dim iDayRef As Long, iNumRows As Long, n As Long

With Range("A3")
    iNumRows = .CurrentRegion.Rows.Count
    For n = 2 To iNumRows
        If .Cells(n, 1).Value = Date Then
            If .Cells(n, 2).Value > iDayRef Then iDayRef = .Cells(n, 2).Value
        End If
    Next
    .Cells(iNumRows + 1, 1).Value = Date
    .Cells(iNumRows + 1, 2).Value = iDayRef + 1
    .Cells(iNumRows + 1, 3).Value = Format(Date, "mm/dd/yyyy") & " - " & iDayRef + 1
    .Cells(iNumRows + 1, 4).Select
End With
End Sub

And do you really need three columns for Date, Count, and Combined ID? If you went with a

yyyy/mm/dd - xx

ID format, one column could replace all three, and you could easily sort on it.