Need Summary of N.X and count of N.X no Continuation

K

Kishan

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

Hi,

Here is bit complicated task my sample data are in C11:Y30. And the summary range AA11:AW29

Data are divided in set of 2 in 2 I want to get Summary of N.X and count of N.X no Continuation

First of all as you can see in the column from range V11 to down end row there is no “N.X” so last column of data will be “U” (Note: every time new data will be filled N.X will continue to next columns.) so far last data column will be considered column “U”

Alter finding the last data column now how to calculate to make a summary….

For example first data with set of 2 Range C11:U12,

In this case C11&C12 both have N.X fill N.X in the AA11=N.X

D11&D12 have Number 1 only in one Cell Fill 1 in The AB11=1

E11&E12 both have N.X fill N.X in the AC11=N.X

F11&F12 both have N.X fill N.X in the AD11=N.X

Now if you see G11&G12 Have Numbers and H11&H12 also Have Number so this will be count 2 and result 2 will be filled in the cell AE11

And so on……

In the brief if there are cells with up/down N.X & N.X will be filled N.X in the summary

And if there is Number in the any Cells with up/down will be keep counting and count will be placed in the next cell of summary range AA11:AW29 for each of the set

Another example to make it clearer range data C23:U24…

C23&C24 Number+D23&D24 Number So result in summary AA23 = 2

E23&E24 Both With N.X So result in summary AB23 = N.X

F23&F24 Both With N.X So result in summary AC23 = N.X

From G23:S24 all these have numbers up or down so count will be 13 result in summary AD23 = 13 then AE23 = N.X and finally AF = 1

Please do advice any formula if not VBA will be good too.

Here below is example Sheet Data Range C11:Y30..Summary Range AA11:AW29

NX.xls
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW
1
2
3
4
5
6
7
8
9
10Serial NumbersEM12345678910111213141516171819202122231234567891011121314151617181920212223
111N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.X1N.XN.X2N.XN.XN.X1N.XN.XN.XN.X1N.X1N.XN.X
122N.XN.XN.XN.X12N.XN.XN.XN.XN.XN.XN.XN.X1N.X1N.XN.X1234
13
141N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.X1N.XN.X3N.XN.X1N.X1N.XN.X3N.X1
156N.XN.XN.XN.X123N.XN.XN.XN.X1N.XN.X123N.X12345
16
172N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.X1N.XN.X3N.X2N.X6N.X1
188N.XN.XN.XN.XN.X12N.X1N.XN.X123456N.X12345
19
202N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X12342N.XN.XN.XN.X2N.XN.X3N.X1N.X
211212N.X12N.XN.XN.XN.X12N.XN.X123N.X1N.X1234
22
235N.XN.XN.XN.X1N.X1N.X1N.XN.XN.X1234N.XN.XN.X12342N.XN.X13N.X1
241312N.XN.X12N.X12345678910N.X12345
25
2661N.XN.X12N.X1N.XN.XN.X123N.X1N.XN.XN.XN.X12342N.X14N.X1
27712N.XN.X12N.X12345678910N.X12345
28
2981N.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.XN.XN.XN.X1234562N.XN.X1N.XN.X1N.X3N.X1N.XN.XN.X2
3010N.X1N.XN.X1N.XN.XN.XN.X123N.X1N.XN.XN.X123456
31
32
33
34
35
Hoja1


Thank you in advance

Regards,
Kishan
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Kishan said:
VBA will be good too
Click to expand...
With VBA:

VBA Code: 
Sub Summary_NX()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, n As Long
  
  a = Range("C11", Range("U" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To 23)
  
  For i = 1 To UBound(a, 1) Step 3
    k = 0
    n = 0
    For j = 1 To UBound(a, 2)
      If a(i, j) = "N.X" And a(i + 1, j) = "N.X" Then
        If n > 0 Then k = k + 1
        k = k + 1
        b(i, k) = "N.X"
        n = 0
      Else
        n = n + 1
        b(i, k + 1) = n
      End If
    Next
  Next
  Range("AA11").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

:cool:
 
Upvote 1
DanteAmor said:
With VBA:

VBA Code: 
Sub Summary_NX()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, n As Long
 
  a = Range("C11", Range("U" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To 23)
 
  For i = 1 To UBound(a, 1) Step 3
    k = 0
    n = 0
    For j = 1 To UBound(a, 2)
      If a(i, j) = "N.X" And a(i + 1, j) = "N.X" Then
        If n > 0 Then k = k + 1
        k = k + 1
        b(i, k) = "N.X"
        n = 0
      Else
        n = n + 1
        b(i, k + 1) = n
      End If
    Next
  Next
  Range("AA11").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

:cool:
Click to expand...
Wow! DanteAmor, 2nd day consecutive you made me happy giving an excellent solution for such a difficult query hats off to you.

a = Range("C11", Range("U" & Rows.Count).End(3)).Value

in the given example current data are find in the columns “C:U” if data raise to next further columns I need to change range in the code “U” to V, X or Y…how can it be set automatically if data raise goes up to for example column “X”?

Just an idea can we add in the row 9 formula which can find if there is N.X in the columns “C:Y” otherwise live it blank…does it is possible to find last column in range C9:Y23 before the empty cell Z9?.... Example sheet attached….

NX.xls
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW
1
2
3
4
5
6
7
8
9Find Last ColumnEMN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.X1234567891011121314151617181920212223
10Serial NumbersEM1234567891011121314151617181920212223EM1234567891011121314151617181920212223
111N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.X1N.XN.X2N.XN.XN.X1N.XN.XN.XN.X1N.X1N.XN.X
122N.XN.XN.XN.X12N.XN.XN.XN.XN.XN.XN.XN.X1N.X1N.XN.X1234
13
141N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.X1N.XN.X3N.XN.X1N.X1N.XN.X3N.X1
156N.XN.XN.XN.X123N.XN.XN.XN.X1N.XN.X123N.X12345
16
172N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X1234N.X1N.XN.X3N.X2N.X6N.X1
188N.XN.XN.XN.XN.X12N.X1N.XN.X123456N.X12345
19
202N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X12342N.X2N.XN.XN.XN.X2N.XN.X3N.X1N.X
211212N.X12N.XN.XN.XN.X12N.XN.X123N.X1N.X1234
22
235N.XN.XN.XN.X1N.X1N.X1N.XN.XN.X1234N.XN.XN.X12342N.XN.X13N.X1
241312N.XN.X12N.X12345678910N.X12345
25
2661N.XN.X12N.X1N.XN.XN.X123N.X1N.XN.XN.XN.X12342N.X14N.X1
27712N.XN.X12N.X12345678910N.X12345
28
2981N.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.XN.XN.XN.X1234562N.XN.X1N.XN.X1N.X3N.X1N.XN.XN.X2
3010N.X1N.XN.X1N.XN.XN.XN.X123N.X1N.XN.XN.X123456
31
32
33
34
35
Hoja1


Have a nice day, I wish you a good luck.

Kind Regards,
Kishan :)
 
Upvote 0
Kishan said:
ust an idea can we add in the row 9 formula which can find if there is N.X
Click to expand...


Considering row 9 starting at column "C" look for the last column with "N.X".
Then the result will be 6 columns to the right of the last "N.X".

Try this:
VBA Code: 
Sub Summary_NX()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, n As Long, lr As Long, lc As Long
  
  j = 3
  Do While Cells(9, j) = "N.X"
    j = j + 1
  Loop
  
  lc = j - 1
  lr = Range("C" & Rows.Count).End(3).Row
  a = Range("C11", Cells(lr, lc)).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  
  For i = 1 To UBound(a, 1) Step 3
    k = 0
    n = 0
    For j = 1 To UBound(a, 2)
      If a(i, j) = "N.X" And a(i + 1, j) = "N.X" Then
        If n > 0 Then k = k + 1
        k = k + 1
        b(i, k) = "N.X"
        n = 0
      Else
        n = n + 1
        b(i, k + 1) = n
      End If
    Next
  Next
  Cells(11, lc + 6).Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub


Have a nice day ;)
 
Upvote 1
Solution
DanteAmor said:
Considering row 9 starting at column "C" look for the last column with "N.X".
Then the result will be 6 columns to the right of the last "N.X".

Try this:
VBA Code: 
Sub Summary_NX()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, n As Long, lr As Long, lc As Long
 
  j = 3
  Do While Cells(9, j) = "N.X"
    j = j + 1
  Loop
 
  lc = j - 1
  lr = Range("C" & Rows.Count).End(3).Row
  a = Range("C11", Cells(lr, lc)).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
 
  For i = 1 To UBound(a, 1) Step 3
    k = 0
    n = 0
    For j = 1 To UBound(a, 2)
      If a(i, j) = "N.X" And a(i + 1, j) = "N.X" Then
        If n > 0 Then k = k + 1
        k = k + 1
        b(i, k) = "N.X"
        n = 0
      Else
        n = n + 1
        b(i, k + 1) = n
      End If
    Next
  Next
  Cells(11, lc + 6).Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub


Have a nice day ;)
Click to expand...
Thank you so much master, DanteAmor. For your help I am glad to use your VBA which you have made very flexible to use it. Really I like it very much it is an excellent solution. 🙌

Have a good day, Good Luck to you!

Kind Regards,
Kishan :)
 
Upvote 0
Kishan said:
Thank you so much master, DanteAmor. For your help I am glad to use your VBA which you have made very flexible to use it. Really I like it very much it is an excellent solution. 🙌

Have a good day, Good Luck to you!

Kind Regards,
Kishan :)
Click to expand...
Hello @DanteAmor, hope you are find well, I want to ask you 1 time more favour please could you modify the macro which is in the #post4 working perfect with 2 sets, but now I need for 6 sets here below is example attached with expected result in columns AA:AW

For example where N.X is 6 times continuous in the column will be N.X and when it is less than 6 times or there is any number that will be keep counted those columns till next 6 time finds N.X

DanteAmor, I am sorry to disturb you again & again I need it desperately. I tried a lot to modify but cannot get success.

Kishan Index.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAV
1
2
3
4
5
6
7
8
9Find Last ColumnEMN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.X12345678910111213141516171819202122
10Serial NumbersEM1234567891011121314151617181920212223EM12345678910111213141516171819202122
111N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X12342N.X4N.X9N.X1
122N.XN.XN.XN.X12N.XN.XN.XN.XN.XN.XN.XN.X1N.X1N.XN.X1234
133N.XN.XN.XN.X123N.XN.XN.XN.X1N.XN.X123N.X11234
144N.XN.XN.XN.XN.X12N.X1N.XN.X123456N.X11234
155N.XN.XN.XN.X1N.X1N.X1N.XN.XN.X1234N.XN.XN.X1234
1661N.XN.X12N.X1N.XN.XN.X123N.X1N.XN.XN.XN.X1234
17
181N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X12342N.X16
192N.XN.XN.XN.X12N.XN.XN.XN.XN.XN.XN.XN.X1N.X1N.XN.X1234
203N.XN.XN.XN.X123N.XN.XN.XN.X1N.XN.X123N.X11234
214N.XN.XN.XN.XN.X12N.X1N.XN.X123456N.X11234
225N.XN.XN.XN.X1N.X1N.X1N.XN.XN.X1234N.XN.XN.X1234
2371N.XN.X1N.XN.XN.X1N.XN.X12N.XN.X1N.XN.X1N.X1234
24
251N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X12341N.XN.X6N.X8
262N.XN.XN.XN.X12N.XN.XN.XN.XN.XN.XN.XN.X1N.X1N.XN.X1234
273N.XN.XN.XN.X123N.XN.XN.XN.X1N.XN.X123N.X11234
284N.XN.XN.XN.XN.X12N.X1N.XN.X123456N.X11234
295N.XN.XN.XN.X1N.X1N.X1N.XN.XN.X1234N.XN.XN.X1234
Hoja5-1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A24:U24Cell Value="N.X"textNO
A11:U23,A25:U30Cell Value="N.X"textNO


