How to generate list in between the two given nos. in excel

Question

Please help with the trick to generate the series of list with given two nos.

For Example:

Having data like this:

Key      | Month | Year | Location | From | To    
HYE000001| 12    |2013  | 91       | 01   | 52

and want the output in the format given below:

Key      | Month | Year | Location
HYE000001| 12    | 2013 | 91
HYE000002| 12    | 2013 | 91
HYE000003| 12    | 2013 | 91
HYE000004| 12    | 2013 | 91
.
.
.
HYE000051| 12    | 2013 | 91
HYE000052| 12    | 2013 | 91

Please help me with the solution

Thank you.

Shiven

Have you searched on here? there was a similar question recently... — Solar Mike, Jun 19 '18 at 14:51
Hi, welcome to SO. However, this side is not a place to post your problem and expect others to code it. You may find a guideline at https://stackoverflow.com/help/how-to-ask What have you tried already? — Capricorn, Jun 19 '18 at 14:51

score 0 · Answer 1 · answered Jun 19 '18 at 15:56

Here i tried with a macro and worked well for me. Below snippet of code will work for Key value alone and you can modify this as per your requirement.

Place the Key value in A2 cell, From value in B2 cell and To value in C2 cell then try to run the below code.

 Sub KeyGenerator()

    'From range defined in B2 Cell
    Dim From As Integer
    From = Range("B2").Value

    'To range defined in C2 Cell
    Dim Till As Integer
    Till = Range("C2").Value

    'Here A2 cell will have the primary key and modifying the A2 cell value using From value.
    Dim LastKey As String
    Dim Key, Current As String
    Current = Range("A2").Value
    Key = Left(Current, Len(Current) - 1)
    Key = Key & From
    Range("A2").Value = Key

    'Dragging values using To value
    Range("A2").Select
    LastKey = "A" & ((Till - From) + 2)
    Selection.AutoFill Destination:=Range("A2:" & LastKey), Type:=xlFillDefault

    End Sub

How to generate list in between the two given nos. in excel

1 Answers1