Code needed to shift data a given number columns to the right

richanor · Mar 18, 2020

Hi all

I am trying to work out what the impact of the Covid-19 virus will have on our work schedules and wondered if somebody could help me with something:

I have a worksheet (called Chart), and in cell B1 I have the number of weeks expected delay (currently set to 3). In cells B3:BB3 I have the week numbers 1-52, and 52+. Under each week number (B4:BB4) I have the number of events expected to be completed in that week.

What I would like to do, is on row 5 (B5:BB5) to shift every number to the right a number of columns, according to the value given in call B1. So for example, if I currently have 6 projects due to finish in week 11 - I will have the number 6 in cell L4. On row 5, I would want this to be shifted along three rows to the right and so the number 6 would appear in cell O5 (indicating that they are now expected to be completed in week 14).

Here is the difficult bit: when it comes to the end of the range (BB3 - which represents 52+, or more than a year) I don't want the values just to be pushed along, I want them to be added up and just appear in cell BB5 as a total.

I have no idea if this is well enough explained to get a response, or even possible - but if anyone can help I'd really appreciate it.

Thanks

Rich

Peter_SSs · Mar 18, 2020

Welcome to the MrExcel board!

Do you really need code?
Would formulas like these do? They could be added by code if necessary?
Is this what you mean?
Formula in B5 copied across to BA5 and BB5 is a separate formula

20 03 18.xlsm

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

1

3

2

3

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

52+

4

3

6

3

6

4

3

9

4

9

6

9

7

2

7

6

5

6

4

3

2

6

8

2

9

5

9

3

7

6

9

7

1

9

1

4

9

4

7

3

4

5

6

2

25

5

Move values

Cell Formulas
Range	Formula
B5:BA5	B5	=IF(B3>$B1,INDEX($B4:$BB4,COLUMNS($B5:B5)-$B1),"")
BB5	BB5	=SUM(B4:BB4)-SUM(B5:BA5)

richanor · Mar 18, 2020

Absolute genius Peter - formulas are even better, it's just that my peanut sized brain couldn't work out how to possible achieve this with formulas (mainly the supping up bit at the end!

Could I ask one further favour. Now that I have put it in, I can see that I want it to appear sligtly differently:

as all the data is shifted along by the number of columns in B1, thecolumns to the left of the first data point all read '0', whereas I actually want these all to appear as the same as the first data point. So for example in your sheet above, cells B5:D5 are empty, but it would be more useful to me if they could appear the same as the first datapoint (E3)?

If not, don't worry - I thank you son much for your help

Rich

Peter_SSs · Mar 18, 2020

richanor said:
whereas I actually want these all to appear as the same as the first data point. So for example in your sheet above, cells B5:D5 are empty, but it would be more useful to me if they could appear the same as the first datapoint (E3)

Like this?

20 03 18.xlsm

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

1

3

2

3

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

52+

4

3

6

3

6

4

3

9

4

9

6

9

7

2

7

6

5

6

4

3

2

6

8

2

9

5

9

3

7

6

9

7

1

9

1

4

9

4

7

3

4

5

6

2

25

5

Move values

Cell Formulas
Range	Formula
B5:BA5	B5	=IF(B3>$B1,INDEX($B4:$BB4,B3-$B1),$B4)
BB5	BB5	=SUM(B4:BB4)-SUM(B5:BA5)+B5*B1

richanor · Mar 19, 2020

Yes - that's exactly what I was after. Thanks so much Peter!

Rich

richanor · Mar 19, 2020

Hi, at the risk of asking for too much - I wonder if anybody could provide some further help with this project? The part above is working perfectly, but I am trying to add something else:

I now have a sheet which is able to work out the knock on / delays that the shutdown will cause. I have copied the results from B5:BB5 to B7:BB7 and so in cells B7:BB7 I have the number of projects outstanding for each week (1-52 and 52+). In cell BC7 I have the capacity available to complete, and I want to be able to deduct the capacity from the outstanding work, starting from week 52+ and working back.

So, if you look at the sheet Peter has posted above, in BB5 you can see I have 38 jobs at 52 weeks +, in BA5 I have 4 jobs, AZ5 I have 3 jobs and so on. I have my capacity in BC5, which lets assume is 53. What I would like to do is to start at BB7 and work backwards reducing the number of jobs until I reach my capacity. So in this example, my capacity is 53; so BB7 would become 0, BA7 would become 0, AZ7 would become 0, AY7 would become 0, and AX7 would become 6 (thus I have deducted 53 - my capacity) from the outstanding jobs, starting at the oldest and working back.

Again, I have no idea if this is achievable, and so no problem if nobody can help.

Cheers

Rich