Excel can do it! Past Occurrence.

CMLET

New Member
Joined
Jul 31, 2018
Messages
3
[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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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?
 
Upvote 0
@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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top