求助：将Sheet1中以New Entry分组的数据转至Sheet2新列

Got it, let's tackle this problem step by step. You've got messy, unstructured data in Sheet1 Column A where each group starts with "New Entry"—some groups span multiple rows, others might be incomplete, but every group follows that starting marker. Your goal is to move each entire group into its own column starting from Sheet2 Column B. Here are two reliable approaches depending on your Excel version:

Approach 1: Dynamic Array Formula (Excel 365/2021) #

This method uses modern Excel functions to avoid manual work and handle dynamic data automatically:

1. Add a Group ID Helper Column in Sheet1

   • In Sheet1 Column B (cell B1), enter this formula and drag it down to cover all your data:

     =SCAN(0, A:A, LAMBDA(current_id, cell_value, IF(cell_value="New Entry", current_id+1, current_id)))
     

   • This assigns a unique number to every row in each group (e.g., all rows under the first "New Entry" get 1, the next group gets 2, etc.)

2. Pull Groups into Sheet2 Columns

   • In Sheet2 cell B1, enter this formula:

     =TRANSPOSE(FILTER(Sheet1!A:A, Sheet1!B:B=COLUMN()-1))
     

   • Now drag this formula horizontally across Sheet2's columns. Each column will populate with all rows from the corresponding group in Sheet1, starting with "New Entry".

   Note: If you want to keep blank rows within groups, the FILTER function will preserve them automatically. If you need to skip blanks, adjust the formula to =TRANSPOSE(FILTER(Sheet1!A:A, Sheet1!B:B=COLUMN()-1, Sheet1!A:A<>""))

Approach 2: VBA Macro (All Excel Versions) #

If you're using an older Excel version without dynamic arrays, or want to automate the process fully, use this VBA script:

1. Open the VBA Editor

   • Press Alt + F11 to launch the VBA editor.
   • Right-click your workbook in the "Project Explorer" pane > Insert > Module.

2. Paste the Macro Code

   Sub SplitEntriesToColumns()
       Dim wsSource As Worksheet, wsDest As Worksheet
       Dim lastRow As Long, entryRow As Long, nextEntryRow As Long
       Dim destCol As Integer
       
       ' Set your source and destination sheets
       Set wsSource = ThisWorkbook.Sheets("Sheet1")
       Set wsDest = ThisWorkbook.Sheets("Sheet2")
       destCol = 2 ' Start pasting at Column B
       
       ' Find the last row with data in Sheet1 Column A
       lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
       entryRow = 1 ' Start checking from the first row
       
       Do While entryRow <= lastRow
           ' Locate the next "New Entry" marker
           On Error Resume Next
           nextEntryRow = wsSource.Range("A" & entryRow + 1 & ":A" & lastRow).Find(What:="New Entry", LookIn:=xlValues).Row
           On Error GoTo 0
           
           ' If no more entries are found, set nextEntryRow to the row after last data row
           If nextEntryRow = 0 Then nextEntryRow = lastRow + 1
           
           ' Copy the current group to the destination column
           wsSource.Range("A" & entryRow & ":A" & nextEntryRow - 1).Copy
           wsDest.Cells(1, destCol).PasteSpecial Paste:=xlPasteValues, Transpose:=False
           
           ' Move to the next column and next entry
           destCol = destCol + 1
           entryRow = nextEntryRow
       Loop
       
       ' Clean up clipboard
       Application.CutCopyMode = False
       MsgBox "Entries split successfully!", vbInformation
   End Sub
   

3. Run the Macro

   • Press F5 while in the module window, or go back to Excel and run it via Developer > Macros > Select SplitEntriesToColumns > Run.

Both methods will take your messy Column A data and organize each "New Entry" group into its own column in Sheet2. Test with a small subset first to make sure it fits your specific data quirks!

内容的提问来源于stack exchange，提问作者Israph