Copy values to column based on date

[myteedog]

Hello, I am sure this has been answered somewhere but I did search and none of the solutions I found worked for this situation. I'm guessing I wasn't using correct terms. I would like to add values to a column based on date. Example: If a date in A:A equals July(E:2) I want it to display the date(A:A) and time(B:B) to the July set of values starting at E:3 and F:3. Which I can get to work row by row but I need all of the dates and times at the top of the columns like in the example(I just copied and pasted a few for reference). I have figured this out before but I no longer have the files to refer back to. I have always been able to figure things out on my own but my brain has shut down on this one. Any help is greatly appreciated!

 

[OaklandJim]

I'd like to try to assist but I need to better understand the need. It seems that you want all the date/value combinations for a month transferred to the column? What is the value in A2 for? What does this mean (i.e. be more specific):

I can get to work row by row but I need all of the dates and times at the top of the columns like in the example

For me to assist please provide some data to work with. Provide a link with dropbox etc. to a worksheet with data. Or, provide the data using the excellent Mr. Excel add-in that enables you to post part of a worksheet to the list. See this link for the add in:

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

I presume you expect a macro to do this?

Jim

 

[OaklandJim]

Here is code that does what you want. You may have to modify it to accommodate the actual workbook/worksheet characteristics like worksheet name and location of cells where data is "anchored".

Option Explicit

' ----------------------------------------------------------------
' Procedure Name: ListFromColumns
' Purpose: Copy date/time values in date/time data to the results list area.
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jim
' Date: 3/29/2025
' ----------------------------------------------------------------

Sub ListFromColumns()

'   Worksheet with the data and results.
    Dim wsData As Worksheet

'   Upperleftmost cell that anchors the results list.
    Dim rListAnchor As Range
    
'   The upperleftmost cell where dates are located.
    Dim rColsAnchor As Range
    
'   Offset from column anchor cell used when processing date/times.
    Dim iColOffset As Long
    
'   Used when looping through date/times rows.
    Dim iRow As Long
    
'   Holds the value of the last date/time column to process.
    Dim iLastColumn As Long
    
'   Used to hold the last row value for the results list range.
    Dim iLastRow As Long
    
'   Holds the value of the count of rows to clear.
    Dim iListRows As Long
    
'   Used to iterate through months' data.
    Dim iMonth As Long
    
'   Used to iterate through months' data.
    Dim iMonths As Long
        
'   This keeps track of which month's data is being processed.
    Dim iMonthIndex As Long
    
'   In the area where data is copied to, this is the index of
'   the row being processed (i.e., into which date/time values
'   are transferred).
    Dim iListIndex As Long
    
'   Used to get date and time values to be copied.
    Dim sDateValue As String
    Dim dTime As Date

'   Duh! holds number of months found (to process).
    Dim iMonthsToProcess As Long
       
'   => Change this to accommodate your workbook <=
    Set wsData = Worksheets("Sheet2")
    
'   => Change this to accommodate your workbook <=
'   Upperleftmost cell where the results list data is located.
    Set rListAnchor = wsData.Range("A3")

'   => Change this to accommodate your workbook <=
'   Upperleftmost cell where the columnar data is located.
    Set rColsAnchor = wsData.Range("E2")
    
'   -----------------------------
'      Clear existing results
'   -----------------------------

'   Need to know last row of existing results to clear the values.
    iLastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
   
'   Determine which rows in the results list to clear. Count is the last row
'   minus the row just below the anchor cell.
    iListRows = iLastRow - rListAnchor.Row + 1
    
'   Clear the existing results if the range is not already empty.
    If iListRows > 1 _
     Then rListAnchor.Cells(1).Resize(iListRows, 2).ClearContents

'   ----------------------------------
'      Get columns' characteristics
'   ----------------------------------
    
'   Find last column in date/time data.
    iLastColumn = rColsAnchor.Cells(2, 10000).End(xlToLeft).Column
    
'   Calculate the number of months to process. /2 becuase there are two
'   columns per month.
    iMonthsToProcess = (iLastColumn - rColsAnchor.Column) / 2

'   ----------------------------------
'          Process months
'   ----------------------------------
'   Iterate through all months' data
    
'   Initialize the var that keeps track of what list results row to process.
    iListIndex = 0
    
    For iMonthIndex = 1 To iMonthsToProcess
    
'       Get column offset (from rColsAnchor). There are two columns per month
'       hence the * 2. Minus two becuase the first monht' offset is zero.
        iColOffset = (iMonthIndex * 2) - 2
        
'       With the cell in the upperleftmost position in the date/time currently
'       being processed.
        With rColsAnchor.Offset(0, iColOffset)
            
'           Initialize month's date/time pairs processed count.
            iRow = 0
            
'           First time though the Do While loop need some text in var sDateValue
'           or Do While loop with not start.
            sDateValue = "x"
            
'           Need to skip a row each time another month is encountered or there will
'           be a blank line between months in results list.
            If iMonthIndex > 1 Then iListIndex = iListIndex - 1

'           -------------------------------------------
'                Iterate date/time values for a month
'           -------------------------------------------

'           For a date/time range iterate through them to transfer to results list area.
            Do While sDateValue <> ""
                
'               Increment the var keeping track of which result list row isto be filled.
                iListIndex = iListIndex + 1
               
'               Increment row index for date/time data for a month.
                iRow = iRow + 1
    
'               Get the date and time from the month-specific list.
                sDateValue = .Offset(iRow, 0)
                dTime = .Offset(iRow, 1)
                
'               If the date/time is not empty then process it.
                If dTime <> 0 _
                 Then
                 
'                   Copy the date/time values to the results list.
                    With rListAnchor
                    
'                       Copy date to the date cell in the results list.
                        .Offset(iListIndex - 1).Value = sDateValue
                        
'                       Copy then format time to the time cell in the results list.
                        With .Offset(iListIndex - 1, 1)
                            .Value = dTime
                            .NumberFormat = "h:mm"
                        End With
                    End With
                End If

            Loop
        
        End With
    
    Next iMonthIndex

End Sub