Regards,
Kishan
 
Upvote 0
Kishan said:
I need for 6 sets
Click to expand...
All sets are 6 rows?
Because, the third data set you put only 5 rows, but I guess it must be 6.

Then try the following macro:

VBA Code: 
Sub Summary_NX()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, m As Long, n As Long, lr As Long, lc As Long
 
  j = 3
  Do While Cells(9, j) = "N.X"
    j = j + 1
  Loop
 
  lc = j - 1
  lr = Range("C" & Rows.Count).End(3).Row
  a = Range("C11", Cells(lr, lc)).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
 
  For i = 1 To UBound(a, 1) Step 7
    k = 0
    n = 0
    For j = 1 To UBound(a, 2)
      If a(i, j) = "N.X" And a(i + 1, j) = "N.X" And a(i + 2, j) = "N.X" And _
         a(i + 3, j) = "N.X" And a(i + 4, j) = "N.X" And a(i + 5, j) = "N.X" Then
        If n > 0 Then k = k + 1
        k = k + 1
        b(i, k) = "N.X"
        n = 0
      Else
        n = n + 1
        b(i, k + 1) = n
      End If
    Next
  Next
  Cells(11, lc + 6).Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

🫡
 
Upvote 1
DanteAmor said:
All sets are 6 rows?
Because, the third data set you put only 5 rows, but I guess it must be 6.

Then try the following macro:

VBA Code: 
Sub Summary_NX()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, m As Long, n As Long, lr As Long, lc As Long
 
  j = 3
  Do While Cells(9, j) = "N.X"
    j = j + 1
  Loop
 
  lc = j - 1
  lr = Range("C" & Rows.Count).End(3).Row
  a = Range("C11", Cells(lr, lc)).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
 
  For i = 1 To UBound(a, 1) Step 7
    k = 0
    n = 0
    For j = 1 To UBound(a, 2)
      If a(i, j) = "N.X" And a(i + 1, j) = "N.X" And a(i + 2, j) = "N.X" And _
         a(i + 3, j) = "N.X" And a(i + 4, j) = "N.X" And a(i + 5, j) = "N.X" Then
        If n > 0 Then k = k + 1
        k = k + 1
        b(i, k) = "N.X"
        n = 0
      Else
        n = n + 1
        b(i, k + 1) = n
      End If
    Next
  Next
  Cells(11, lc + 6).Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

🫡
Click to expand...
Fantastic! DanteAmor, 🤗 true it was my mistake correct result must be 2-N.X-N.X-6-N.X-8 and your macro give me perfect outcome! ✍️

I am very happy with your work and appreciate your time you spent to solve my problem and I am very sorry for troubling you again and again. 🙏

I wish you best of luck and cheers to your bright future. 🥂

Kind Regards,
Kishan :) 🫡
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Count N.X till find in any of 2 rows
Replies
5
Views
292
Peter_SSs
Peter_SSs
K
  • Question Question
Need a code modification as per current requirement.
Replies
2
Views
356
Kishan
K
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
  • Solved
Count continuous “X’s” and covert the numbers into “N.X”
2
Replies
13
Views
773
Peter_SSs
Peter_SSs
K
  • Solved
Extract number only from “mix-up text & numbers data row”
Replies
7
Views
869
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,844
Messages
6,181,294
Members
453,030
Latest member
PG626

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