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.

 

[DanteAmor]

How about:

Sub Most_Frequent()
  Dim a As Range, lc As Long
  Dim s1 As String, s2 As String
  
  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 & "&"","","",AA,CC,GG,TT,""))," & s1 & _
                 ",IF(ISNUMBER(SEARCH("",""&" & s2 & "&"","","",AA,CC,GG,TT,""))," & s2 & ",""""))"
      .Value = .Value
    End With
  Next
End Sub

 

[JODomingos]

Hello DanteAmor.

It almost worked perfectly! When I have the red cell for example in the row 4, after running I got the number "0". instead of this I would like to Keep the original cell. In this example it would mantain "TT". Is it possible to adjust? Your help was too much useful. Thank you a lot friend.

before:

after running:

 

[DanteAmor]

For the correct functioning of the macro, you must keep the structure of the sheet that you showed at the beginning. It implies a row of "headings" in row 3.
After each "Source" a title like "Most Frequent", if you leave blank spaces the macro will have problems.

Modify your sheet with the information, keeping the same structure of your original image and try again.

varios 08ago2020.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3	7	7	7	7	7	7	7	7	7	7
4	A	CT	TT	TT	AT	TT	./.	TT	TT	AA
5	A	TT	TT	TT	TT	TT	TT	TT	TT	AA
6	Most Frequent	TT	TT	TT	TT	TT	TT	TT	TT	AA
7
8	A	CT	TT	TT	AT	TT	./.	TT	TT	AA
9	A	TT	TT	TT	TT	TT	TT	TT	TT	AA
10	Most Frequent	TT	TT	TT	TT	TT	TT	TT	TT	AA
Hoja1

 

[JODomingos]

You were completly Right! Now it worked.

I really appreciate your support.

 

[DanteAmor]

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

 

[JODomingos]

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

Hello DanteAmor, How are you friend?
Based on your macro script I would like to perform a simple adjustment. Can you help me?
I would like to keep the formula you have created but I need to add more one: When i Have cells diferent of "./." then keep it as the most frequent. for example, in red is the result I get with this macro, and in green is the result I would like to have now. Thanks in advance.

 

[DanteAmor]

in red is the result I get with this macro

In no case does the macro write "./."

If you want AT or CA you must add those letters to the macro string.

Try this:

Sub Most_Frequent()
  Dim a As Range, lc As Long
  Dim s1 As String, s2 As String
  
  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 & "&"","","",AA,AT,CA,CC,GG,TT,""))," & s1 & _
                 ",IF(ISNUMBER(SEARCH("",""&" & s2 & "&"","","",AA,AT,CA,CC,GG,TT,""))," & s2 & ",""""))"
      .Value = .Value
    End With
  Next
End Sub

 

[JODomingos]

I understood your point. It really worked. Just one comment:
Is it possible to keep those AT, CA, GT, AG.... just in case of having "./." ? Because as shown in column seven the result expected would be GG and not "GT". But in case of having "./." then a GT should be filled (Shown in column 3). It is like to have a preference firstly for (AA, CC, GG, TT) and if there is no firt condition then fill with cell "GT, AG..... which is different of "./."

I really appreciate your suport!

 

[DanteAmor]

Try this. Complete first and second condition:
fst = ",AA,CC,GG,TT,"
snd = ",AT,CA,CG,GC,GT,AG,"

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("",""&" & s1 & "&"","",""" & snd & """))," & s2 & ",""""))))"
      .Value = .Value
    End With
  Next
End Sub