Highlight the most repeated value in columns

Yamasaki450 · Wednesday at 1:52 PM

Hi guys. I need some help please

I need VBA or conditional formatting formula to highlight most repeated values in each column. like shown on screenshot below.
I did this manually for the first three columns just to show example. So in column B the most repeated value is 30. In column C -5. And in column D -24 and -20.
I have a lot of columns to process... How can i do this the fastest way.

JamesCanale · Wednesday at 2:11 PM

I think this should work in xl2021. You might have trouble with blanks in your thing depending on how you implement it.

MrExcelPlayground24.xlsx

B

C

D

E

F

G

2

1

3

FALSE

3

1

2

3

5

FALSE

4

1

3

4

FALSE

5

2

4

2

FALSE

6

2

5

4

FALSE

7

2

6

3

FALSE

8

3

7

1

TRUE

9

3

8

2

TRUE

10

3

TRUE

11

3

TRUE

12

4

FALSE

13

4

FALSE

14

5

TRUE

15

5

TRUE

16

5

TRUE

17

5

TRUE

18

6

FALSE

19

6

FALSE

20

6

FALSE

21

7

FALSE

22

8

FALSE

23

8

FALSE

Sheet6

Cell Formulas
Range	Formula
C2:C9	C2	=UNIQUE(B2:B23)
D2:D9	D2	=MMULT(--(TOROW(B2:B23)=C2#),SEQUENCE(ROWS(B2:B23),1,1,0))
E2:E3	E2	=FILTER(C2#,D2#=MAX(D2#))
F2:F23	F2	=ISNUMBER(XMATCH(B2:B23,E2#,0))
G2:G23	G2	=LET(x,B$2:B$23,y,B2,a,UNIQUE(x),b,MMULT(--(TOROW(x)=a),SEQUENCE(ROWS(x),1,1,0)),c,FILTER(a,b=MAX(b)),d,ISNUMBER(XMATCH(y,c,0)),d)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B23	Expression	=LET(x,B$2:B$23,y,B2,a,UNIQUE(x),b,MMULT(--(TOROW(x)=a),SEQUENCE(ROWS(x),1,1,0)),c,FILTER(a,b=MAX(b)),d,ISNUMBER(XMATCH(y,c,0)),d)	text	NO

Yamasaki450 · Wednesday at 4:45 PM

JamesCanale said:
I think this should work in xl2021. You might have trouble with blanks in your thing depending on how you implement it.

I tried this formula but it doesnt work. I get this error in screenshot below. Maybe it works in excel 2021 but im using 2019. Im sorry i forgot to change this in my account settings...

Sam_D_Ben · Wednesday at 5:47 PM

Another approach,

VBA Code:

Sub HighlightMostRepeated()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, c As Long
    Dim dict As Object
    Dim key As Variant
    Dim maxCount As Integer
    Dim cellValue As String
   
    Set ws = ActiveSheet
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    Application.ScreenUpdating = False
    For c = 1 To lastCol
        Set dict = CreateObject("Scripting.Dictionary")
        For r = 1 To lastRow
            cellValue = Trim(ws.Cells(r, c).Value)
            If cellValue <> "" Then
                If dict.exists(cellValue) Then
                    dict(cellValue) = dict(cellValue) + 1
                Else
                    dict.Add cellValue, 1
                End If
            End If
        Next r
        maxCount = 0
        For Each key In dict.keys
            If dict(key) > maxCount Then maxCount = dict(key)
        Next key
        For r = 1 To lastRow
            cellValue = Trim(ws.Cells(r, c).Value)
            If dict.exists(cellValue) Then
                If dict(cellValue) = maxCount Then
                    ws.Cells(r, c).Interior.Color = RGB(255, 0, 0)
                   
                End If
            End If
        Next r
    Next c
    Application.ScreenUpdating = True
End Sub

Yamasaki450 · Wednesday at 7:10 PM

Sam_D_Ben said:

Another approach,

VBA Code:

Sub HighlightMostRepeated()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, c As Long
    Dim dict As Object
    Dim key As Variant
    Dim maxCount As Integer
    Dim cellValue As String
  
    Set ws = ActiveSheet
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    Application.ScreenUpdating = False
    For c = 1 To lastCol
        Set dict = CreateObject("Scripting.Dictionary")
        For r = 1 To lastRow
            cellValue = Trim(ws.Cells(r, c).Value)
            If cellValue <> "" Then
                If dict.exists(cellValue) Then
                    dict(cellValue) = dict(cellValue) + 1
                Else
                    dict.Add cellValue, 1
                End If
            End If
        Next r
        maxCount = 0
        For Each key In dict.keys
            If dict(key) > maxCount Then maxCount = dict(key)
        Next key
        For r = 1 To lastRow
            cellValue = Trim(ws.Cells(r, c).Value)
            If dict.exists(cellValue) Then
                If dict(cellValue) = maxCount Then
                    ws.Cells(r, c).Interior.Color = RGB(255, 0, 0)
                  
                End If
            End If
        Next r
    Next c
    Application.ScreenUpdating = True
End Sub

VBA code runs fine no errors no nothing. But cells with most repeated values are not highlighted in red "RGB(255, 0, 0)" What im doing wrong? Can you check this out...

Here is the link to worksheet im working with. Try it yourself.

Loading Google Sheets

Sam_D_Ben · Wednesday at 7:41 PM

Please try this,

VBA Code:

Sub HighlightMostRepeated()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, c As Long
    Dim dict As Object
    Dim key As Variant
    Dim maxCount As Integer
    Dim cellValue As String
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

    Application.ScreenUpdating = False

    ws.Cells.Interior.ColorIndex = xlNone

    For c = 1 To lastCol
        Set dict = CreateObject("Scripting.Dictionary")
        
        For r = 1 To lastRow
            cellValue = Trim(CStr(ws.Cells(r, c).Value))
            If cellValue <> "" Then
                If dict.exists(cellValue) Then
                    dict(cellValue) = dict(cellValue) + 1
                Else
                    dict.Add cellValue, 1
                End If
            End If
        Next r
        
        
        maxCount = 0
        For Each key In dict.keys
            If dict(key) > maxCount Then maxCount = dict(key)
        Next key
        
        
        For r = 1 To lastRow
            cellValue = Trim(CStr(ws.Cells(r, c).Value))
            If dict.exists(cellValue) Then
                If dict(cellValue) = maxCount Then
                    ws.Cells(r, c).Interior.ColorIndex = 3
                End If
            End If
        Next r
    Next c

    Application.ScreenUpdating = True
    
End Sub

Peter_SSs · Wednesday at 8:05 PM

You could try this Conditional Formatting. Note that nothing would be highlighted if all entries in the column are unique.

25 03 06.xlsm

A

B

C

D

1

2

-1

0

-1

3

0

-1

4

1

0

-1

5

2

1

-1

6

2

0

7

2

0

8

3

4

1

9

3

4

1

10

3

5

2

11

3

5

2

12

3

5

2

13

3

5

2

14

4

6

3

15

5

6

3

16

5

6

3

17

5

6

4

18

5

6

5

19

5

8

5

20

6

8

5

21

7

9

5

22

7

9

6

23

8

10

8

24

8

10

9

25

8

10

9

26

8

10

27

9

10

28

10

11

10

29

10

12

11

30

11

13

11

31

12

13

12

32

12

14

13

33

13

14

13

34

13

15

13

35

14

15

14

36

15

14

37

16

15

38

17

15

39

17

15

40

17

16

41

17

16

42

18

17

43

CF Most

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:AJ42	Expression	=COUNTIF(B$2:B$42,B2)=COUNTIF(B$2:B$42,MODE(B$2:B$42))	text	NO

Yamasaki450 · Wednesday at 9:00 PM

Peter_SSs said:
You could try this Conditional Formatting. Note that nothing would be highlighted if all entries in the column are unique.

25 03 06.xlsm
A B C D
1
2 -1 0 -1
3 0 0 -1
4 1 0 -1
5 2 1 -1
6 2 2 0
7 2 2 0
8 3 4 1
9 3 4 1
10 3 5 2
11 3 5 2
12 3 5 2
13 3 5 2
14 4 6 3
15 5 6 3
16 5 6 3
17 5 6 4
18 5 6 5
19 5 8 5
20 6 8 5
21 7 9 5
22 7 9 6
23 8 10 8
24 8 10 9
25 8 10 9
26 8 10 10
27 9 10 10
28 10 11 10
29 10 12 11
30 11 13 11
31 12 13 12
32 12 14 13
33 13 14 13
34 13 15 13
35 14 15 14
36 15 15 14
37 16 15 15
38 17 15 15
39 17 15 15
40 17 16 16
41 17 16 16
42 18 17 17
43
CF Most
Cells with Conditional Formatting
Cell Condition Cell Format Stop If True
B2:AJ42 Expression =COUNTIF(B$2:B$42,B2)=COUNTIF(B$2:B$42,MODE(B$2:B$42)) text NO

Awesome. This does exactly what i need... Thanks a lot man...

Sam_D_Ben said:

Please try this,

VBA Code:

Sub HighlightMostRepeated()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, c As Long
    Dim dict As Object
    Dim key As Variant
    Dim maxCount As Integer
    Dim cellValue As String
   
    Set ws = ActiveSheet
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

    Application.ScreenUpdating = False

    ws.Cells.Interior.ColorIndex = xlNone

    For c = 1 To lastCol
        Set dict = CreateObject("Scripting.Dictionary")
       
        For r = 1 To lastRow
            cellValue = Trim(CStr(ws.Cells(r, c).Value))
            If cellValue <> "" Then
                If dict.exists(cellValue) Then
                    dict(cellValue) = dict(cellValue) + 1
                Else
                    dict.Add cellValue, 1
                End If
            End If
        Next r
       
       
        maxCount = 0
        For Each key In dict.keys
            If dict(key) > maxCount Then maxCount = dict(key)
        Next key
       
       
        For r = 1 To lastRow
            cellValue = Trim(CStr(ws.Cells(r, c).Value))
            If dict.exists(cellValue) Then
                If dict(cellValue) = maxCount Then
                    ws.Cells(r, c).Interior.ColorIndex = 3
                End If
            End If
        Next r
    Next c

    Application.ScreenUpdating = True
   
End Sub

Still not working. But i doesnt matter i will use formula. Anyway thanks for your effort.

Have a nice day guys...

Peter_SSs · Wednesday at 10:47 PM

Yamasaki450 said:
This does exactly what i need... Thanks a lot man...

You're welcome. Thanks for the follow-up.

Highlight the most repeated value in columns

Yamasaki450

Board Regular

Attachments

JamesCanale

Well-known Member

Yamasaki450

Board Regular

Attachments

Sam_D_Ben

Well-known Member

Yamasaki450

Board Regular

Sam_D_Ben

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Yamasaki450

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Share this page

Highlight the most repeated value in columns

Board Regular

Attachments

Well-known Member

Board Regular

Attachments

Well-known Member

Board Regular

Well-known Member

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock