Moving range of values

[rlobera]

Hi everyone!

I'm trying to figure out how to perform this but haven't been able to do it, so I'm turning to you since I believe you'd be able to help me with this issue.

In sheet 1 I have estimated active customers for a period of time, where columns are months and rows are departments/states:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]Month 1[/TD]
[TD="align: center"]Month 2[/TD]
[TD="align: center"]Month 3[/TD]
[TD="align: center"]Month 4[/TD]
[TD="align: center"]Month 5[/TD]
[/TR]
[TR]
[TD="align: center"]Department A[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]Department B[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]Department C[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]12[/TD]
[/TR]
</tbody>[/TABLE]

Then, in sheet 2, I'm forecasting sales and need excel to return values from sheet 1 depending on which month we are kicking-off for each department:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]Kick-off[/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: center"]Apr[/TD]
[TD="align: center"]May[/TD]
[TD="align: center"]Jun[/TD]
[TD="align: center"]Jul[/TD]
[/TR]
[TR]
[TD="align: center"]Department A[/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Department B[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]Department C[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]12[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Of course, the kick-off month may vary for each department and values from sheet 1 should accommodate according to the selected month on column B in sheet 2. What formula should I use within C2:I4???

Thank you so much for your help!

Best,
R.

 

[mumps]

Try this macro:

Sub CopyRanges()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet2").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lColumn As Long
    lColumn = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
    Dim dept As Range
    Dim rng As Range
    Dim foundRng As Range
    For Each rng In Sheets("Sheet2").Range("B2:B" & LastRow)
        Set dept = Sheets("Sheet1").Range("A:A").Find(rng.Offset(0, -1), LookIn:=xlValues, lookat:=xlWhole)
        Set foundRng = Sheets("Sheet2").Rows(1).Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        Sheets("Sheet1").Activate
        Sheets("Sheet1").Range(Cells(dept.Row, 2), Cells(dept.Row, lColumn)).Copy Sheets("Sheet2").Cells(rng.Row, foundRng.Column)
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[lrobbo314]

In your second table, sheet 2, you have the months spelled out, e.g. "Jan". Change those to dates, like the first of the month for each month, 1/1/2017, 2/1/2017, etc, then format the cells with a custom format of "mmm" so they read Jan, Feb, etc.
If you do that, then in C2 you can enter the formula below and copy it over and down to get the results you are looking for.

=IFERROR(IF(C$1>=$B2,INDEX(Sheet1!$B$2:$F$4,MATCH($A2,Sheet1!$A$2:$A$4,0),MONTH(C$1)-MONTH($B2)+1),""),"")

 

[lrobbo314]

Just playing around with another VBA option. Of course, you can include row and column variables like in mumps solution to account for more rows and columns.

Sub arraySolution()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim Data()
Dim MR()
Dim MC As Range
Dim Start As Integer
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Data = ws1.Range("B2:F4").Value
MR = ws2.Range("B2:B4").Value
Set MC = ws2.Range("C1:I1")
For i = 1 To UBound(MR)
    Start = Application.WorksheetFunction.Match(MR(i, 1), MC, 0)
    For j = 1 To UBound(Data, 2)
        ws2.Cells(i + 1, (Start + 1) + j).Value = Data(i, j)
    Next j
Next i
End Sub

Also, you can change the last line in mumps for loop to

Sheets("Sheet2").Cells(rng.Row, foundRng.Column).Resize(1, 5).Value = Sheets("Sheet1").Range(Sheets("Sheet1").Cells(dept.Row, 2), Sheets("Sheet1").Cells(dept.Row, lColumn)).Value

If you do that you can get rid of the 'Sheets("Sheet1").Activate' line of code to avoid switching between sheets.

 

[Rick Rothstein]

Assuming the department names are in the same order on both sheets, then here is another macro for you to consider...

[table="width: 500"]
[tr]
	[td]Sub Forecasts()
  Dim X As Long, Mnth As Long
  Application.ScreenUpdating = False
  With Sheets("Sheet1").UsedRange
    .Offset(1, 1).Resize(.Rows.Count, .Columns.Count).Copy Sheets("Sheet2").Range("C2")
  End With
  With Sheets("Sheet2")
    For X = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
      Mnth = Month(CDate(.Cells(X, "B").Value & " 1"))
      If Mnth > 1 Then .Cells(X, "C").Resize(, Mnth - 1).Insert xlShiftToRight
    Next
  End With
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[rlobera]

Hey guys, you're awesome! I'll try everything you just teach me and then I'll let you know. Thank you for your kind help!

If possible, I would really prefer to use a series of formulas an avoid VBA.

 

[rlobera]

Quick question... What if I have a department (or departments) repeated multiple times in sheet 1, and in sheet 2 I have to sum those values to show the result? Would that be possible?

Btw, the formula from lrobbo314 works flawlessly!

 

[rlobera]

Your solution works great, but I have a more challenging question, and want to know if it's possible to solve it with a formula (I'm trying to avoid VBA).

So, what if I have a department (or departments) repeated multiple times in sheet 1, and in sheet 2 I have to sum those values to show the result? I tried to figure it out but was not able to do it.