Most Frequent Cell with conditional

JODomingos

New Member
Joined
Jul 11, 2020
Messages
48
Office Version
  1. 2016
Platform
  1. Windows
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.

1596821556271.png
 

Attachments

  • 1596821527899.png
    1596821527899.png
    16.5 KB · Views: 1

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
How about:

VBA Code:
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
 
Last edited:

JODomingos

New Member
Joined
Jul 11, 2020
Messages
48
Office Version
  1. 2016
Platform
  1. Windows
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:

1596900044823.png


after running:

1596900149103.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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
ABCDEFGHIJ
1
2
37777777777
4ACTTTTTATTT./.TTTTAA
5ATTTTTTTTTTTTTTTTAA
6Most FrequentTTTTTTTTTTTTTTTTAA
7
8ACTTTTTATTT./.TTTTAA
9ATTTTTTTTTTTTTTTTAA
10Most FrequentTTTTTTTTTTTTTTTTAA
Hoja1
 

JODomingos

New Member
Joined
Jul 11, 2020
Messages
48
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You were completly Right! Now it worked.

I really appreciate your support.
 

JODomingos

New Member
Joined
Jul 11, 2020
Messages
48
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.



1599054021513.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
1599054021513.png

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:
Rich (BB code):
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

New Member
Joined
Jul 11, 2020
Messages
48
Office Version
  1. 2016
Platform
  1. Windows
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!

1599073153807.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this. Complete first and second condition:
fst = ",AA,CC,GG,TT,"
snd = ",AT,CA,CG,GC,GT,AG,"


VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top