Count N.X till find in any of 2 rows

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Using Excel 2010
Hello,

I need to count N.X till it finds any of the 2 rows…If there are number in both rows just highlight cell in colour red…if both are blank just replace 0

Please help.

Regards,
Kishan

Count N.X.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
1Condition-1 If Row 1 & 2 Is N.X Count From 1 To…, Example Resulr in C24:L24 1 to 10 Condition-2 If Row 1 & 2 Has Numbers Just RED in the corosponding Result Cell M72,Z24,AA24,AH24,AI24,AJ24Conition-3 If it is nothing then = 0
2Result Start in Row 24
3
4
5123456789101112131415161718192021222324252627282930313233343536373839404142
6Row 1-2N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.XN.X1N.XN.XN.XN.XN.XN.XN.X12N.XN.XN.XN.XN.XN.X123N.X1N.XN.X
7N.XN.X1N.XN.XN.XN.X1N.XN.X1N.XN.XN.XN.XN.X12345N.XN.X141N.X123N.X124N.XN.X1N.X
8
9Row 3-4N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.X
10N.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.X12N.X12345N.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.X12N.X
11
12Row 5-6N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.X
13N.XN.XN.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.XN.XN.XN.X1N.X123N.X
14
15Row 7-8N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.X
16N.XN.XN.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.X1N.X12N.X1N.XN.XN.X1N.X1N.XN.X1N.XN.X1N.X12N.XN.X
17
18Row 9-10N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.X
191N.XN.XN.XN.XN.XN.X1N.XN.X12N.X1N.X12N.XN.XN.X1N.X1N.X123N.XN.XN.XN.XN.X1N.X1N.XN.XN.X
20
21
22
23Result123456789101112131415161718192021222324252627282930313233343536373839404142
24Row 1-21234567891012345678910111212345612340000
25Row 3-4
26Row 5-6
27Row 7-8
28Row 9-10
29Row 11-12
30Row 13-14
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C6:AR20Cell Value="N.X"textYES
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Try:
varios 06abr2024.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
1Condition-1 If Row 1 & 2 Is N.X Count From 1 To…, Example Resulr in C24:L24 1 to 10 Condition-2 If Row 1 & 2 Has Numbers Just RED in the corosponding Result Cell M72,Z24,AA24,AH24,AI24,AJ24Conition-3 If it is nothing then = 0
2Result Start in Row 24
3
4
5123456789101112131415161718192021222324252627282930313233343536373839404142
6Row 1-2N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.XN.X1N.XN.XN.XN.XN.XN.XN.X12N.XN.XN.XN.XN.XN.X123N.X1N.XN.X
7N.XN.X1N.XN.XN.XN.X1N.XN.X1N.XN.XN.XN.XN.X12345N.XN.X141N.X123N.X124N.XN.X1N.X
8
9Row 3-4N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.X
10N.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.X12N.X12345N.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.X12N.X
11
12Row 5-6N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.X
13N.XN.XN.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.XN.XN.XN.X1N.X123N.X
14
15Row 7-8N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.X
16N.XN.XN.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.X1N.X12N.X1N.XN.XN.X1N.X1N.XN.X1N.XN.X1N.X12N.XN.X
17
18Row 9-10N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.X
191N.XN.XN.XN.XN.XN.X1N.XN.X12N.X1N.X12N.XN.XN.X1N.X1N.X123N.XN.XN.XN.XN.X1N.X1N.XN.XN.X
20
21
22
23Result123456789101112131415161718192021222324252627282930313233343536373839404142
24Row 1-212345678910 123456789101112  123456   12340000
25Row 3-4123456789101112131415 12345678910111213141516171819 120000
26Row 5-6123456789 12345678910111213141516171819202122232425 120000
27Row 7-8123456789 12345 12345678910111213141516171819 120000
28Row 9-1012345678910  123 123456789101112131415161718192021220000
Hoja5
Cell Formulas
RangeFormula
C24:AR24C24=IF(COUNTBLANK(C6:C7)=2,0,IF(COUNTIF(C6:C7,"N.X"),SUM(B24,1),""))
C25:AR25C25=IF(COUNTBLANK(C9:C10)=2,0,IF(COUNTIF(C9:C10,"N.X"),SUM(B25,1),""))
C26:AR26C26=IF(COUNTBLANK(C12:C13)=2,0,IF(COUNTIF(C12:C13,"N.X"),SUM(B26,1),""))
C27:AR27C27=IF(COUNTBLANK(C15:C16)=2,0,IF(COUNTIF(C15:C16,"N.X"),SUM(B27,1),""))
C28:AR28C28=IF(COUNTBLANK(C18:C19)=2,0,IF(COUNTIF(C18:C19,"N.X"),SUM(B28,1),""))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C24:AR30Cell Value=""textNO
 
