Divide a range of values for each unique value

0 votes

I have an Excel data frame with structure similar to the exemple below:

Model Year Year
A 2010 2012
B 2013 2020

I need to split the range of years into a single row for each year. Example below:

Model Year
A 2010
A 2011
A 2012
B 2013
B 2014
B 2015
B 2016
B 2017
B 2018
B 2019
B 2020

I couldn't think of anything to solve

Feb 2 in Others by Kithuzzz
• 35,300 points

1 answer to this question.

0 votes

With the next available row as the starting point, this will loop through each of the Models on SheetB and produce the distinct Model: Year combinations. As a result, if SheetB already has some data, this macro will add values underneath it rather than overwriting it.

Sub mydearmacro()

Dim a_ws As Worksheet: Set a_ws = ThisWorkbook.Sheets("SheetA")
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("SheetB")

Dim a_lr As Long, lr As Long
a_lr = a_ws.Range("A" & a_ws.Rows.Count).End(xlUp).Row

Dim model As String
Dim i As Long, y As Long, x As Long

For i = 2 To a_lr

    model = a_ws.Range("A" & i).Value
    start_year = a_ws.Range("B" & i) - 1
    y = a_ws.Range("C" & i) - a_ws.Range("B" & i) + 1
    lr = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    For x = 1 To y
        ws.Range("A" & lr + x).Value = model
        ws.Range("B" & lr + x).Value = start_year + x
    Next x
Next i

End Sub

enter image description here

answered Feb 2 by narikkadan
• 60,820 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How to apply zoom animation for each element of a list in angular?

Hey @Sid, do check if this link ...READ MORE

answered Jul 30, 2019 in Others by Vardhan
• 13,200 points
0 votes
1 answer
0 votes
1 answer

Excel - Make a graph that shows number of occurrences of each value in a column

There is probably a better way to ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 60,820 points
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,970 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
0 votes
1 answer

How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 60,820 points
0 votes
1 answer

How to increment the Range of a For Each loop - Excel VBA

Your formula seems to sum 1 single ...READ MORE

answered Jan 7 in Others by narikkadan
• 60,820 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP