Find top values in range, if top value already exists in specific row, enter second value

[Aaron092]

Hi all

I need to get the top value from a list of 12 possible responses, check the value does not already exist in a specific row, if it does, then enter the second top value (or third, fourth etc. until it is a unique value in the row), else enter it as a value.

E.G:
Value 1 Value 2
Value 1 Value 2
Value 1 Value 1
Value 2 Value 1
Value 3 Value 3
Value 1 Value 1

Value 1 Value 2

Because Value 1 in the first column is taken, Value 2 is substituted as it is the second most common value.
I've attached a desensitised example which will hopefully explain a bit more what I'm trying to achieve.

I'm currently using a formula to get the top value (index mode match), manually going through the answers and omitting duplicate values for the second or third highest

Any help with this is greatly appreciated, and if you need any further information, please let me know

Thanks

EDIT: Updated the attachment as I realised columns 1 and 6 we're identical.

EDIT 2: I've managed to make some code to get this to work - I have 12 instances of this code in total, running one after the other but for future reference here is one example of it:

'[[=== AW ===]][[=== AW ===]][[=== AW ===]][[=== AW ===]][[=== AW ===]]
Range("AW2").Select
Selection.End(xlDown).Select
eRow = Split(ActiveCell(1).Address(1, 0), "$")(1)
ActiveCell.Offset(3, 0).Select
xx = Split(ActiveCell(1).Address(1, 0), "$")(1) 'get row
xy = Split(ActiveCell(1).Address(1, 0), "$")(0) 'get column
TrgCel1 = xy & xx

c1a = WorksheetFunction.CountIf(Range("AW2:AW" & eRow), "value1")
c2a = WorksheetFunction.CountIf(Range("AW2:AW" & eRow), "value2")
c3a = WorksheetFunction.CountIf(Range("AW2:AW" & eRow), "value3")
c4a = WorksheetFunction.CountIf(Range("AW2:AW" & eRow), "value4")
c5a = WorksheetFunction.CountIf(Range("AW2:AW" & eRow), "value5")
c6a = WorksheetFunction.CountIf(Range("AW2:AW" & eRow), "value6")
c7a = WorksheetFunction.CountIf(Range("AW2:AW" & eRow), "value7")
c8a = WorksheetFunction.CountIf(Range("AW2:AW" & eRow), "value8")
c9a = WorksheetFunction.CountIf(Range("AW2:AW" & eRow), "value9")
c10a = WorksheetFunction.CountIf(Range("AW2:AW" & eRow), "value10")
c11a = WorksheetFunction.CountIf(Range("AW2:AW" & eRow), "value11")
c12a = WorksheetFunction.CountIf(Range("AW2:AW" & eRow), "value12")

WFM = WorksheetFunction.Max(c1a, c2a, c3a, c4a, c5a, c6a, c7a, c8a, c9a, c10a, c11a, c12a)

If WFM = c1a Then
    retval = c1aa
    Else
    If WFM = c2a Then
        retval = c2aa
        Else
        If WFM = c3a Then
            retval = c3aa
            Else
            If WFM = c4a Then
                retval = c4aa
                Else
                If WFM = c5a Then
                    retval = c5aa
                    Else
                    If WFM = c6a Then
                        retval = c6aa
                        Else
                        If WFM = c7a Then
                            retval = c7aa
                            Else
                            If WFM = c8a Then
                                retval = c8aa
                                Else
                                If WFM = c9a Then
                                    retval = c9aa
                                    Else
                                    If WFM = c10a Then
                                        retval = c10aa
                                        Else
                                        If WFM = c11a Then
                                            retval = c11aa
                                            Else
                                            If WFM = c12a Then
                                                retval = c12aa
                                                Else
                                            End If
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
End If
ActiveCell.Value = retval

'ActiveCell.Value = WorksheetFunction.Max(c1a, c2a, c3a, c4a, c5a, c6a, c7a, c8a, c9a, c10a, c11a, c12a)
'ActiveCell.Copy Destination:=Cells(ActiveCell)

Dim IntLoop As Integer
IntLoop = 2
Looperman1:
For Each c In Range("AV" & avRow & ":BG" & avRow)
cyk = Split(c(1).Address(1, 0), "$")(0)
    If ActiveCell.Value = Range(c).Value Then
                            WFM = WorksheetFunction.Large(Array(c1a, c2a, c3a, c4a, c5a, c6a, c7a, c8a, c9a, c10a, c11a, c12a), IntLoop)
                            IntLoop = IntLoop + 1
                            Loopman = True
                            If WFM = c1a Then
                                retval = c1aa
                                Else
                                If WFM = c2a Then
                                    retval = c2aa
                                    Else
                                    If WFM = c3a Then
                                        retval = c3aa
                                        Else
                                        If WFM = c4a Then
                                            retval = c4aa
                                            Else
                                            If WFM = c5a Then
                                                retval = c5aa
                                                Else
                                                If WFM = c6a Then
                                                    retval = c6aa
                                                    Else
                                                    If WFM = c7a Then
                                                        retval = c7aa
                                                        Else
                                                        If WFM = c8a Then
                                                            retval = c8aa
                                                            Else
                                                            If WFM = c9a Then
                                                                retval = c9aa
                                                                Else
                                                                If WFM = c10a Then
                                                                    retval = c10aa
                                                                    Else
                                                                    If WFM = c11a Then
                                                                        retval = c11aa
                                                                        Else
                                                                        If WFM = c12a Then
                                                                            retval = c12aa
                                                                            Else
                                                                        End If
                                                                    End If
                                                                End If
                                                            End If
                                                        End If
                                                    End If
                                                End If
                                            End If
                                        End If
                                    End If
                                End If
                            End If
                            ActiveCell.Value = retval
    Else
    End If
If Loopman = True Then
    Loopman = False
    GoTo Looperman1
    Else
End If
ActiveCell.Value = retval
Next c
'awCel = ActiveCell.Value
'EXIT VALUES
Set TrgCel1 = Nothing
Set eRow = Nothing
Set c1a = Nothing
Set c2a = Nothing
Set c3a = Nothing
Set c4a = Nothing
Set c5a = Nothing
Set c6a = Nothing
Set c7a = Nothing
Set c8a = Nothing
Set c9a = Nothing
Set c10a = Nothing
Set c11a = Nothing
Set c12a = Nothing
Set WFM = Nothing
Loopman = False