# Wrong Skips return.

#### serge

##### Well-known Member
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
JKLMNOPQRSTUVWXY
212345678910
3
4
5
6
7113791111111111
8134571212221212
93478101311131323
101127101411241131
1123910101122351241
12122671113462311
13556891124511422
14467782235112113
15224783341211124
16335564151321135
1746810105212112246
18266681321213151
19126682132314162
Sheet

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Russell Hauf

##### MrExcel MVP
Are you really referring to P7 from within P7?

#### myall_blues

##### New Member
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

##### Well-known Member
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 ?

Last edited:

#### Russell Hauf

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

#### Marcelo Branco

##### MrExcel MVP
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

##### Well-known Member
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

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

#### Marcelo Branco

##### MrExcel MVP
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.

Last edited:

Replies
1
Views
300
Replies
1
Views
220
Replies
10
Views
634
Replies
8
Views
1K
Replies
2
Views
546

1,190,638
Messages
5,982,073
Members
439,753
Latest member
mnyankee

### 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.

### Which adblocker are you using?

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

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