技术需求：将两列异语言对应加粗表头匹配对齐至同一行

Hey there! Let's fix that annoying misalignment between your bold English headers in Column A and their corresponding translated ones in Column B. Here's a practical, step-by-step plan to get them perfectly lined up:

1. Extract all bold headers (with their original row positions) #

First, we need to pull out every bold header from both columns along with which row they're currently on. This makes it way easier to match them later without hunting through the entire table.

For Excel Users (VBA Script) #

Pop open the VBA editor (Alt + F11), insert a new module, and paste this code:

Sub ExtractBoldHeaders()
    Dim ws As Worksheet
    Dim lastRowA As Long, lastRowB As Long
    Dim i As Long, j As Long
    Dim headerListA As Collection, headerListB As Collection
    
    Set ws = ActiveSheet
    Set headerListA = New Collection
    Set headerListB = New Collection
    
    ' Grab bold headers from Column A
    lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = 1 To lastRowA
        If ws.Cells(i, "A").Font.Bold Then
            headerListA.Add ws.Cells(i, "A").Value & "|" & i ' Store header text + original row number
        End If
    Next i
    
    ' Grab bold headers from Column B
    lastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    For j = 1 To lastRowB
        If ws.Cells(j, "B").Font.Bold Then
            headerListB.Add ws.Cells(j, "B").Value & "|" & j ' Store header text + original row number
        End If
    Next j
    
    ' Output to a new sheet for easy reviewing
    Dim newWs As Worksheet
    Set newWs = ThisWorkbook.Sheets.Add(After:=ws)
    newWs.Name = "Extracted Headers"
    newWs.Cells(1, 1).Value = "English Header"
    newWs.Cells(1, 2).Value = "Original Row (A)"
    newWs.Cells(1, 3).Value = "Target Language Header"
    newWs.Cells(1, 4).Value = "Original Row (B)"
    
    For i = 1 To headerListA.Count
        newWs.Cells(i + 1, 1).Value = Split(headerListA(i), "|")(0)
        newWs.Cells(i + 1, 2).Value = Split(headerListA(i), "|")(1)
    Next i
    
    For j = 1 To headerListB.Count
        newWs.Cells(j + 1, 3).Value = Split(headerListB(j), "|")(0)
        newWs.Cells(j + 1, 4).Value = Split(headerListB(j), "|")(1)
    Next j
End Sub

Run the script, and you'll get a new sheet with all your bold headers laid out alongside their original row numbers.

For Google Sheets Users (Apps Script) #

Go to Extensions > Apps Script, delete the default code, and paste this:

function extractBoldHeaders() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('Extracted Headers');
  
  // Set up headers for the extracted list
  newSheet.getRange(1,1).setValue('English Header');
  newSheet.getRange(1,2).setValue('Original Row (A)');
  newSheet.getRange(1,3).setValue('Target Language Header');
  newSheet.getRange(1,4).setValue('Original Row (B)');
  
  const dataA = sheet.getRange('A:A').getValues();
  const dataB = sheet.getRange('B:B').getValues();
  const formatA = sheet.getRange('A:A').getFontWeights();
  const formatB = sheet.getRange('B:B').getFontWeights();
  
  let rowCounterA = 2;
  for(let i=0; i<dataA.length; i++){
    if(formatA[i][0] === 'bold' && dataA[i][0] !== ''){
      newSheet.getRange(rowCounterA,1).setValue(dataA[i][0]);
      newSheet.getRange(rowCounterA,2).setValue(i+1);
      rowCounterA++;
    }
  }
  
  let rowCounterB = 2;
  for(let j=0; j<dataB.length; j++){
    if(formatB[j][0] === 'bold' && dataB[j][0] !== ''){
      newSheet.getRange(rowCounterB,3).setValue(dataB[j][0]);
      newSheet.getRange(rowCounterB,4).setValue(j+1);
      rowCounterB++;
    }
  }
}

Click the run button, authorize the script, and you'll have your extracted headers ready to go.

2. Match the header pairs #

Now that you have all headers in one place, pair up each English header with its corresponding translated version:

• Manual matching (most reliable for technical/industry-specific terms): Add a new column to the "Extracted Headers" sheet, label it "Match ID", and assign the same number to each corresponding pair (e.g., 1 for "Customer Name" and its translation, 2 for "Order Date" and its translation, etc.).
• Automatic matching (for simple, straightforward headers): Use Excel's Fuzzy Lookup add-in or Google Sheets' MATCH function with approximate matching, but double-check results to avoid mismatches.

3. Realign the headers in your original table #

Once you have your matched pairs, it's time to fix the original table:

1. For each pair, note the original row numbers from the extracted sheet.
2. Either:
   • Cut and paste: Move the translated header (from Column B) to the same row as its English counterpart (in Column A), preserving bold formatting (regular copy-paste keeps formatting intact).
   • Insert blank rows: If moving headers would disrupt other data, insert blank rows above/below the misaligned header to shift it into the correct row.
3. For large datasets, consider rebuilding the table using the matched pairs from the extracted sheet—this ensures perfect alignment from the start.

Pro Tip: Preserve formatting #

When moving headers, use regular Ctrl+C/Ctrl+V (or Cmd+C/Cmd+V on Mac) to keep the bold formatting intact. Avoid "Paste Values Only" unless you intentionally want to strip formatting.

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