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 (http://www.mrexcel.com/forum/showthread.php?t=298752&page=3&highlight=AYAZGREAT)

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 ?

211

[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 ?

[ayazgreat]

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

[ayazgreat]

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

[Admin]

Hi,

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

[ayazgreat]

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