# Unique formula which can find delay for number 1 to 50 at once



## motilulla (Dec 24, 2022)

Using Excel 2010
Hello,

I am using following formula given by Fluff under this link Count delay of specific number after it finds reset to 0, and repeat counts

I have a few numbers of results in the columns “B” they are in between 1 to 50, In the column “C” there is formula which finds the delay of the number is in the cell “C5” to get the delay of the each numbers I have to change the number and put delay of their corresponding number in the columns “G:M”

Now I require a formula which find delay of each number automatically in the columns “G:M” in the cells G6:M55 as shown in the example below. (Also I want to mention that appearance for the each number max will be 10 times)

Does it is possible to have unique formula which can find the delay of each numbers?

Note: my request if I could be possible, I can use the given formula in the excel 2000 also.

MrExcel Question.xlsxABCDEFGHIJKLMN1234Few ResultDelayUniqueUnique1 Time2 Time3 Time4 Time5 Time6 Time7 Time5Few Result1NumCountDelayDelayDelayDelayDelayDelayDelay629 13161458732 2175849 322553945 43481181042 5098117 6539174528211215 733745051336 83148 921535 1031612 1111734 1221821 132199 1402033 152211161602250 1702310 1852413 1902520 2022623 2122726 2212835 2322924 242303 2503110 2623230 2703333 2803439 292351143043640 3113715 3223835 3343923 3414030 3534130 362428 3724345 381446 3914540 4044631 4114744 421487 4324933 4425029 4525149 461527 471534 482544 4955540 5025640 5718 5812 5918 6043 616 6211 6320 6424 656 668 6749 6848 6937 706 7150 724 7318 7448 7538 7626 7721 7822 7949 802 8130 8247 833 8441 8544 8649 8710 8832 8943 9033 919 9213 931589437 9536 9646 9718 986 9918 Sheet1Cell FormulasRangeFormulaG6G6=MATCH($E6,$B$6:$B$99,0)F6:F55F6=COUNTIF($B$6:$B$99,E6)C6:C99C6=IF($B6=C$5,ROW()-LOOKUP(2,1/(($B$5:$B5=C$5)+($B$5:$B5="Few Result")),ROW($B$5:$B5)),"")

Please help

Thank you all.

Regards,
Moti


----------



## HongRu (Dec 24, 2022)

Try.

G6

```
=IFERROR(MATCH($E6,$B$6:$B$99,0),"")
```
copy downward to the last row.

H6

```
=IFERROR(MATCH($E6,OFFSET($B$6:$B$99,SUM($G6:G6),),0),"")
```
copy to right and down


----------



## motilulla (Dec 24, 2022)

HongRu said:


> Try.
> 
> G6
> 
> ...


*HongRu, *Spot on! Formulas worked like a charm, as I request perfect, also it worked fine with Excel v2000. 

Thank you so much for resolving it.

Have a great weekend, good luck!

Kind Regards,
Moti


----------



## HongRu (Dec 24, 2022)

Glad to help.


----------



## Peter_SSs (Dec 24, 2022)

If you wanted to use a single formula and also avoid using the volatile function OFFSET, you could try this array-entered formula.

22 12 24.xlsmBCDEFGHIJKLM4Few ResultDelayUniqueUnique1 Time2 Time3 Time4 Time5 Time6 Time7 Time5Few Result1NumCountDelayDelayDelayDelayDelayDelayDelay62913161458    7322175      849322553     9454348118    104250       1176539174528  1215736374    13368392824    148921472     DelayCell FormulasRangeFormulaF6:F14F6=COUNTIF($B$6:$B$99,E6)G6:M14G6=IFERROR(SMALL(IF($B$6:$B$99=$E6,ROW($B$6:$B$99)-ROW($B$5)),COLUMNS($G:G))-SUM($F6:F6)+$F6,"")Press CTRL+SHIFT+ENTER to enter array formulas.


----------



## motilulla (Dec 24, 2022)

motilulla said:


> *HongRu, *Spot on! Formulas worked like a charm, as I request perfect, also it worked fine with Excel v2000.
> 
> Thank you so much for resolving it.
> 
> ...


*HongRu,* your given formula worked perfect with excel 2010, but changed a bit as below to work with both excel 2010 & excel 2000 versions.

Have a great weekend, good luck!

