Check delay with past occurance.

motilulla · Oct 28, 2017

Hello,

I require to check delay 3 of each character (1 with 1, X with X & 2 with 2) with the past occurrence of the same character.

For example column C, start-checking delay of "X" is in cell C7 steps back in cell C6 result is "X" (so far delay of "X" in C7 = 1 Print delay result in F7 = 1)

Check delay of "X" is in cell C8 steps back in cell C7 result is "X" (so far delay of "X" in C8 = 1 Print delay result in F8 = 1)

Check delay of "2" is in cell C9 steps back in cells "2" not found (so far delay of "2" in C9 = 0 Print delay result in F9 = 0)

Check delay of "X" is in cell C11 steps back in cells "X" found 3 steps back (so far delay of "X" in C11 = 3 Print delay result in F11 = 3)

Resume check each character with past occurrence finds in the column back rows

Sheet1 example....

Book1

A

B

C

D

E

F

G

H

1

2

3

4

N

Year

n1

n2

EM1

Delayn1

Delayn2

5

N

Year

n1

n2

EM1

Delayn1

Delayn2

6

1

2001

X

1

7

2

2001

X

1

0

8

3

2001

X

1

2

9

4

2001

2

1

0

1

10

5

2001

1

X

0

3

11

6

2001

X

2

3

0

12

7

2001

X

1

3

13

8

2001

1

X

3

14

9

2001

1

2

15

10

2001

1

X

1

2

16

11

2001

X

1

4

2

17

12

2001

1

2

1

18

13

2001

1

19

14

2001

X

1

20

15

2001

1

21

16

2001

1

22

17

2001

2

1

23

18

2001

1

24

19

2001

X

1

25

20

2001

1

26

21

2001

X

1

27

22

2001

1

2

28

23

2001

1

29

24

2001

1

30

25

2001

1

31

26

2001

1

32

27

2001

1

X

33

28

2001

2

34

29

2001

1

X

35

30

2001

2

1

36

31

2001

1

X

37

32

2001

X

1

38

33

2001

X

2

39

34

2001

X

1

40

35

2001

1

41

36

2001

X

42

37

2001

2

X

43

38

2001

X

44

39

2001

X

45

40

2001

1

46

41

2001

1

47

42

2001

2

X

48

43

2001

1

2

49

44

2001

X

1

50

45

2001

X

1

51

46

2001

X

1

52

47

2001

X

1

53

48

2001

X

1

54

49

2001

2

1

55

50

2001

X

56

57

Sheet1

Thank you all
Excel 2000
Regards,
Moti

wideboydixon · Oct 28, 2017

This?

Book1

A

B

C

D

E

F

G

4

N

Year

n1

n2

EM1

Delayn1

Delayn2

5

N

Year

n1

n2

EM1

Delayn1

Delayn2

6

1

2001

X

1

7

2

2001

X

1

0

8

3

2001

X

1

2

9

4

2001

2

1

0

1

10

5

2001

1

X

0

3

11

6

2001

X

2

3

0

12

7

2001

X

1

3

13

8

2001

1

X

3

14

9

2001

1

2

15

10

2001

1

X

1

2

16

11

2001

X

1

4

2

17

12

2001

1

2

1

18

13

2001

1

19

14

2001

X

1

3

1

20

15

2001

1

2

1

21

16

2001

1

22

17

2001

2

1

13

1

23

18

2001

1

2

1

24

19

2001

X

1

5

1

25

20

2001

1

2

1

26

21

2001

X

1

2

1

27

22

2001

1

2

16

28

23

2001

1

2

29

24

2001

1

30

25

2001

1

31

26

2001

1

32

27

2001

1

X

1

17

33

28

2001

2

11

6

34

29

2001

1

X

2

35

30

2001

2

1

2

4

36

31

2001

1

X

2

37

32

2001

X

1

11

2

38

33

2001

X

2

1

5

39

34

2001

X

1

2

40

35

2001

1

4

1

41

36

2001

X

2

5

42

37

2001

2

X

7

1

43

38

2001

X

2

