# change from sequence to range

#### shili12

##### Member
i have them in sequential order,
how do i change to range as below ?? formula and power query solutions too are fine.

 Name from to Jawwab Insurance Agencies B13451227 B13455432 B13455439 B13455464 PANTHERA INSURANCE AGENCY B13271712 B13329527 B13329529 B13340636 B13340640 B13368804 B13368808 B13371368 B13371371 B13371376 B13371388

#### Attachments

• summarycertificates.xlsb
156.9 KB · Views: 4
From your pivot or directly from 'summary' worksheet without the need to create a pivot ?​

ignore pivot, from summary sheet !!!

Your expected result is wrong but I catch the idea …​

According to your attachment a Windows VBA demonstration to paste only to the 'results' worksheet module :​
Code:
``````Sub DemoW1()
Dim oRE(2) As Object, F&, L&, R&, P%, T%
With Sheet11.[A1].CurrentRegion.Rows
If .Count < 3 Then Beep: Exit Sub
Set oRE(2) = CreateObject("VBScript.RegExp")
UsedRange.Offset(1).Clear
Application.ScreenUpdating = False
.Sort .Cells(6), 1, .Cells(2), , 1, , , 1
oRE(2).Pattern = "(\D*)(\d*)"
F = 2
L = 1
For R = F To .Count
P = R And 1
Set oRE(P) = oRE(2).Execute(.Cells(R, 2))
T = -(.Cells(R, 6) <> .Cells(R, 6)(0))
If T = 0 Then T = oRE(P)(0).SubMatches(0) <> oRE(1 - P)(0).SubMatches(0) Or _
Val(oRE(P)(0).SubMatches(1)) - Val(oRE(1 - P)(0).SubMatches(1)) > 1
If T Then
If R > F Then Cells(L, 3) = .Cells(R, 2)(0)
F = R + 1
L = L + 1
If T = 1 Then
Cells(L, 1).Borders(8).Weight = 2
Rows(L).Columns("A:B") = Array(.Cells(R, 6), .Cells(R, 2))
Else
Cells(L, 2) = .Cells(R, 2)
End If
End If
Next
If R - 1 > F Then Cells(L, 3) = .Cells(R, 2)(0)
End With
Erase oRE
For P = 7 To 9:  UsedRange.Columns(1).Borders(P).Weight = 2:  Next
UsedRange.Columns("B:C").Borders.Weight = 2
UsedRange.NumberFormat = "_0@ "
UsedRange.VerticalAlignment = xlCenter
UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
End Sub``````

The attached contains a Power Query solution rather than VBA. See table at cell E1 of ther results sheet. This just needs refreshing like a pivot table if the source data should change.
I've added a pivot table at cell I1 based on the new table to give you the same style of output that you asked for, although I think you can do more with the raw table.

#### Attachments

• Chandoo56931summarycertificates.xlsb
155.8 KB · Views: 2
For a better word for it. I could not have written urgent as this data is going in papers by Thursday with use of your formula. 17000 lines had to be summarized. If I said urgent, it would be a violation. Thanks alot for yr assistance. You've saved me hours of toil.

Hakuna matatizo !​

Hakuna matatizo !that means no sweat in Swahili. oddly enough am an Indian residing in swahili land​

Like 'va tutto bene', no problem, you're welcome.​
Next time you may indicate you have « some short delay, if possible etc … »​
and according to your small attachment give more details like about the rows size​
as if I knew there are 17 000 I certainly wrote a different code - is my previous demonstration slow ? -​
like also columns criterion (2 & 6) and if column 2 always starts with B or at least with a single letter,​
again it should be a different code, easier and working on every platform and not only under Windows.​

