Excel can do it! Past Occurrence.

[CMLET]

[TABLE="width: 222"]
<tbody>[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]10608[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]10607[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]10606[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]10605[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]10604[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]10603[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]10602[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]10601[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]10600[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]10599[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]10598[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]10597[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]10596[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]10595[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]10594[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]10593[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


Hi Mr Excel. Past Occurrences. Sample: Columns A1 through G1. Column D rows are numbered in reverse, 1 to current 10608. Hoping to create a formula to number the columns, E, F, and G that correspond with columns A,B and C.

Example: A1 cell is 5. Counting down to row A4 the cell is 5. Basically I'm counting down 3,3 5. Ignore the duplicate 3,

count 1-2. The corresponding column, E1 cell is numbered 2. Sample two: A2 cell is 3 and the cell below that A3 is 3. In the corresponding column, E2 the cell its numbered 1. Another example; A7 cell is 4. Corresponding column E7 cell is 5. Count down from A7: 4,3,5,1,2,4. Thank you. CMLET.

 

[Peter_SSs]

Welcome to the MrExcel board!

I don't understand the logic.

A7 is 4. The next 4 in column A is A12. OK, I understand that I7 might be 5 as there are 5 rows before the next identical number.

Apply the logic to A1. There are 3 rows before the next identical number. But your answer is 2, not 3. So perhaps your logic is how many time the number in column A actually changes. A1 changes to 3 in A2, stays the same in A3 and 3 changes to 5 in row 4. So the value has changed twice in returning to the value of 5. That would give the answer as 2 - as you have given.

But if we apply that logic to A2 (=3), then the number of changes before 3 again occurs in column A is zero. So why isn't the result in E2 = 0?

Clearly I am not understanding the "rules". Can you clarify further?

Also, how would the values in A15 & A16 be arrived at when there is no further similar data below?

 

[Special-K99]

@Peter_SSs:

I'm wondering if it's the number of unique values before the number repeats again (including the repeated number).

I agree with A15 A16 and no further data.

UPDATE: No cant be unique values as A8 is 3 and E8 is 5 yet 3 doesnt appear again. And it doesnt work for several other values below A8.
So we can ignore unique values.
Guess we'll have to wait and see what OP comes up with.

It's very annoying when a problem is depicted but OP doesn't provide an explanation for the logic.

 

[Peter_SSs]

I'm wondering if it's the number of unique values before the number repeats again (including the repeated number).

UPDATE: No cant be unique values as A8 is 3 and E8 is 5 yet 3 doesnt appear again.

Actually, you were probably right with your first assessment. The values lower in the table are probably only failing because the data has been truncated. If 3 is the next unique number in column A, then 5 would be the correct return for the 3 in cell A8.

Unfortunately, I am about to be away for a few days so probably won't be able to look at this again until next week.