Array formula to expand complex list of numbers with same prefix

[Fractalis]

I request help to get a very complex output, so it seems a very complex array formula could be needed.


My inputs are 2 numbers that share a common prefix. The first number is the common prefix and the second has a few numbers more
and I need to get a list expanded up to the second number. This list should contain all possibilities sharing the same prefix.


I dont know if this only could be got using VBA macro or an array formula could do it.


Example:

If in [B]A1[/B] = [B]27[/B] and [B]B1[/B] = [B][COLOR=#ff0000]2773[/COLOR][/B] the output list from C1 should be like Output1:
If in [B]A1[/B] = [B]345[/B] and [B]B1[/B] = [COLOR=#ff0000][B]345920[/B][/COLOR] the output list from C1 should be like Output2

[B]Output 1[/B]      [B]Output 2[/B]
270            3450
271            3451
272            3452
273            3453
274            3454
275            3455
276            3456
2770           3457
2771           3458
2772           34590
[COLOR=#ff0000][B]2773[/B][/COLOR]           34591
2774           [COLOR=#ff0000][B]345920[/B][/COLOR]
2775           345921
2776           345922
2777           345923
2778           345924
2779           345925
278            345926
279            345927
               345928
               345929
               34593
               34594
               34595
               34596
               34597
               34598
               34599

For the firts case I made a very primitive array formula that expands partially.


This is my formula attemp:

C1={SMALL(0+($A$1&(ROW($1:$10)-1)),ROW(A1))}

I hope make sense.


Thanks for any help.

 

[Tetra201]

Could you please clarify why 277 is missing in Output 1, and 3459 and 34592 are missing in Output 2?

 

[Fractalis]

Could you please clarify why 277 is missing in Output 1, and 3459 and 34592 are missing in Output 2?

Hi,

277 is not missing, 277 is expanded since the target value belongs to prefix 277, this is 2773. All possible numbers that have as prefix 277 are 277X, where X could be 0,1,...9. Then if I need to expand 277, in output should go 2770, 2771, ...2779

In the same way, the target number in output 2 is 345920 that belongs to prefix 34592 and 34592 belongs to prefix 3459. Then expanding 34592 this time requires 2 level expansion. First expand all possible values for 3459, this is 3459X where X = 0,1,2...9. Then since the target number is 345920 this means it belongs to 34592. The 34592 should be expanded to all possible values, this is 34592X, where X = 0,1..9. This last level of expansion matches the target value.

In summary, the level of expansions could be N, depending the second number given.

I hope make sense.

 

[Tetra201]

OK, let me rephrase:

Why doesn't Output 1 look like this?

270
271
272
273
274
275
276
[COLOR=#ff0000]277[/COLOR]
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
278
279

 

[Fractalis]

OK, let me rephrase:

Why doesn't Output 1 look like this?

270
271
272
273
274
275
276
[COLOR=#ff0000]277[/COLOR]
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
278
279

Is not needed to show 277 since 277 itself represents all possibilities. Show 277 and then 2770 would be a repetition. When a prefix should be expanded, all possible values that begin with the prefix (0...9) should be shown but not the root (in this case 277 is the root)

 

[Fractalis]

Some idea?

 

[Tetra201]

Here is a VBA code that will output a list of "all possibilities sharing the same prefix" starting from cell C1. The numbers in the list, however, will be sorted "smallest to largest." The code has very minimal checks.

Sub Test()
    Dim Inp1 As Range, Inp2 As Range, Outp As Range
    Dim Len1 As Long, Len2 As Long
    Dim i As Long, j As Long, l As Long
    Dim Temp As String
    Set Inp1 = Range("[COLOR=#ff0000]A1[/COLOR]"): Len1 = Len(Inp1)
    Set Inp2 = Range("[COLOR=#ff0000]B1[/COLOR]"): Len2 = Len(Inp2)
    Set Outp = Range("[COLOR=#ff0000]C1[/COLOR]")
    If Left(Inp2, Len1) <> CStr(Inp1) Then Exit Sub
    If Inp2 = Inp1 Then Outp = Inp1: Exit Sub
    j = 0
    For l = Len1 To Len2 - 1
        For i = 0 To 9
            Temp = Left(Inp2, l) & i
            If Temp <> Left(Inp2, l + 1) Or l + 1 = Len2 Then Outp.Offset(j) = Temp: j = j + 1
        Next i
    Next l
End Sub

 

[Fractalis]

Hi Tetra201,

Thanks a lot. I made some tests and it seems to print the correct output. I still would be looking for the way to get the order required, but the main and more difficult part is to generate the list and your solution it does.

One question:
How can be showed the target value in the output in the way below? I was tryng to add a text to Temp variable but my change added the same string to each value in output and I'd like the target value be visible in output like this:

270 
271 
272 
273 
274 
275 
276 
2770
2771
2772
[COLOR=#ff0000]2773 -> Target[/COLOR]
2774
2775
2776
2777
2778
2779
278 
279

 

[Tetra201]

Here is an updated code. It outputs the list in the required order and marks the target value:

Sub Test1()
    Dim Inp1 As Range, Inp2 As Range, Outp As Range
    Dim Len1 As Long, Len2 As Long
    Dim i As Long, j As Long, l As Long
    Dim Temp As String, Hlpr As String
    Set Inp1 = Range("A1"): Len1 = Len(Inp1)
    Set Inp2 = Range("B1"): Len2 = Len(Inp2)
    Set Outp = Range("C1")
    Application.ScreenUpdating = False
    If Left(Inp2, Len1) <> CStr(Inp1) Then Exit Sub
    If Inp2 = Inp1 Then Outp = Inp1: Exit Sub
    j = 0
    For l = Len1 To Len2 - 1
        For i = 0 To 9
            Temp = Left(Inp2, l) & i
            If Temp <> Left(Inp2, l + 1) Or l + 1 = Len2 Then
                Outp.Offset(j) = Temp & WorksheetFunction.Rept("0", Len2 - l - 1)
                Outp.Offset(j, 1) = l + 1
                j = j + 1
            End If
        Next i
    Next l
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Outp, xlSortOnValues, xlAscending, , xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("C1:D" & j)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
    Temp = "$C$1:$C" & j
    Hlpr = "$D$1:$D" & j
    Range(Temp) = Evaluate("IF(" & Temp & "="""","""",--LEFT(" & Temp & "," & Hlpr & "))")
    Range(Hlpr) = Evaluate("IF(" & Temp & "=" & Inp2 & ",""-> Target"","""")")
    Application.ScreenUpdating = True
End Sub

 

[Fractalis]

Tetra201, Thanks so much. It works just brilliant. I´ll try to understand the logic of your solution.

Very appreaciated:wink: