Need a code modification as per current requirement.

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 the code I am using which gives me following results in range AC11:AW23 as shown in the example below.

NX.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW
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.XN.XN.XN.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.X1234N.XN.XN.XN.X1N.XN.XN.XN.X3N.XN.XN.X1N.XN.XN.X
122N.XN.XN.XN.X1N.XN.XN.XN.X123N.XN.XN.X1N.XN.XN.X1234
13
1411N.XN.X1N.XN.X1N.X12N.XN.X1N.X1N.XN.XN.XN.X12341N.XN.X1N.XN.X4N.XN.X1N.X2N.XN.XN.X
1561N.XN.X1N.XN.XN.X1N.X1N.XN.XN.XN.XN.X1N.XN.XN.X2345
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
2021N.XN.X12N.X1N.XN.XN.X123N.X1N.XN.XN.XN.X12342N.X14N.X1
211212N.XN.X12N.X12345678910N.X11234
22
2351N.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.XN.XN.XN.X1212342N.XN.X1N.XN.X1N.X3N.X1N.XN.XN.X2
2413N.X1N.XN.X1N.XN.XN.XN.X123N.X1N.XN.XN.X122345
Sheet1


VBA Code: 
'https://www.mrexcel.com/board/threads/need-summary-of-n-x-and-count-of-n-x-no-continuation.1236827/#post-6059282
'Need Summary of N.X and count of N.X no Continuation

Sub Summary_NX_2Sets()
  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

I need VBA modification to get required result as shown below in range AC11:AW23.

NX.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAY
9Find Last ColumnEMN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.X1234567891011121314151617181920212223
10Serial NumbersEM1234567891011121314151617181920212223EMSerial NumbersMax1234567891011121314151617181920212223
111N.XN.XN.XN.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.X12341 To 1 & 2 To 23N.XN.XN.XN.X1N.XN.XN.XN.X123N.XN.XN.X1N.XN.XN.X
122N.XN.XN.XN.X1N.XN.XN.XN.X123N.XN.XN.X1N.XN.XN.X1234
13
1411N.XN.X1N.XN.X1N.X12N.XN.X1N.X1N.XN.XN.XN.X12341 To 1 & 3 To 341N.XN.X1N.XN.X1234N.XN.X1N.X12N.XN.XN.X
1561N.XN.X1N.XN.XN.X1N.X1N.XN.XN.XN.XN.X1N.XN.XN.X2345
16
172N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X12341 To 1 & 4 To 46N.X1N.XN.X123N.X12N.X123456N.X1
188N.XN.XN.XN.XN.X12N.X1N.XN.X123456N.X12345
19
2021N.XN.X12N.X1N.XN.XN.X123N.X1N.XN.XN.XN.X12341 To 1 & 5 To 51412N.X1234567891011121314N.X1
211212N.XN.X12N.X12345678910N.X11234
22
2351N.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.XN.XN.XN.X1212341 To 1 & 6 To 6312N.XN.X1N.XN.X1N.X123N.X1N.XN.XN.X12
Sheet2
Cell Formulas
RangeFormula
AB11,AB23,AB20,AB17,AB14AB11=MAX(AC11:AY11)


Thank you in advance

Regards,
Kishan
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Hi,

Sorry, I noticed here is missing basic information for the modification required…

My Data are in the 2 & 2 rows…1st in cells C11:Y12… The Results of these 2 rows in AA11:AW11 for example… if both rows have filled in the same column with N.X then result =N.X…if not count progressively 1, 2, 3, 4…and so on.

Please help. Thank you in advance.

Regards,
Kishan
 
Upvote 0
Kishan said:
Using Excel 2000

Hi,

I need VBA modification to get required result as shown below in range AC11:AW23.

NX.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAY
9Find Last ColumnEMN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.X1234567891011121314151617181920212223
10Serial NumbersEM1234567891011121314151617181920212223EMSerial NumbersMax1234567891011121314151617181920212223
111N.XN.XN.XN.XN.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.X12341 To 1 & 2 To 23N.XN.XN.XN.X1N.XN.XN.XN.X123N.XN.XN.X1N.XN.XN.X
122N.XN.XN.XN.X1N.XN.XN.XN.X123N.XN.XN.X1N.XN.XN.X1234
13
1411N.XN.X1N.XN.X1N.X12N.XN.X1N.X1N.XN.XN.XN.X12341 To 1 & 3 To 341N.XN.X1N.XN.X1234N.XN.X1N.X12N.XN.XN.X
1561N.XN.X1N.XN.XN.X1N.X1N.XN.XN.XN.XN.X1N.XN.XN.X2345
16
172N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X12341 To 1 & 4 To 46N.X1N.XN.X123N.X12N.X123456N.X1
188N.XN.XN.XN.XN.X12N.X1N.XN.X123456N.X12345
19
2021N.XN.X12N.X1N.XN.XN.X123N.X1N.XN.XN.XN.X12341 To 1 & 5 To 51412N.X1234567891011121314N.X1
211212N.XN.X12N.X12345678910N.X11234
22
2351N.XN.XN.XN.XN.XN.X1N.X1N.X1N.XN.XN.XN.XN.X1212341 To 1 & 6 To 6312N.XN.X1N.XN.X1N.X123N.X1N.XN.XN.X12
Sheet2
Cell Formulas
RangeFormula
AB11,AB23,AB20,AB17,AB14AB11=MAX(AC11:AY11)


Thank you in advance

Regards,
Kishan
Click to expand...

Hi, after hundreds of combinations I could modify following code to work with following 2nd scenarios as I needed.

Thank you all for looking my request if any ony need here is the code.

VBA Code: 
'https://www.mrexcel.com/board/threads/need-summary-of-n-x-and-count-of-n-x-no-continuation.1236827/#post-6059282
'Need Summary of N.X and count of N.X no Continuation

Sub Combin_Peso_NX_SetOf2()
 
 
  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
 
Application.ScreenUpdating = False
 
  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                                      'This Is For 2 Sets 1 TO 2
    
       'If n > 0 Then k = k + 1 'SUM > 0
        If n > 0 Then k = k + n  'No sum > 0
      
        k = k + 1
        b(i, k) = "N.X"
        n = 0
      Else
        n = n + 1
        'b(i, k + 1) = n 'SUM > 0
        b(i, k + n) = n  'No sum > 0
      End If
    Next
  Next
  Range("AA11").Resize(UBound(b, 1), UBound(b, 2)).Value = b
 
  Application.ScreenUpdating = True

End Sub

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
290
Peter_SSs
Peter_SSs
A
  • Solved
doesn't show data in listbox on form without any error in this code
Replies
6
Views
119
Abdo
A
Y
  • Solved
Help with VBA modification
2
Replies
10
Views
640
Burrgogi
Burrgogi
rjplante
  • Solved
sort numbers with letters
2 3
Replies
24
Views
1K
Akuini
Akuini
A
  • Solved
adapting code to merge amount for duplicates item on usrform
Replies
4
Views
301
abdo meghari
A

Forum statistics

Threads
1,223,912
Messages
6,175,341
Members
452,638
Latest member
Oluwabukunmi

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