Most Frequent Cell with conditional

[JODomingos]

Hi Fellows.

I need a help to design a VBA macro for the following issue: I want to Unify rows 4 and 5 (SourceA), based on the condition:

When the cell B4 and B5 have "AA" ; "CC" ; "GG" ; "TT" then get the most frequent in the column of source A range and put into third cell below(Most frequent), make a loop for all cells right until the last column filled.

If B4 or B5 have anything different of "AA" ; "CC" ; "GG" ; "TT" then consider just the cell with the double letter.

Besides that i would like to perform that macro for many Sources in coluns A.

See in the picture the expected result.

Really Thank you in advance.

 

[JODomingos]

Almost there Friend! The script worked very well but when there is cases like column B and D, which it should be expected to fill with "AT" it results a empty cell.
I really thank you again.

 

[DanteAmor]

Try this. my fault an error in the formula:

Sub Most_Frequent()
  Dim a As Range, lc As Long
  Dim s1 As String, s2 As String
  Dim fst As String, snd As String

  fst = ",AA,CC,GG,TT,"
  snd = ",AT,CA,CG,GC,GT,AG,"
  
  lc = Cells(3, Columns.Count).End(1).Column
  For Each a In Range("A4", Range("A" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants).Areas
    With a.Cells(a.Rows.Count).Offset(, 1).Resize(1, lc - 1)
      s1 = a.Offset(0, 1).Cells(1).Address(0, 0)
      s2 = a.Offset(1, 1).Cells(1).Address(0, 0)
      .Formula = "=IF(ISNUMBER(SEARCH("",""&" & s1 & "&"","",""" & fst & """))," & s1 & _
                 ",IF(ISNUMBER(SEARCH("",""&" & s2 & "&"","",""" & fst & """))," & s2 & _
                 ",IF(ISNUMBER(SEARCH("",""&" & s1 & "&"","",""" & snd & """))," & s1 & _
                 ",IF(ISNUMBER(SEARCH("",""&" & s2 & "&"","",""" & snd & """))," & s2 & ",""""))))"
      .Value = .Value
    End With
  Next
End Sub

 

[JODomingos]

Hi Friend, Almost There! Column B and D should be filled with "AT", but it results in empty cell.

I really thank you.

 

[JODomingos]

Sorry, I duplicate my answer. I will try your formula

 

[JODomingos]

It worked perfectly! Thank you for your support. Amazing help!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.