MrExcel Question.xlsABCDEFGHIJKLMN1234Few ResultDelayUniqueUnique1 Time2 Time3 Time4 Time5 Time6 Time7 Time5Few Result1NumCountDelayDelayDelayDelayDelayDelayDelay629 13161458    732 2175849 322553945 43481181042 50117 65391745281215 7363741336 8392824148 9214721535 103188561612 111571734 12211421821 1321968199 1402033 152725211161602250 1702310 185522142422413 1902520 20220382623 21213592726 221732835 23221132924 2422435303 2503110 26222493230 2703333 2803439 292144351143042781403640 311413715 3222813835 334151316413923 341124030 3531013104130 362882428 37264254345 38170446 391294540 4043191014631 411794744 4215487 43255294933 44242385029 4524345149 46191527 47177534 482636544 495343161275540 5021749Sheet1Cell FormulasRangeFormulaG6G6=IF(ISERROR(MATCH($E6,$B$6:$B$99,0)),"",MATCH($E6,$B$6:$B$99,0))H6:M6H6=IF(ISERROR(MATCH($E6,OFFSET($B$6:$B$99,SUM($G6:G6),),0)),"",MATCH($E6,OFFSET($B$6:$B$99,SUM($G6:G6),),0))C6:C55C6=IF($B6=C$5,ROW()-LOOKUP(2,1/(($B$5:$B5=C$5)+($B$5:$B5="Few Result")),ROW($B$5:$B5)),"")F6:F55F6=COUNTIF($B$6:$B$99,E6)

Kind Regards,
Moti


----------



## motilulla (Dec 24, 2022)

Peter_SSs said:


> If you wanted to use a single formula and also avoid using the volatile function OFFSET, you could try this array-entered formula.
> 
> 22 12 24.xlsmBCDEFGHIJKLM4Few ResultDelayUniqueUnique1 Time2 Time3 Time4 Time5 Time6 Time7 Time5Few Result1NumCountDelayDelayDelayDelayDelayDelayDelay62913161458    7322175      849322553     9454348118    104250       1176539174528  1215736374    13368392824    148921472     DelayCell FormulasRangeFormulaF6:F14F6=COUNTIF($B$6:$B$99,E6)G6:M14G6=IFERROR(SMALL(IF($B$6:$B$99=$E6,ROW($B$6:$B$99)-ROW($B$5)),COLUMNS($G:G))-SUM($F6:F6)+$F6,"")Press CTRL+SHIFT+ENTER to enter array formulas.


*Peter,* I like your single formula worked perfect with excel 2010, will go with this one, but changed a bit as below to work with both excel 2010 & excel 2000 versions.

Thank you so much for giving an alternative single formula.

Have a great weekend, good luck! 🥂

MrExcel Question.xlsABCDEFGHIJKLMN1234Few ResultDelayUniqueUnique1 Time2 Time3 Time4 Time5 Time6 Time7 Time5Few Result1NumCountDelayDelayDelayDelayDelayDelayDelay629 13161458    732 2175849 322553945 43481181042 50117 65391745281215 7363741336 8392824148 9214721535 103188561612 111571734 12211421821 1321968199 1402033 152725211161602250 1702310 185522142422413 1902520 20220382623 21213592726 221732835 23221132924 2422435303 2503110 26222493230 2703333 2803439 292144351143042781403640 311413715 3222813835 334151316413923 341124030 3531013104130 362882428 37264254345 38170446 391294540 4043191014631 411794744 4215487 43255294933 44242385029 4524345149 46191527 47177534 482636544 495343161275540 50217495640 5718 5812 5918 6043 616 6211 6320 6424 656 668 6749 6848 6937 706 7150 724 7318 7448 7538 7626 7721 7822 7949 802 8130 8247 833 8441 8544 8649 8710 8832 8943 9033 919 9213 931589437 9536 9646 9718 986 9918 Sheet2Cell FormulasRangeFormulaG6:M6G6=IF(ISERROR(SMALL(IF($B$6:$B$99=$E6,ROW($B$6:$B$99)-ROW($B$5)),COLUMNS($G6:G55))-SUM($F6:F6)+$F6),"",SMALL(IF($B$6:$B$99=$E6,ROW($B$6:$B$99)-ROW($B$5)),COLUMNS($G6:G55))-SUM($F6:F6)+$F6)F6:F55F6=COUNTIF($B$6:$B$99,E6)C6:C99C6=IF($B6=C$5,ROW()-LOOKUP(2,1/(($B$5:$B5=C$5)+($B$5:$B5="Few Result")),ROW($B$5:$B5)),"")Press CTRL+SHIFT+ENTER to enter array formulas.

Kind Regards,
Moti


----------



## Peter_SSs (Dec 24, 2022)

motilulla said:


> changed a bit as below to work with both excel 2010 & excel 2000 versions.


Ah, yes, I forgot about IFERROR not going back that far. You could try this much shorter one instead if you want.