1

44

39

2001

X

1

45

40

2001

1

5

46

41

2001

1

47

42

2001

2

X

5

3

48

43

2001

1

2

10

49

44

2001

X

1

5

3

50

45

2001

X

1

51

46

2001

X

1

52

47

2001

X

1

53

48

2001

X

1

54

49

2001

2

1

7

1

55

50

2001

X

2

8

Sheet1

Cell Formulas
Range	Formula
F7	{=MOD(ROW()-MAX(IF(C$6:C6=C7,ROW(C$6:C6))),ROW())}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy formula across and down.

WBD

motilulla · Oct 28, 2017

wideboydixon said:
This?

Array Formulas

Cell
Formula

F7
{=MOD(ROW()-MAX(IF(C$6:C6=C7,ROW(C$6:C6))),ROW())}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Copy formula across and down.

WBD

Thank you WBD, formula is working correct. Please could you tell me how can I run it through VBA so it can leave the values only

Regards,
Moti

motilulla · Oct 29, 2017

Hello, I tried my best to creating a small code and run the formula via macro but the macro below wipeout the borders. Please can anybody will help with creating a VBA solution

Code:

 Sub Fill_ArrayFormula()
    Dim lngLastRow As Long
    lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row
    Range("F7").FormulaArray = "=MOD(ROW()-MAX(IF(C$6:C6=C7,ROW(C$6:C6))),ROW())"
    Range("F7:G7").FillRight
    Range("F7:G" & lngLastRow).FillDown
    Range("F7:G" & lngLastRow) = Range("F7:G" & lngLastRow).Value
End Sub

Regards,
Moti

motilulla · Oct 29, 2017

Bump
Require VBA solution if possible?

Regards,
Moti

wideboydixon · Oct 30, 2017

Try this instead:

Code:

Sub Fill_ArrayFormula()
    Dim lngLastRow As Long
    lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row
    Range("F7").FormulaArray = "=MOD(ROW()-MAX(IF(C$6:C6=C7,ROW(C$6:C6))),ROW())"
    Range("F7").Copy
    Range("G7").PasteSpecial xlPasteFormulas
    Range("F7:G7").Copy
    Range("F8:G" & lngLastRow).PasteSpecial xlPasteFormulas
    Range("F7:G" & lngLastRow).Value = Range("F7:G" & lngLastRow).Value
    Application.CutCopyMode = False
    Range("A4").Select
End Sub

WBD

motilulla · Oct 30, 2017

wideboydixon said:

Try this instead:

Code:

Sub Fill_ArrayFormula()
    Dim lngLastRow As Long
    lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row
    Range("F7").FormulaArray = "=MOD(ROW()-MAX(IF(C$6:C6=C7,ROW(C$6:C6))),ROW())"
    Range("F7").Copy
    Range("G7").PasteSpecial xlPasteFormulas
    Range("F7:G7").Copy
    Range("F8:G" & lngLastRow).PasteSpecial xlPasteFormulas
    Range("F7:G" & lngLastRow).Value = Range("F7:G" & lngLastRow).Value
    Application.CutCopyMode = False
    Range("A4").Select
End Sub

WBD

Thank you WBD, yes it is solved the borders issue. I changed the range as per my original data 25 columns across and 7000+ rows down it take a long time to get populate the results. Also I tried to fraction the sub in 5 steps but can't solve it.

Please is there any other way?

Regards,
Moti

wideboydixon · Oct 30, 2017

OK. Re-written entirely in VBA without array formula. I ran it across 25 columns with 7000 rows in each column and it completed in less than 5 seconds:

Code:

Public Sub CheckDelay()

Const FIRST_NUM_COLUMN = 3 ' First column of numbers - in our case "C"
Const NUM_COLUMNS = 25 ' Number of columns we're calculating for
Const FIRST_ROW = 6 ' The first row of data (delay will be left blank on this row)
Const BLANK_COLUMNS = 1 ' Number of blank columns between the numbers and the delays

