Data will pair with vertical header, but not horizontal header

[ddr4lyfe]

I'm trying to set up a spreadsheet that I can use to keep track of wins and losses between two people, the entire roster is repeated on the vertical and horizontal header. I have a VB function that will keep my list of contenders on columnA in real-time alphabetical order as I add more contenders, and the row1 header contains simple Offset() functions to mirror what's in columnA.

Now, the actual data (number of wins) pairs fine with the columnA changes (if a new contender gets entered, the number of wins for the contenders will pair correctly), but it doesn't stay paired for row1. How can I make it so that the data pairs with both columnA and row1 changes?

Let me know if you need more of a description! Thanks!

[codeslizer]

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..

[ddr4lyfe]

Alright, attached is my sample worksheet. If you enter "Adam" as a contender to ColumnA, then you'll see the data (number of wins in the match-up) move down with the names in ColumnA, but they don't move to the right to stay paired with the contender names in Row1. That's the issue I'm trying to fix.

[codeslizer]

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.

[ddr4lyfe]

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. :/

[codeslizer]

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.