counting 7th data after zero

jheiz · Jun 11, 2021

hello good day, i was working on monitoring the 7th day duty of guards, i don't know what formula to be used.

i have a thousands of data to check it one by one. i just want to have a formula that marks or emphasized the 7th day data after zero. thank you.

attached is a sample.

DanteAmor · Jun 11, 2021

Hi jheiz and welcome to MrExcel.

It is not a formula that I like, but maybe someone else can provide us with a more compact formula.

Dante Amor.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

2

Emp

Name

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

7th Day

3

8

0

8

12

0

8

4

12

8

0

12

8

0

8

5

12

8

12

8

0

8

7

6

0

8

0

12

8

0

7

12

0

8

12

0

12

8

12

8

12

0

12

0

8

9

12

0

12

8

12

Hoja2

Cell Formulas
Range	Formula
R3:R9	R3	=IF(COUNTIF(C3:I3,">0")=7,I$2,IF(COUNTIF(D3:J3,">0")=7,J$2,IF(COUNTIF(E3:K3,">0")=7,K$2, IF(COUNTIF(F3:L3,">0")=7,L$2,IF(COUNTIF(G3:M3,">0")=7,M$2,IF(COUNTIF(H3:N3,">0")=7,N$2, IF(COUNTIF(I3:O3,">0")=7,O$2,IF(COUNTIF(J3:P3,">0")=7,P$2,IF(COUNTIF(K3:Q3,">0")=7,K$2,"")))))))))

jheiz · Jun 12, 2021

thank you sir. ill try this formula., i just wonder if there are 2, 7th day duty per row.

jheiz · Jun 12, 2021

hello sir dante thanks for your help, i just use the formula that you provide. and it is working on a single output. but on the 2nd day it is not applicable.

Toadstool · Jun 12, 2021

@DanteAmor , yes, I also struggled with this which initially seemed simple. I did see a typo where the last cell in your formula is K$2 but should be Q$2.

@jheiz I took a slightly different format and added to Dante's work. I use a start date in C2 and then formatting to show the month in C1 and just the day of the month in C2 to Q2. The result in R3 to R16 is the date but just formatted as dd-mmm

You can only have two occurrences if the first is on the 7th or 8th so I've added column S where I do the check for a second set of non-zero values (again, building on Dante's work).

P.S. If you can use XL2BB or even just paste your data as text and not an image then we won't need to retype the data.

JHeiz3.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

June

2

Emp

Name

1

7th Day

2nd 7th

3

8

0

8

12

0

8

4

12

8

0

12

8

0

8

5

12

8

12

8

0

8

7-Jun

6

0

8

0

12

8

0

7

12

0

8

12

8

9-Jun

8

12

8

0

8

12

0

8

10-Jun

9

12

0

12

8

14-Jun

10

12

0

8

7-Jun

11

12

0

8

7-Jun

12

0

12

8

8-Jun

15-Jun

13

12

0

8

7-Jun

14

12

1

8

7-Jun

14-Jun

15

0

12

8

8-Jun

15-Jun

16

8

2

12

0

8

15-Jun

Sheet1

Cell Formulas
Range	Formula
C1	C1	=C2
D2:Q2	D2	=C2+1
R3:R16	R3	=IF(COUNTIF(C3:I3,">0")=7,I$2,IF(COUNTIF(D3:J3,">0")=7,J$2,IF(COUNTIF(E3:K3,">0")=7,K$2, IF(COUNTIF(F3:L3,">0")=7,L$2,IF(COUNTIF(G3:M3,">0")=7,M$2,IF(COUNTIF(H3:N3,">0")=7,N$2, IF(COUNTIF(I3:O3,">0")=7,O$2,IF(COUNTIF(J3:P3,">0")=7,P$2,IF(COUNTIF(K3:Q3,">0")=7,Q$2,"")))))))))
S3:S16	S3	=IF(R3=$I$2,IF(COUNTIF(J3:P3,">0")=7,P$2,""),IF(R3=$J$2,IF(COUNTIF(K3:Q3,">0")=7,Q$2,""),""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I3:Q18	Expression	=OR($R3=I$2,$S3=I$2)	text	NO

jheiz · Jun 13, 2021

@Toadstool thanks for your help. sorry for the data that i upload. im just new to this site so i am not familiar with XL2BB. I'll try to use the formula that you provided. i really helps me a lot if it works properly since i am dealing with thousands of data.

DanteAmor · Jun 13, 2021

Try the following macros.
If you want them in a column:

Dante Amor

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

2

Emp

Name

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

7th Day

3

8

0

8

12

0

8

4

12

8

7, 14

5

12

8

12

8

0

8

7

6

0

8

0

12

8

0

7

12

0

8

12

0

12

8

12

8

12

0

12

0

8

9

12

0

12

8

12

10

12

0

12

8

7, 15

11

12

0

8

12

0

12

8

15

Hoja3

VBA Code:

Sub Conting7()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, n As Long
  
  Range("R3:R" & Rows.Count).ClearContents
  a = Range("C2", Range("Q" & Rows.Count).End(3)).Value
  ReDim b(2 To UBound(a), 1 To 1)
  For i = 2 To UBound(a, 1)
    n = 0
    For j = 1 To UBound(a, 2)
      If a(i, j) <> 0 Then
        n = n + 1
        If n = 7 Then
          n = 0
          If b(i, 1) = "" Then b(i, 1) = a(1, j) Else b(i, 1) = b(i, 1) & ", " & a(1, j)
        End If
      Else
        n = 0
      End If
    Next j
  Next i
  
  Range("R3").Resize(UBound(a) - 1).Value = b
End Sub

____
If you want them in two columns:

varios 13jun2021.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

2

Emp

Name

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

7th Day

3

8

0

8

12

0

8

4

12

8

7

14

5

12

8

12

8

0

8

7

6

0

8

0

12

8

0

7

12

0

8

12

0

12

8

12

8

12

0

12

0

8

9

12

0

12

8

12

10

12

0

12

8

7

15

11

12

0

8

12

0

12

8

15

Hoja3

VBA Code:

Sub Conting7_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, n As Long
  
  Range("R3:S" & Rows.Count).ClearContents
  a = Range("C2", Range("Q" & Rows.Count).End(3)).Value
  ReDim b(2 To UBound(a), 1 To 2)
  For i = 2 To UBound(a, 1)
    n = 0
    For j = 1 To UBound(a, 2)
      If a(i, j) <> 0 Then
        n = n + 1
        If n = 7 Then
          n = 0
          If b(i, 1) = "" Then b(i, 1) = a(1, j) Else b(i, 2) = a(1, j)
        End If
      Else
        n = 0
      End If
    Next j
  Next i
  
  Range("R3").Resize(UBound(a) - 1, 2).Value = b
End Sub

___
HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Conting7 or Conting7_v2) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

jheiz · Jun 13, 2021

Thank you sir @Toadstool and @DanteAmor, it really helps. I learn a lot of things on both of you.

DanteAmor · Jun 14, 2021

Glad we could help & thanks for the feedback

counting 7th data after zero

jheiz

New Member

Excel Facts

DanteAmor

Well-known Member

jheiz

New Member

jheiz

New Member

Toadstool

Well-known Member

jheiz

New Member

DanteAmor

Well-known Member

jheiz

New Member

DanteAmor

Well-known Member

Similar threads

Forum statistics

Share this page

counting 7th data after zero

New Member

Excel Facts

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New 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