Dim last1 As Long
Dim last2 As Long
Dim lastX As Long
Dim thisRow As Long
Dim thisCol As Long
Dim lastRow As Long
Dim delay As Long

' No screen updating please
Application.ScreenUpdating = False

' Work across all columns
For thisCol = 0 To NUM_COLUMNS - 1
    ' Find the last row
    lastRow = Cells(Rows.Count, FIRST_NUM_COLUMN + thisCol).End(xlUp).Row
    
    ' Reset the row trackers
    last1 = 0
    last2 = 0
    lastX = 0
    
    ' Process all rows
    For thisRow = FIRST_ROW To lastRow
        ' Determine what's in the cell and remember the last row we found the value
        Select Case Cells(thisRow, FIRST_NUM_COLUMN + thisCol).Value
            Case 1
                delay = IIf(last1 = 0, 0, thisRow - last1)
                last1 = thisRow
            Case 2
                delay = IIf(last2 = 0, 0, thisRow - last2)
                last2 = thisRow
            Case "X"
                delay = IIf(lastX = 0, 0, thisRow - lastX)
                lastX = thisRow
        End Select
        
        ' For cells after the first row, record the delay
        If thisRow > FIRST_ROW Then Cells(thisRow, FIRST_NUM_COLUMN + NUM_COLUMNS + BLANK_COLUMNS + thisCol).Value = delay
    Next thisRow
Next thisCol

' Turn on screen updating
Application.ScreenUpdating = True

End Sub

You can set up the number of columns etc. at the top of the function.

WBD

motilulla · Oct 30, 2017

wideboydixon said:

OK. Re-written entirely in VBA without array formula. I ran it across 25 columns with 7000 rows in each column and it completed in less than 5 seconds:

Code:

Public Sub CheckDelay()

Const FIRST_NUM_COLUMN = 3 ' First column of numbers - in our case "C"
Const NUM_COLUMNS = 25 ' Number of columns we're calculating for
Const FIRST_ROW = 6 ' The first row of data (delay will be left blank on this row)
Const BLANK_COLUMNS = 1 ' Number of blank columns between the numbers and the delays

Dim last1 As Long
Dim last2 As Long
Dim lastX As Long
Dim thisRow As Long
Dim thisCol As Long
Dim lastRow As Long
Dim delay As Long

' No screen updating please
Application.ScreenUpdating = False

' Work across all columns
For thisCol = 0 To NUM_COLUMNS - 1
    ' Find the last row
    lastRow = Cells(Rows.Count, FIRST_NUM_COLUMN + thisCol).End(xlUp).Row
    
    ' Reset the row trackers
    last1 = 0
    last2 = 0
    lastX = 0
    
    ' Process all rows
    For thisRow = FIRST_ROW To lastRow
        ' Determine what's in the cell and remember the last row we found the value
        Select Case Cells(thisRow, FIRST_NUM_COLUMN + thisCol).Value
            Case 1
                delay = IIf(last1 = 0, 0, thisRow - last1)
                last1 = thisRow
            Case 2
                delay = IIf(last2 = 0, 0, thisRow - last2)
                last2 = thisRow
            Case "X"
                delay = IIf(lastX = 0, 0, thisRow - lastX)
                lastX = thisRow
        End Select
        
        ' For cells after the first row, record the delay
        If thisRow > FIRST_ROW Then Cells(thisRow, FIRST_NUM_COLUMN + NUM_COLUMNS + BLANK_COLUMNS + thisCol).Value = delay
    Next thisRow
Next thisCol

' Turn on screen updating
Application.ScreenUpdating = True

End Sub

You can set up the number of columns etc. at the top of the function.

WBD

Amazing! WBD, eye-blinking solution what a big difference using formula and this VBA it is just fantastic!

Thank you so much for your kind help

Have a good day

Regards,
Moti

Check delay with past occurance.

motilulla

Well-known Member

Excel Facts

wideboydixon

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

wideboydixon

Well-known Member

motilulla

Well-known Member

wideboydixon

Well-known Member

motilulla

Well-known Member

Similar threads

Forum statistics

Share this page

Check delay with past occurance.

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock