Data will pair with vertical header, but not horizontal header

Yes sir, we'd like to hear more. It'll be lot easier if you could help us with a sample worksheet to explain your requirements..

That's coz the win-data is hard-coded. I suggest if the data to be fed on some other sheet, say, with columns Challenger1, Challenger2,Winner which includes details about who had a match against whom and winner keeps the record of who won - can be name or number 1 and 0.. and that sheet with formula should be used only to provide a view like that.
The reason is coz you basically perform a sorting on column A. So based on name's starting letter the name gets arranged in the column and later you delete the duplicates, and for corresponding change on the headers you use OFFSET. But how would the data would come to know where to go?

As of now, with my intellectuals I can only suggest this part. Maybe other experts might have some better way to meet your requirements.

Ok, so then how could I take a list that is vertical and make it horizontal with corner dragging? Whenever I try to corner drag horizontally, the cell references increment down the columns instead of across the rows.


EDIT: I managed to use TRANSPOSE() instead of OFFSET() to get the same list filling effect, but the win data still won't move with the top header. :/

Hey buddy, attaching the re-worked sample file of yours based on my concept. Gives you the same result as you desired. I simply used dynamic named ranges to store the challengers and winners name which is further used in doing the COUNTIFS(). Following is the code, you can easily drag the formula to either left or right down or up, the formulae would adapt to the changes.

=COUNTIFS(Challenger1,$A2,Challenger2,B$1,Winners,$A2)

and I have also made changes in the change event.. following is the code..

If Target.Column = 1 Then
    Application.EnableEvents = False
    Range("$A$1:$A$500").Sort Key1:=Range("A2"), Order1:=xlAscending, _
    Header:=A1, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    Range("A2:A500").RemoveDuplicates Columns:=Array(1)
    Application.EnableEvents = True
End If

Download the attachment and try to feed "James" in column A. You'll find some differences. I have used another sheet called "WIN-Stats" to store the WINNER's record as I mentioned earlier. Also, if the COUNTIFS() return zero, I have a conditional formatting to avoid black zero text.