Need to increment the date value in rows based on the coloumn value

P

pratheesh

New Member
Joined
Dec 16, 2020
Messages
12
Office Version
  1. 365
  2. 2011
  3. 2010
Platform
  1. Windows
Hi,
I wanted to increment the date value based on the column value

My input:
ArrivalNights
01/01/2016​
2​
01/01/2016​
2​
01/01/2016​
6​

Output:

ArrivalNights
1/01/2016​
2​
2/01/2016​
2​
1/01/2016​
6​
2/01/2016​
6​
3/01/2016​
6​
4/01/2016​
6​
5/01/2016​
6​
6/01/2016​
6​


My "Arrival" date values should be increment based on the "Nights" column vales

Please help me with it

thanks in advance :)
 
Sort by date Sort by votes
Joe4 said:
It appears that you stumbled upon a very important lesson here that many people run into.
You don't want to oversimplify your question for the sake of posting it here. Otherwise, you may get a reply that answers your question, but doesn't really solve your problem (because your problem was really more complex than the question you posted!).

In order to help you better, I would like to be able to copy/paste your data to a spreadsheet on my side. However, I cannot do that with the pictures you posted.
But you can post excerpts of your data using this tool described here: XL2BB - Excel Range to BBCode

So if you can post a sampling of your actual data using that tool, I will be better able to assist you.
Click to expand...
Hi Joe,

here is the sample dataset which I'm working on. bb format is as below. Please take a look

the problem scenario in short: I need to duplicate all the row values by multiple of the "Nights" column value and also the "Arrival" column date need to be incremented by the time of values in the "Nights" column. Please refer to the picture of my previous reply to get more understanding of my query.

Thank You Again...


Book1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1NameVIPRoom no.RT.Adlt.Total_PersonArrivalDepartureNightsCurrencyPriceRate codeStat.GroupCompanyTravel AgencySourceSharerAttached ProfileCompanionSource CodeMarket CodeCome ReasonHear Reason
2Voegele Klaus Herr 116D2D2/0/0/0/0201/01/2016xxxx7EUR496GR02COG1comp1TA1S1APROILE1IT CENTRAL LEISURE GroupIT CENTRAL E-mail
3Petersen Thorsten HerrV2-103D2D2/0/0/0/0201/01/2016xxxx2EUR330ARH ImportCOG2comp2TA2S2APROILE2IT CENTRAL LEISURE GroupIT CENTRAL E-mail
4Michelin Stefania Signora 205D2D2/0/0/0/0301/01/2016xxxx5EUR319ARH04COG3comp3TA3S3APROILE3IT CENTRAL LEISURE GroupIT CENTRAL E-mail
5Lumini Roberta Signora 204D2D2/0/0/0/0301/01/2016xxxx6EUR468S23 ImportCOG4comp4TA4S4APROILE4IT CENTRAL LEISURE GroupIT CENTRAL E-mail
6Cagiada Emanuele SignoreV2-215D2D2/0/0/0/0401/01/2016xxxx6EUR179ARB ImportCOG5comp5TA5S5APROILE5IT CENTRAL LEISURE GroupIT CENTRAL E-mail
7Bigarini Paolo .303D2D501/01/2016xxxx4EUR391SI09/0COG6comp6TA6S6APROILE6IT CENTRAL LEISURE GroupIT CENTRAL E-mail
8Vavra Gunda Frau305D2D201/01/2016xxxx3EUR298ARH ImportCOG7comp7TA7S7APROILE7IT CENTRAL LEISURE GroupIT CENTRAL E-mail
9Nussbaumer Gernot Herr 213J2C1/0/0/0/0502/01/2016xxxx2EUR269GR01COG8comp8TA8S8APROILE8IT CENTRAL LEISURE GroupIT CENTRAL E-mail
10Pfuelb Norbert Herr 217D2D2/0/0/0/0402/01/2016xxxx3EUR205S51 ImportCOG9comp9TA9S9APROILE9IT CENTRAL LEISURE GroupIT CENTRAL E-mail
11Lecce Teresa SignoraGold Spirit Club206D2D2/0/1/1/0502/01/2016xxxx4EUR312ARH ImportCOG10comp10TA10S10APROILE10IT CENTRAL LEISURE GroupIT CENTRAL E-mail
12Selden Karl Herr 201J2D1/0/0/0/0302/01/2016xxxx7EUR152ARH ImportCOG11comp11TA11S11APROILE11IT CENTRAL LEISURE GroupIT CENTRAL E-mail
13Bortolamei Mauro Signore 107D2D2/0/0/0/0503/01/2016xxxx6EUR195SALE_HQ ImpCOG12comp12TA12S12APROILE12IT CENTRAL LEISURE GroupIT CENTRAL E-mail
14Kucka Lars Herr301J2C303/01/2016xxxx1EUR328S23 ImportCOG13comp13TA13S13APROILE13IT CENTRAL LEISURE GroupIT CENTRAL E-mail
15Imoscopi Marco Signore 105D2D2/0/2/0/0404/01/2016xxxx5EUR359GR02COG14comp14TA14S14APROILE14IT CENTRAL LEISURE GroupIT CENTRAL E-mail
16Partsch Rudolf Herr 210J2C2/0/0/0/0404/01/2016xxxx7EUR290GR01COG15comp15TA15S15APROILE15IT CENTRAL LEISURE GroupIT CENTRAL E-mail
17Massari Silvia Signora 206D2D2/0/0/0/0304/01/2016xxxx3EUR170BAR_HBCOG16comp16TA16S16APROILE16IT CENTRAL LEISURE GroupIT CENTRAL E-mail
18Fernandez Hector 205D2D1/0/1/0/0304/01/2016xxxx1EUR433GR02COG17comp17TA17S17APROILE17IT CENTRAL LEISURE GroupIT CENTRAL E-mail
19Jenjahn Karin Frau 209J2C2/0/1/1/0504/01/2016xxxx1EUR349ARH04COG18comp18TA18S18APROILE18IT CENTRAL LEISURE GroupIT CENTRAL E-mail
20Pichmoser Claudia Frau 207D2D2/1/1/0/0404/01/2016xxxx5EUR209ARH01COG19comp19TA19S19APROILE19IT CENTRAL LEISURE GroupIT CENTRAL E-mail
21Pixner Alois Herr 208D2D2/0/0/0/0504/01/2016xxxx1EUR500ARH04COG20comp20TA20S20APROILE20IT CENTRAL LEISURE GroupIT CENTRAL E-mail
22Marelli Angelo Signore 204D2D2/0/0/0/0404/01/2016xxxx1EUR438GR02COG21comp21TA21S21APROILE21IT CENTRAL LEISURE GroupIT CENTRAL E-mail
23Gottardi Fabio MrBlue Spirit Club109J2C2/0/0/0/0305/01/2016xxxx2EUR170ARH01COG22comp22TA22S22APROILE22IT CENTRAL LEISURE GroupIT CENTRAL E-mail
24Fuchs Hubert Herr 208D2D1/0/0/0/0505/01/2016xxxx1EUR458BAR_BFBCOG23comp23TA23S23APROILE23IT CENTRAL LEISURE GroupIT CENTRAL E-mail
25Kownacki Dawid 216C1S2/0/0/0/0305/01/2016xxxx4EUR433SI05/0COG24comp24TA24S23APROILE24IT CENTRAL Individual BARIT CENTRAL E-mail
Sheet1
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
OK, I think this should do what you want:
VBA Code: 
Sub MyOutputSheet()

    Dim inSht As Worksheet
    Dim outSht As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim dte As Date
    Dim nt As Long
    Dim n As Long
    Dim nr As Long
    
    Application.ScreenUpdating = False
    
