Move column value to correct week ending column

horizonflame

Board Regular
Joined
Sep 27, 2018
Messages
184
Office Version
  1. 2013
Hi All

In Column A I have a list of values with a date in Column B. In row 1 I have list of week ending dates (52 rolling dates in total but only a sample shown below.) I need to move the Column A values into into the correct week ending column. Any help appeciated!

Book1
ABCDEFGHIJ
1Ending11/04/202104/04/202128/03/202121/03/202114/03/202107/03/202128/02/2021
21052209/04/2021
3452326/02/2021
4541515/03/2021
51078928/02/2021
61458901/04/2021
7147525/03/2021
81759612/03/2021
9814506/04/2021
101154703/03/2021
11618208/03/2021
Sheet1
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,904
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDEFGHIJ
1Ending11/04/202104/04/202128/03/202121/03/202114/03/202107/03/202128/02/2021
21052209/04/202110522      
3452326/02/2021      4523
4541515/03/2021   5415   
51078928/02/2021     1078910789
61458901/04/2021 14589     
7147525/03/2021  1475    
81759612/03/2021    17596  
9814506/04/20218145      
101154703/03/2021     11547 
11618208/03/2021    6182  
Lists
Cell Formulas
RangeFormula
D2:J11D2=IF(AND($B2>=D$1-7,$B2<=D$1),$A2,"")
 

horizonflame

Board Regular
Joined
Sep 27, 2018
Messages
184
Office Version
  1. 2013
Thank you @Fluff. Would there possibly be a way to turn this into VBA code too please? Many thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,904
Office Version
  1. 365
Platform
  1. Windows
If you want VBA you should say so in the thread.
Try
VBA Code:
Sub horizonflame()
   Dim Ary As Variant
   Dim r As Long, c As Long
   
   Ary = Range("A1").CurrentRegion.Value2
   For r = 2 To UBound(Ary)
      For c = 4 To UBound(Ary, 2)
         If Ary(r, 2) >= Ary(1, c) - 6 And Ary(r, 2) <= Ary(1, c) Then
            Ary(r, c) = Ary(r, 1)
         End If
      Next c
   Next r
   Range("A1").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
End Sub
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,904
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,195
Messages
5,640,791
Members
417,166
Latest member
Funwayo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top