22 12 24.xlsmBCDEFGHIJKLM4Few ResultDelayUniqueUnique1 Time2 Time3 Time4 Time5 Time6 Time7 Time5Few Result1NumCountDelayDelayDelayDelayDelayDelayDelay62913161458    7322175      849322553     9454348118    104250       1176539174528  1215736374    13368392824    148921472     DelayCell FormulasRangeFormulaF6:F14F6=COUNTIF($B$6:$B$99,E6)G6:M14G6=IF(COLUMNS($G:G)>$F6,"",SMALL(IF($B$6:$B$99=$E6,ROW($B$6:$B$99)-ROW($B$5)),COLUMNS($G:G))-SUM($F6:F6)+$F6)Press CTRL+SHIFT+ENTER to enter array formulas.


----------



## motilulla (Dec 24, 2022)

Peter_SSs said:


> Ah, yes, I forgot about IFERROR not going back that far. You could try this much shorter one instead if you want.
> 
> 22 12 24.xlsmBCDEFGHIJKLM4Few ResultDelayUniqueUnique1 Time2 Time3 Time4 Time5 Time6 Time7 Time5Few Result1NumCountDelayDelayDelayDelayDelayDelayDelay62913161458    7322175      849322553     9454348118    104250       1176539174528  1215736374    13368392824    148921472     DelayCell FormulasRangeFormulaF6:F14F6=COUNTIF($B$6:$B$99,E6)G6:M14G6=IF(COLUMNS($G:G)>$F6,"",SMALL(IF($B$6:$B$99=$E6,ROW($B$6:$B$99)-ROW($B$5)),COLUMNS($G:G))-SUM($F6:F6)+$F6)Press CTRL+SHIFT+ENTER to enter array formulas.


*Peter,* I just replace your new shorter formula it worked even great with value “0” fantastic! I modified the previous formula but which were not resulting correctly with “0” with this one all is fixed ok! 

Thank you so much for your kind help!

I want to wish you a Merry Christmas 🍾

Kind Regards,
Moti


----------



## Peter_SSs (Dec 24, 2022)

motilulla said:


> I just replace your new shorter formula it worked even great with value “0” fantastic!


Good news!



motilulla said:


> I modified the previous formula but which were not resulting correctly with “0”


I don't know what you mean by that.


----------



## motilulla (Dec 24, 2022)

Using Excel 2010
Hello,

I am using following formula given by Fluff under this link Count delay of specific number after it finds reset to 0, and repeat counts

I have a few numbers of results in the columns “B” they are in between 1 to 50, In the column “C” there is formula which finds the delay of the number is in the cell “C5” to get the delay of the each numbers I have to change the number and put delay of their corresponding number in the columns “G:M”

Now I require a formula which find delay of each number automatically in the columns “G:M” in the cells G6:M55 as shown in the example below. (Also I want to mention that appearance for the each number max will be 10 times)

Does it is possible to have unique formula which can find the delay of each numbers?

Note: my request if I could be possible, I can use the given formula in the excel 2000 also.

MrExcel Question.xlsxABCDEFGHIJKLMN1234Few ResultDelayUniqueUnique1 Time2 Time3 Time4 Time5 Time6 Time7 Time5Few Result1NumCountDelayDelayDelayDelayDelayDelayDelay629 13161458732 2175849 322553945 43481181042 5098117 6539174528211215 733745051336 83148 921535 1031612 1111734 1221821 132199 1402033 152211161602250 1702310 1852413 1902520 2022623 2122726 2212835 2322924 242303 2503110 2623230 2703333 2803439 292351143043640 3113715 3223835 3343923 3414030 3534130 362428 3724345 381446 3914540 4044631 4114744 421487 4324933 4425029 4525149 461527 471534 482544 4955540 5025640 5718 5812 5918 6043 616 6211 6320 6424 656 668 6749 6848 6937 706 7150 724 7318 7448 7538 7626 7721 7822 7949 802 8130 8247 833 8441 8544 8649 8710 8832 8943 9033 919 9213 931589437 9536 9646 9718 986 9918 Sheet1Cell FormulasRangeFormulaG6G6=MATCH($E6,$B$6:$B$99,0)F6:F55F6=COUNTIF($B$6:$B$99,E6)C6:C99C6=IF($B6=C$5,ROW()-LOOKUP(2,1/(($B$5:$B5=C$5)+($B$5:$B5="Few Result")),ROW($B$5:$B5)),"")

Please help

Thank you all.

Regards,
Moti


----------



## motilulla (Dec 26, 2022)

motilulla said:
*I modified the previous formula but which were not resulting correctly with “0”*


Peter_SSs said:


> *I don't know what you mean by that.*


*Peter**, *I apologise it is my mistake; I just forgot to erase this line while posting, from the message all worked perfect!

Good Luck!

Kind Regards,
Moti


----------



## Peter_SSs (Dec 26, 2022)

OK, thanks for clarifying.


----------