perfect, it worked perfectly without a glitch or was not even slow !! i did exactly as you instructed, i cannot know how to express my gratitude. If only you could get compensated by likes we give you !! i spent over 3 hours or so on gemini, chatgpt, and copilot testing all scenarios and rephrasing myself but to no avail.
 Xxxx Insurance Agency B13222463 B13222495 B13224297 B13224330 B13282077 B13282080 B13283294 B13283314 B13288056 B13288068 B13293644 B13293650 B13293652 B13293658 B13293673 B13307871 B13307905 B13309988 B13310017 B13314467 B13314503 B13328121 B13328155 B13329650 B13329695 B13337434 B13337465 B13340564 B13340596 B13349613 B13349647 B13354589 B13354594 B13357263 B13357272 B13365803 B13365807 B13368184 B13368213 B13371297 B13371304 B13374504 B13374537 B13383252 B13383296 B13399951 B13400008 B13406225 B13406227 B13406320 B13406339 B13407422 B13407457 B13412796 B13412828 B13419329 B13419379 B13420840 B13420892 B13425816 B13425877 B13434099 B13434103 B13434137 B13443601 B13443638 B13443643 B13443645 B13448404 B13448456 B13451230 B13451235 B13451239 B13451255 B13451259 B13451263 B13463281 B13463282 B13464451 B13464480 B13465587 B13465602 B13465607 B13465609 B13465613 B13465629 B13476115 B13476135 B13476142 B13484393 B13484417 B13484953 B13484956 B13487776 B13487795 B13492790 B13492832 B13495844 B13495876 B13495882 B13495917 B13497277 B13497332 B13497800 B13497849 B13502735 B13502749 B13502751 B13502780 B13502783 B13502789 B13513698 B13513747 B13519468 B13519498 B13519501 B13519535 B13519537 B13519539 B13520726 B13520746 B13527653 B13527684 B13527687 B13527701 B13528279 B13528285 B13530486 B13530510 B13530514 B13530530 B13531040 B13531044 B13531047 B13531049 B13531053 B13531061 B13531577 B13531628 B13532215 B13532252 B13532255 B13532257 B13532260 B13532267 B13535019 B13535050 B13541045 B13541079 B13541082 B13541097 B13543955 B13543967 B13543971 B13544004 B13544596 B13544627 B13554063 B13554095

If column 2 data starts always with 'B' or at least with only a single letter then my VBA procedure​
can be simplified without any regular expression (so working whatever on Mac like under Windows)​
and of course optimized for 17 000 rows, easier code and could be slightly faster …​
gemini, chatgpt, and copilot
I only saw working ways from those AI with people who already well know VBA so they do not need any AI !​
I'm helping someone on a project started with Copilot : ugly code, often not working and​
when it may work many useless things so not such efficient. I had to restart from blank …​

i spent over 3 hours or so on gemini, chatgpt, and copilot testing all scenarios and rephrasing myself but to no avail.
The easy beginner way : filling a helper column as a process criteria with a beginner level worksheet formula​
like the kid Excel beginner solved this (I sent him as a logic training exercice).​
Could be less difficult when using an AI …​
According to the 17K data rows, under a VBA procedure filling a helper column can be avoided in your case​
but using the same kid logic with the worksheet formula directly evaluated in a variable,​
this faster Excel basics VBA demonstration assumes column 2 contains only a single letter before digits,​
to paste only to the 'results' worksheet module :​
Code:
``````Sub Demo1()
Const E = "IF({1},(LEFT(B2:B#,1)&MID(B2:B#,2,LEN(B2:B#))-1<>B1:B§)+(F2:F#<>F1:F§))"
Dim V, W, S\$(), F&, R&, L&
UsedRange.Offset(1).Clear
With Sheet11.[A1].CurrentRegion.Rows
'       .Sort .Cells(6), 1, .Cells(2), , 1, , , 1
V = .Range("B2:F" & .Count)
W = .Parent.Evaluate(Replace(Replace(E, "#", .Count), "§", .Count - 1))
End With
ReDim S(1 To UBound(W), 2)
F = 1
For R = F To UBound(W)
If W(R, 1) Then
If R > F Then S(L, 2) = V(R - 1, 1)
F = R + 1
L = L + 1
If W(R, 1) = 2 Then S(L, 0) = V(R, 5)
S(L, 1) = V(R, 1)
End If
Next
If L Then
If R - 1 > F Then S(L, 2) = V(R - 1, 1)
With [A2].Resize(L, 3)
'        .Borders.Weight = 2
'        .NumberFormat = "_0@ "
.Value = S
.Columns.AutoFit
End With
End If
End Sub``````