change from sequence to range

[shili12]

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

 

[Marc L]

From your pivot or directly from 'summary' worksheet without the need to create a pivot ?​

​

 

[shili12]

ignore pivot, from summary sheet !!!

 

[Marc L]

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

​

 

[Marc L]

According to your attachment a Windows VBA demonstration to paste only to the 'results' worksheet module :​

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

 

[p45cal]

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.

 

[shili12]

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.

 

[Marc L]

Hakuna matatizo !​

​

 

[shili12]

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

 

[Marc L]

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.​

​

 

[shili12]

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

 

[Marc L]

Thanks for your revert !​

​

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 …​

​

 

[Marc L]

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 :​

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