Upvote 1
@DanteAmor, yes your formula solution worked as request. But as I did not mention that I have 924 set of rows in I have shown example with only 5 sets. Does it is possible to get VBA solution. Thank you for your help.

I wish you a good luck.

Kind Regards,
Kishan
 
Upvote 0
So, eliminate the above, formulas and formatting conditions

Adjust the following numbers in the macro according to your information on the sheet.
Rich (BB code): 
  k = 24                  'Change 24 to the row number where the results will begin

  For i = 6 To 18 Step 3  'change 18 to last row of sets

Macro:
VBA Code: 
Sub count_NX()
  Dim i As Long, j As Long, k As Long, m As Long
  Application.ScreenUpdating = False
 
  k = 24                  'Change 24 to the row number where the results will begin
  With Range("C" & k & ":AR" & Rows.Count)
    .ClearContents
    .Interior.Color = xlNone
  End With
 
  For i = 6 To 18 Step 3  'change 18 to last row of sets
    m = 0
    For j = 3 To Columns("AR").Column
      With Cells(k, j)
        If Cells(i, j).Value = "" And Cells(i + 1, j).Value = "" Then
          .Value = 0
        ElseIf Cells(i, j).Value = "N.X" Or Cells(i + 1, j).Value = "N.X" Then
          m = m + 1
          .Value = m
        Else
          .Value = ""
          .Interior.Color = vbRed
          m = 0
        End If
      End With
    Next
    k = k + 1
  Next
 
  Application.ScreenUpdating = True
End Sub

Regards
Dante Amor
😇
 
Upvote 1
Solution
DanteAmor said:
So, eliminate the above, formulas and formatting conditions

Adjust the following numbers in the macro according to your information on the sheet.
Rich (BB code): 
  k = 24                  'Change 24 to the row number where the results will begin

  For i = 6 To 18 Step 3  'change 18 to last row of sets

Macro:
VBA Code: 
Sub count_NX()
  Dim i As Long, j As Long, k As Long, m As Long
  Application.ScreenUpdating = False
 
  k = 24                  'Change 24 to the row number where the results will begin
  With Range("C" & k & ":AR" & Rows.Count)
    .ClearContents
    .Interior.Color = xlNone
  End With
 
  For i = 6 To 18 Step 3  'change 18 to last row of sets
    m = 0
    For j = 3 To Columns("AR").Column
      With Cells(k, j)
        If Cells(i, j).Value = "" And Cells(i + 1, j).Value = "" Then
          .Value = 0
        ElseIf Cells(i, j).Value = "N.X" Or Cells(i + 1, j).Value = "N.X" Then
          m = m + 1
          .Value = m
        Else
          .Value = ""
          .Interior.Color = vbRed
          m = 0
        End If
      End With
    Next
    k = k + 1
  Next
 
  Application.ScreenUpdating = True
End Sub

Regards
Dante Amor
😇
Click to expand...
@DanteAmor, just had opportunity to try with full data 924 sets. Code is just amazing thanks for the instruction how to change the rages accordingly sheet data this help a lot . I do appreciate your kind help. Problem solved.

I wish you a good day and good luck.

Kind Regards,
Kishan :)
 
Upvote 0
.. or you could have used the very similar solution posted some 9 hours earlier here.

In future it would be appreciated if the two of you are going to post the same question in different forums to still report the cross-posts as per #13 of the Forum Rules. It isn't really fair to have somebody spend a considerable amount of time developing a solution when one has already been provided elsewhere.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
If multi column has N.X then N.X otherwise keep counting till next N.X
Replies
3
Views
335
Peter_SSs
Peter_SSs
K
  • Question Question
Need a code modification as per current requirement.
Replies
2
Views
356
Kishan
K
K
  • Solved
Need Summary of N.X and count of N.X no Continuation
Replies
8
Views
609
Kishan
K
K
  • Question Question
Combin copy total 924 sets
Replies
3
Views
437
GlennUK
G
K
  • Solved
Copy rows 2 by 2 at a time.
2
Replies
19
Views
935
bebo021999
B

Forum statistics

Threads
1,224,830
Messages
6,181,225
Members
453,025
Latest member
Hannah_Pham93

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top