Flatten File: Repeat (LIST) (by # of times)

[Kristen Sorenson]

I am trying to take a forecast and flatten the file to import it back into our ERP software. The files can contain (1000+ items which will vary by plant). I need to take this report and break it down so that it appears in the following format:

My best attempt was using this formula in column D. However, creating the item and repeating that item by the number of months is my biggest issue.

=INDEX(Forecast!$C$3:$N$1554,MATCH(A4:$A$6209&B4:$B$6209,Forecast!$A$3:$A$1554&Forecast!$B$3:$B$1554,0),MATCH(C4:$C$6209,Forecast!$C$2:$N$2,0))

Item Month Forecast Qty (Column D).
123456 Jan 2222
123456 Feb 2798
123456 March 3210
123456 April 2356

Current the information that I receive is displayed:

Item Jan Feb March April May June
123456 2222 2798 3210 2356


The IMPORT tab on my spreadsheet was my best attempt of reformatting the (forecast sheet). I would like help in building a macro that will break down an item that is in the (forecast sheet) by the number of months shown.

Example:

Item Month Forecast Qty
123456 Jan 2222
123456 Feb 2798
123456 March 3210
123456 April 2356
1234561 Jan 345
1234561 Feb 279
1234561 March 324
1234561 April 321

 

[Somendra Misra]

Hi Kristen,

I might not understood your requirement. Correct me if something is wrong.

You the below code to put the data as shown in below pic. (Can't upload the file here because of size limitation as the file crossed 1 Mb (1.33 Mb after full data extraction).

Sub ForecastItem()
 
Dim Sourcesheet As Worksheet
Dim Outputsheet As Worksheet
Dim lastrow As Long
 
Set Sourcesheet = ThisWorkbook.Worksheets("Forecast")
Set Outputsheet = ThisWorkbook.Worksheets("OutputSheet") 'Change the name as per your requirement
 
With Sourcesheet
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
 
i = 2
 
Application.ScreenUpdating = False
 
    For Item = 3 To lastrow
        For Months = 3 To 15
      
            If Sourcesheet.Cells(Item, Months) = "" Then
                GoTo a
            End If
      
        Outputsheet.Cells(i, 1) = Sourcesheet.Cells(Item, 1)
        Outputsheet.Cells(i, 2) = Sourcesheet.Cells(Item, 2)
        Outputsheet.Cells(i, 3) = Sourcesheet.Cells(2, Months)
        Outputsheet.Cells(i, 4) = Sourcesheet.Cells(Item, Months)
        i = i + 1
      
a:
      Next Months
  
Next Item
      
Application.ScreenUpdating = True
 
End Sub

Put the code in VBA Editor and Run it. I had named output sheet as OutputSheet. Change the sheet name to your original name in the code.

Just advise if any issue.


Also note I am moving this thread to VBA forum Thread.

Regards,

 

[Kristen Sorenson]

That was exactly what I wanted!!!! Amazing how fast you were able to do that. Thank you!

My only question is if the duration of the forecast ranges from (6 months, 12 months, or 24 months) - will the code need to be updated?

 

[Somendra Misra]

@Kristen Sorenson

You current data is from Jan - Dec. I did not understand 6 months, 12 months or 24 months.

You mean to say if the data is for Jan - June or Jan to Dec or for 2 years for an item. Is it?

Kindly, explain a bit more.

Regards,

 

[Kristen Sorenson]

Yes, you are correct. This example only had data from jan-dec. However, if I was to change it in the future and the data is for June to December or like you said (2 years) for an item, will the macro still work?

 

[Somendra Misra]

@Kristen Sorenson

Basic below line in the code is jumping from Jan to Dec.
For Months = 3 To 15
So, FOR look of months need to be changed like 3 to 7 for June - Dec with months in column C to Column H.

For 2 years are you going to insert a new row for the same item with the year 2014?

Regards,

 

[Kristen Sorenson]

Yes if it rolls into a new year. It would have to be a unique row. For example, if I forecast an item from September 2013 to June 2014. Each month would have a new row.

 

[Somendra Misra]

Sorry for not making me clear.

What I mean by new row is that will you insert a new row in source sheet. Like for item say 12345

Item Year Jan Feb Mar Apr May June Jul Sep Oct Nov Dec
12345 2013 5 6 7 8 9 10
12345 2014 11 12 13 14 15

Like this. OR like keeping the row same and extending the column from JAN to DEC and agian FROM JAN to DEC?

I am talking about source sheet.
Regards,