Yes, this is a macro which will apply the Countif formula to each of the four columns (B, D, F, and H). You initially descried what you want as 10 columns with an unknown row number, but you sample is 4 columns with the same number of rows. I realize it is a sample, but the reason I mention it, the code could be written slightly different for all different types of scenarios.
In the end, if you do not want a macro, the formula by itself will suffice. This macro creates the Countif formula, but then copies it as values instead of leaving the formula intact.
If you want to just use a formula, paste into B2 and copy down...
=IF(A1<>A2,COUNTIF(A$2:A$14,A2),"")
...now apply to the other columns, but again, this is only if the row numbers are the same. If they are not, just adjust
A$14 to the length of your rows.
Code:
Sub ApplyCountif()
Dim LastRow As Long
Dim LastCol As Long
Dim i As Long
LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
Const x As Long = -1
Const sFormula As String = "=IF(R[" & x & "]C[" & x & "]<>RC[" & x & "],COUNTIF(C[" & x & "],RC[" & x & "]),"""")"
For i = 1 To LastCol Step 2
LastRow = Cells(Rows.Count, i).End(xlUp).Row - 1
Application.ScreenUpdating = False
With Cells(2, i + 1).Resize(LastRow)
.Formula = sFormula
.Value = .Value
End With
Next i
Application.ScreenUpdating = True
End Sub
Paste code in a Normal module - Where to paste code
- Highlight macro to copy >> Ctrl + C >> Open your workbook
- Alt + F11 >> opens the Visual Basic Editor (VBE)
- Ctrl + R >> opens the Project Explorer (if not already open on left side of screen)
- Insert menu >> Module or Alt + I, M >> activates the Insert menu and inserts a Standard Module
- Paste code >> Ctrl + V (right side of screen)
- Alt + Q >> exits VBE and returns to Excel
- Back in Excel >> Alt + F8 >> Macro Dialog Box >> Highlight macro >> Run