'   Set workheet objects
    Set inSht = Sheets("Sheet1")
    Set outSht = Sheets("Sheet2")
    
'   Copy headers from input sheet to output sheet
    inSht.Range("A1:X1").Copy outSht.Range("A1")
    
'   Find last row with data in input sheet
    lr = inSht.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all records on input sheet, starting with row 2
    For r = 2 To lr
'       Get Arrival Date and Nights from row
        dte = inSht.Cells(r, "G")
        nt = inSht.Cells(r, "I")
'       Loop through all nts
        For n = 1 To nt
'           Find next available for on output sheet
            nr = outSht.Cells(Rows.Count, "A").End(xlUp).Row + 1
'           Copy row from input sheet to output sheet
            inSht.Range(Cells(r, "A"), Cells(r, "X")).Copy outSht.Cells(nr, "A")
'           Populate date
            outSht.Cells(nr, "G") = dte
'           Increment date by 1
            dte = dte + 1
        Next n
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!", vbOKOnly
        
End Sub
 
Upvote 0
Solution
Joe4 said:
OK, I think this should do what you want:
VBA Code: 
Sub MyOutputSheet()

    Dim inSht As Worksheet
    Dim outSht As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim dte As Date
    Dim nt As Long
    Dim n As Long
    Dim nr As Long
   
    Application.ScreenUpdating = False
   
'   Set workheet objects
    Set inSht = Sheets("Sheet1")
    Set outSht = Sheets("Sheet2")
   
'   Copy headers from input sheet to output sheet
    inSht.Range("A1:X1").Copy outSht.Range("A1")
   
'   Find last row with data in input sheet
    lr = inSht.Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all records on input sheet, starting with row 2
    For r = 2 To lr
'       Get Arrival Date and Nights from row
        dte = inSht.Cells(r, "G")
        nt = inSht.Cells(r, "I")
'       Loop through all nts
        For n = 1 To nt
'           Find next available for on output sheet
            nr = outSht.Cells(Rows.Count, "A").End(xlUp).Row + 1
'           Copy row from input sheet to output sheet
            inSht.Range(Cells(r, "A"), Cells(r, "X")).Copy outSht.Cells(nr, "A")
'           Populate date
            outSht.Cells(nr, "G") = dte
'           Increment date by 1
            dte = dte + 1
        Next n
    Next r
   
    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!", vbOKOnly
       
End Sub
Click to expand...

Hi Joe,

It worked well. Thank you so much for your timely help. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
transpose data with same value in column A by date order
Replies
5
Views
175
Fluff
Fluff
U
  • Question
Week number based on the year starting with a particular date
Replies
2
Views
429
Jeffrey Mahoney
Jeffrey Mahoney
A
  • Question
Need to convert value depending on currency and date
Replies
2
Views
191
deLockloire
D
C
  • Question
SUMIFS if a date is within the range
Replies
1
Views
239
Fluff
Fluff
M
  • Solved
How merge amounts into textbox based on two comboboxes instead of one combobox
Replies
6
Views
116
Maklil
M

Forum statistics

Threads
1,215,734
Messages
6,126,544
Members
449,316
Latest member
sravya

Share this page

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
Back
Top