Convert Series into Range.

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

Hi Ayaz,

Welcome to ExcelFox !!

try

Code:

---

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

---

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.

Hi

Upload a sample workbook with actual layout.

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

Hi

Try

Code:

---

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

---

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

Hi

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

Attachment 211

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

Hi

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

Sorry Kris still unable to upload workbook but tomorrow I will do the same
I am just attaching another sample pic for you kindly see red highlighted last cells I hope you will get what I am I trying to say
In col f and col j the range count should be 1 instead of 3 but code gives me result 3 however it is one

Dear Kris

I am attaching here a sample file, kindly see red highlighted last cells I hope you will get what I am I trying to say
In last cell of col f and col j the range count should be 1 instead of 3 but code gives me result 3 however it is one

Hi,

You haven't pasted the code correctly. Copy the correct code from my post # 6 and try. It should work.

Kris you are right, it works fine now, thank you very much for your kind support