Convert Series into Range.

[ayazgreat]

Dear Krishnakumar

I require your help here again regarding to mentioned below link

Convert different series into its Start and End Range - Page 3 - MrExcel Message Board

Thanks in Advance

[Admin]

Hi Ayaz,

Welcome to ExcelFox !!

try

Sub kTest()
    
    Dim ka, k(), i As Long, n As Long, c As Long
    
    ka = Range("a1").CurrentRegion.Resize(, 2)
    
    ReDim k(1 To UBound(ka, 1), 1 To 3)
    
    For i = 2 To UBound(ka, 1)
        If i = 2 Then
            n = n + 1: c = c + 1
            k(n, 1) = ka(i, 1)
            k(n, 2) = ka(i, 1)
            k(n, 3) = c
        Else
            If ka(i, 2) - ka(i - 1, 2) = 1 Then
                c = c + 1
                k(n, 2) = ka(i, 1)
                k(n, 3) = c
            Else
                n = n + 1: c = 1
                k(n, 1) = ka(i, 1)
                k(n, 2) = ka(i, 1)
                k(n, 3) = c
            End If
        End If
    Next
    If n Then
        Range("d1:f1") = Array("Start", "End", "Qty Count")
        Range("d2").Resize(n, 3) = k
    End If
    
End Sub

[ayazgreat]

Thank you very much for your reply, I have just tried your code and found an issue regarding to a single number if a series has only one number with MB number then code does not convert it into range.

And one thqing more is that if you please once again see my mentioned link result it is from col d to col j in last updated post.

[Admin]

Hi

Upload a sample workbook with actual layout.

[ayazgreat]

I am not able attach Excel file here from mobile but I am attaching a pic view if you could see

[Admin]

Hi

Try

Sub kTest()
    
    Dim ka, k(), i As Long, n As Long, c As Long
    
    ka = Range("a1").CurrentRegion.Resize(, 2)
    
    ReDim k(1 To UBound(ka, 1), 1 To 7)
    
    For i = 2 To UBound(ka, 1)
        If i = 2 Then
            n = n + 1: c = c + 1
            k(n, 1) = ka(i, 1): k(n, 2) = ka(i, 1): k(n, 3) = c
            k(n, 5) = ka(i, 2): k(n, 6) = ka(i, 2): k(n, 7) = c
        Else
            If ka(i, 2) - ka(i - 1, 2) = 1 Then
                c = c + 1: k(n, 2) = ka(i, 1): k(n, 3) = c
                k(n, 6) = ka(i, 2): k(n, 7) = c
            Else
                n = n + 1: c = 1
                k(n, 1) = ka(i, 1): k(n, 2) = ka(i, 1): k(n, 3) = c
                k(n, 5) = ka(i, 2): k(n, 6) = ka(i, 2): k(n, 7) = c
            End If
        End If
    Next
    If n Then
        Range("d1:j1") = Array("Start", "End", "Qty Count", "", "MCP Start", "MCP End", "MCP Count")
        Range("d2").Resize(n, 7) = k
    End If
    
End Sub

[ayazgreat]

Kris there is a problem with a single number in seruies if you see in attached pic there is single 30735609 with MB number 9002 , after running. Code the Qty or count result is 3 , however it is should be 1

[Admin]

Hi

I got the same results what you shown in the picture. So what's no working for you ?

1.JPG

[ayazgreat]

Kris I mean to say if I say there are different numbers in series with their MB number in col b if there is a single number in series like 307392589 then result should be 307392589 to 307392589 equal 1 in Qty or count col but result in your code is equal to 3 not 1

[Admin]

Hi

I'm not getting you. Can you upload a workbook with expected results ?