如何用宏合并表格Time列并按时间键对应排序Length值？

Hey there! Your initial idea of using Time values as keys to group their corresponding Length values is exactly the right direction. Let's break down how to implement this with a VBA macro, plus a no-code alternative using Power Query for efficiency.

Option 1: VBA Macro Implementation #

This macro will use a dictionary to map each unique Time value to its associated Length entries, then sort the Time keys and output the consolidated, sorted data.

Step-by-Step Macro Code #

Sub ConsolidateAndSortTimeLength()
    Dim ws As Worksheet
    Dim timeDict As Object
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long, outputRow As Long
    Dim timeKey As Variant
    Dim lengthColl As Collection
    
    ' Target worksheet (adjust to your sheet name if needed)
    Set ws = ActiveSheet
    ' Initialize dictionary to store Time-Length groups
    Set timeDict = CreateObject("Scripting.Dictionary")
    
    ' Get the last used row and column in the sheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' Loop through all columns to find Time headers
    For j = 1 To lastCol
        If ws.Cells(1, j).Value = "Time" Then
            ' Loop through each row (skip header row)
            For i = 2 To lastRow
                ' Skip empty/highlighted cells (treat as no value)
                If Not IsEmpty(ws.Cells(i, j).Value) Then
                    timeKey = ws.Cells(i, j).Value
                    ' Assume Length column is immediately next to Time column
                    ' If your layout is different, adjust this to target the correct Length cell
                    Dim lengthVal As Variant
                    lengthVal = ws.Cells(i, j + 1).Value
                    
                    ' Create a new collection for the Time key if it doesn't exist
                    If Not timeDict.Exists(timeKey) Then
                        Set lengthColl = New Collection
                        timeDict.Add timeKey, lengthColl
                    End If
                    ' Add the Length value to the corresponding Time group
                    timeDict(timeKey).Add lengthVal
                End If
            Next i
        End If
    Next j
    
    ' Set up output headers (adjust columns K/L to your preferred starting point)
    outputRow = 2
    ws.Cells(1, "K").Value = "Consolidated Time"
    ws.Cells(1, "L").Value = "Sorted Length"
    
    ' Sort the Time keys
    Dim sortedKeys As Variant
    sortedKeys = GetSortedKeys(timeDict)
    
    ' Output the consolidated and sorted data
    For Each timeKey In sortedKeys
        For Each lengthVal In timeDict(timeKey)
            ws.Cells(outputRow, "K").Value = timeKey
            ws.Cells(outputRow, "L").Value = lengthVal
            outputRow = outputRow + 1
        Next lengthVal
    Next timeKey
    
    MsgBox "Data consolidation and sorting complete!", vbInformation
End Sub

' Helper function to sort dictionary keys numerically
Function GetSortedKeys(dict As Object) As Variant
    Dim keys As Variant
    Dim i As Long, j As Long
    Dim temp As Variant
    
    keys = dict.Keys
    ' Bubble sort (works well for small to medium datasets)
    For i = LBound(keys) To UBound(keys) - 1
        For j = i + 1 To UBound(keys)
            If keys(i) > keys(j) Then
                temp = keys(i)
                keys(i) = keys(j)
                keys(j) = temp
            End If
        Next j
    Next i
    
    GetSortedKeys = keys
End Function

Key Notes for the Macro: #

• Adjust Worksheet/Columns: If your sheet isn't the active one, replace ActiveSheet with ThisWorkbook.Sheets("YourSheetName"). If Length columns aren't directly next to Time columns, modify the lengthVal line to target the correct cell (e.g., check for "Length" headers paired with Time columns).
• Empty Cells: The code skips empty/highlighted cells as requested.
• Sorting: The helper function uses a bubble sort; for very large datasets, you could replace it with a faster sorting method like quicksort.

Option 2: No-Code Power Query Solution (More Efficient for Large Datasets) #

If you prefer avoiding VBA, Power Query is a built-in Excel tool that can handle this task quickly:

• Select your data range, go to the Data tab, click From Table/Range (check "My table has headers").
• In the Power Query Editor:
  1. Hold Ctrl to select all pairs of Time and Length columns.
  2. Right-click one of the selected columns, choose Unpivot Columns > Unpivot Only Selected Columns.
  3. Rename the unpivoted columns to "Consolidated Time" and "Sorted Length" (optional).
  4. Click Sort Ascending on the "Consolidated Time" column.
  5. Go to Home > Close & Load to export the cleaned, sorted data back to Excel.

This method is non-destructive (it keeps your original data intact) and easy to update if your source data changes.

内容的提问来源于stack exchange，提问作者Ho Jun Hong