Wrong Skips return.

[serge]

Hi,


I'm having a small problem with this formula, with unic digits it works fine but now, I have duplicate digits, it's not calculating the right way can it be fix ?



This formula calculate the skips of a digit and when reappear, it start at 1.


The red cells are highlighted in green normally, but I put them in red for you to see better.




Cell P9 should be a 2 not 1
Cell P10 should be a 3 not 1


Cell P12 should be a 2 not 1
Cell P17 is highlighted and shouldn't maybe need to correct my CF ?
Cell P18 should be a 6 not 1
Cell P19 should be a 7 not 2

The rest look Ok it seems to just happen in the first column !

Can it be fix ?
Thank you.

Excel Workbook
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
2							1	2	3	4	5	6	7	8	9	10
3
4
5
6
7	1	1	3	7	9		1	1	1	1	1	1	1	1	1	1
8	1	3	4	5	7		1	2	1	2	2	2	1	2	1	2
9	3	4	7	8	10		1	3	1	1	1	3	1	3	2	3
10	1	1	2	7	10		1	4	1	1	2	4	1	1	3	1
11	2	3	9	10	10		1	1	2	2	3	5	1	2	4	1
12	1	2	2	6	7		1	1	1	3	4	6	2	3	1	1
13	5	5	6	8	9		1	1	2	4	5	1	1	4	2	2
14	4	6	7	7	8		2	2	3	5	1	1	2	1	1	3
15	2	2	4	7	8		3	3	4	1	2	1	1	1	2	4
16	3	3	5	5	6		4	1	5	1	3	2	1	1	3	5
17	4	6	8	10	10		5	2	1	2	1	1	2	2	4	6
18	2	6	6	6	8		1	3	2	1	2	1	3	1	5	1
19	1	2	6	6	8		2	1	3	2	3	1	4	1	6	2
Sheet

 

[Russell Hauf]

Are you really referring to P7 from within P7?

 

[myall_blues]

Hi

Can you clarify the logic here? You say "when reappear, it start at 1". By that logic, P10 should be 1, because J10:N10 contains a 1, P11 should be 2, P12 should revert to 1? Likewise T8 should be 1 (because J8:N8 contains a 5), and T9 should be 2?

If I have expressed it correctly try this formula in P7 and copy down/right.

=IF(ISERROR(HLOOKUP(P$2,$J7:$N7,1,FALSE)),P6+1,1)

Note that the TRUE clause of the IF statement should refer to P6, not P7, to avoid a circular reference.

Regards

Murray

 

[serge]

Thank you for your responses guys.
It's almost there.

When I scroll down and the formula rich the digit it must show the total skip not a 1.

The problem I have is that there is duplicate in the same row but should be consider a unique value not a duplicate under that base row P2:Y2.

So for example :
L14, M14 there is 2 digits 7 and in cell V14 it show a Skip of 2 for those 2 digits 7.

Same for J15,K15 there is 2 digits 2 and in cell Q15 it return a skip of 3.

So those 2 digits are consider been one only.

It works fine from Q:Y but not for column P ?

I hope I'm explaining it right ?

 

[Russell Hauf]

Would you mind posting the formulas in columns Q-Y, to help us better understand?

 

[Marcelo Branco]

Are these the expected results?

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

T​

U​

V​

W​

X​

Y​

1​

2​

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

3​

4​

5​

6​

7​

1​

1​

3​

7​

9​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

8​

1​

3​

4​

5​

7​

1​

2​

1​

2​

2​

2​

1​

2​

1​

2​

9​

3​

4​

7​

8​

10​

1​

3​

1​

1​

1​

3​

1​

3​

2​

3​

10​

1​

1​

2​

7​

10​

2​

4​

1​

1​

2​

4​

1​

1​

3​

1​

11​

2​

3​

9​

10​

10​

1​

1​

2​

2​

3​

5​

1​

2​

4​

1​

12​

1​

2​

2​

6​

7​

2​

1​

1​

3​

4​

6​

2​

3​

1​

1​

13​

5​

5​

6​

8​

9​

1​

1​

2​

4​

5​

1​

1​

4​

2​

2​

14​

4​

6​

7​

7​

8​

2​

2​

3​

5​

1​

1​

2​

1​

1​

3​

15​

2​

2​

4​

7​

8​

3​

3​

4​

1​

2​

1​

1​

1​

2​

4​

16​

3​

3​

5​

5​

6​

4​

1​

5​

1​

3​

2​

1​

1​

3​

5​

17​

4​

6​

8​

10​

10​

5​

2​

1​

2​

1​

1​

2​

2​

4​

6​

18​

2​

6​

6​

6​

8​

6​

3​

2​

1​

2​

1​

3​

1​

5​

1​

19​

1​

2​

6​

6​

8​

7​

1​

3​

2​

3​

1​

4​

1​

6​

2​

If i'm right try

put 1 in P7, O7......Y7

Formula in P8 copied across and down
=IF(ISERROR(FIND("|"&P$2&"|","|"&$J7&"|"&$K7&"|"&$L7&"|"&$M7&"|"&$N7&"|")),P7+1,1)

Hope this helps

M.

 

[serge]

Thank you very much Marcelo, this formula work very well, I really appreciate the help.
The CF for the first column doesn't work right for some reason, Oh well.

Thanks again.
Serge.

 

[serge]

Thanks to you also Murray and Russell, But I didn't know how to explain better than what I did.
Serge.

 

[Marcelo Branco]

Thank you very much Marcelo, this formula work very well, I really appreciate the help.
The CF for the first column doesn't work right for some reason, Oh well.

Thanks again.
Serge.

@ Russell
Thanks for the Like

@Serge
You are welcome. Glad to help

M.