How to prevent auto numbering at duplicate column header in Excel table?
In Excel, when you type the duplicate column header in the table, it will be auto-added a number just as name, name1, name2, and the number cannot be removed as the below screenshot shows. To prevent the auto numbering at duplicate column header in table, you can add trailing space to the duplicate header. Here is a VBA code that can help you repeat the header and auto-add trailing spaces to let them different but looks the same.
Note: the methods provided in this tutorial are tested in Excel 2021, there may be some different in different Excel verisions.
1. Type the headers you want to repeat in a column, then press Alt + F11 keys to enable Microsoft Visual Basic for Applications window.
2. Click Insert > Module to create a new module in the Microsoft Visual Basic for Applications window, then copy and paste the below code to it.
VBA: Repeat and add trailing spaces
Sub repeat() 'UpdatebyExtendoffice20220927 Dim xRg As Range Dim saveRg As Range Dim xIndex As Integer Dim xCount As Integer Dim k As Integer Dim xStr As String Set xRg = Application.InputBox("Select the cells that you want to repeat", "kte", , , , , , 8) xIndex = Application.InputBox("Enter the repeat times", "kte") Set saveRg = Application.InputBox("Select a cell to output", "kte", , , , , , 8) xStr = "" xCount = xRg.Cells.Count * xIndex Set saveRg = saveRg.Range("a1").Resize(1, xCount) k = 0 For i = 1 To xIndex For j = 1 To xRg.Cells.Count k = k + 1 saveRg.Cells(k).Value = xRg.Cells(j).Value + xStr Next xStr = xStr + " " Next End Sub
3. Click Run button in the ribbon or press F5 key to activate the code, in the first popping dialog, select the headers you want to repeat. Click OK.
4. In the second popping dialog, type the repeating times you want. Click OK.
5. Select a cell that you want to output the repeated headers. Click OK.
Now the headers have been repeated and added with trailing spaces.
6. Select the headers and copy them by pressing Ctrl + C, and paste them back to the table you need by pressing Ctrl + V.
Now you can see the table has duplicate column headers without numbering.