2 Columns of Data - Need looping identifier (1,2 or 3) when column combinations change

[Detroiter]

Oh Great & Powerful Mr Excel Gurus, please bestow your knowledge upon me, a humble desk jockey!

I have been tasked with creating a macro that will autopopulate a column in excel based on the values of two other columns (a store number (first column in example) and a date (second column in example)). The columns will be sorted by number (ascending) and then by date (oldest first). The third column will be populated with numbers 1,2 or 3.

The first time a store number appears along with the first date it appears with - it will be assigned a 1 in the third column. All instances of that store number/date combination will be assigned a 1 as well. If a second date appears with that store, it will be assigned a 2. If a third date appears with that store, it will be assigned a 3. If a fourth date appears with that store, it will be assigned a 1 - fifth date, 2 - sixth date, 3 etc etc looping 1-3 based on the date change for a particular store.

When the store number changes, the cycle repeats with the number/date combinations: 1,2,3,1,2,3 until a number has been assigned to all cells in the third column.

Example:

[TABLE="width: 50"]
<tbody>[TR]
[TD]10[/TD]
[TD]12/1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]12/1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]12/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]12/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]12/1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]12/1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]12/1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]12/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]12/20[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]12/20[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]12/25[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]12/1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]12/1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]12/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]12/20[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

I don't have much experience coding, but I suspect that a Loop with an If statement could be used to accomplish this. I haven't the slightest idea of where to start - could you all help me out?

Cheers!

 

[Peter_SSs]

That could certainly be done by a macro, but do you really need that?
What about this formula, copied down?

Excel Workbook
	A	B	C
2	10	12/1	1
3	10	12/1	1
4	10	12/15	2
5	10	12/15	2
6	22	12/1	1
7	22	12/1	1
8	31	12/1	1
9	31	12/15	2
10	31	12/20	3
11	31	12/20	3
12	31	12/25	1
13	44	12/1	1
14	44	12/1	1
15	44	12/15	2
16	44	12/20	3
Cycle codes

 

[Detroiter]

Wow, thank you Peter! That worked like a dream

Could you tell me why you thought of using the MOD function? To be honest, it's the first time I've seen it used and I'm not quite sure how it's working in the cell.

Regardless, you've solved my issue and again, thank you so very much!

Cheers from Detroit!

 

[ask2tsp]

please insert the following code in the code module for the page that has the list:
1. Start the Visual Basic editor (press ALT+F11)
2. Locate the project explorer panel on the left. If you don't see it press Ctrl+R
3. Double click the sheetname where the list is located
4. A code window comes up. In this window paste the following

Option Explicit


Sub mark123()
   Dim sheetRow      As Long
   Dim storeNr       As Integer
   Dim prevStoreNr   As Integer
   Dim cat123        As Integer
   Dim storeDate     As Date
   Dim prevStoreDate As Date
   
   sheetRow = 2 'assume column header
   prevStoreNr = 0
   
   Do
      storeNr = Me.Cells(sheetRow, 1)
      storeDate = Me.Cells(sheetRow, 2)
      
      If storeNr > prevStoreNr Then
         prevStoreNr = storeNr
         prevStoreDate = storeDate
         cat123 = 1
      
      ElseIf storeDate > prevStoreDate Then
         prevStoreDate = storeDate
         cat123 = cat123 + 1
         If cat123 = 4 Then cat123 = 1
      End If
      
      Me.Cells(sheetRow, 3) = cat123
      sheetRow = sheetRow + 1
   Loop Until Me.Cells(sheetRow, 1) = ""
End Sub

 

[Peter_SSs]

Wow, thank you Peter! That worked like a dream

That's good, thanks for letting us know.

Could you tell me why you thought of using the MOD function?

Because you want to cycle through 3 different values. MOD returns the remainder when a number is divided by another number (the divisor).
Mod of any number using a divisor of 3 will always return 0, 1 or 2 - that is 3 values. Add 1 to those and you get the choices you want.


If you did want to do this via code, you could use this formula structure as part of that:

Sub CycleCodes()
  With Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IF(RC[-2]=R[-1]C[-2],MOD(R[-1]C-1+(RC[-1]<>R[-1]C[-1]),3)+1,1)"
    .Value = .Value
  End With
End Sub