deleting identical numbers

[excelNewbie22]

first thing first, is it ok to ask several similar questions in one thread or should i split them?

so my question is similar to my last one (in other thread from today)
i want to delete all rows containing 3 identical numbers, in any order
example as follow

example.xlsx
	A	B	C	D
1	5	1	1	1
2	1	1	1	8
3	4	9	4	4
4	1	2	1	2
5	1	2	4	8
6	1	3	5	1
7	1	2	4	9
8	8	2	4	6
9	2	2	4	2
22

 

[JoeMo]

Will there always be only 4 numbers in any row?

 

[Rick Rothstein]

I think this macro should work...

Sub DeleteRepeatThreeOrMoreInRow()
  Dim R As Long, Rng As Range
  Set Rng = Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row)
  For R = 1 To Rng.Rows.Count
    If Evaluate(Replace("COUNTIF(@,MODE(@))>2", "@", Intersect(Rng, Rows(R)).Address)) Then
      Cells(R, "A").Value = "#N/A"
    End If
  Next
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub

 

[JGordon11]

In Power Query:

let
    gte3 = (lst as list) as logical => List.Count(lst) - List.Count(List.RemoveMatchingItems(lst,{List.Mode(lst)})) >2,
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = Table.ToRows(Source),
    lst1  = List.Accumulate({0..List.Count(lst)-1}, {}, (s,c)=> if gte3(lst{c}) then s else s & {lst{c}}),
    Result = Table.FromRows(lst1, Table.ColumnNames(Source))
in
    Result

Book2
	A	B	C	D	E	F	G	H	I
1	Column1	Column2	Column3	Column4		Column1	Column2	Column3	Column4
2	5	1	1	1		1	2	1	2
3	1	1	1	8		1	2	4	8
4	4	9	4	4		1	3	5	1
5	1	2	1	2		1	2	4	9
6	1	2	4	8		8	2	4	6
7	1	3	5	1
8	1	2	4	9
9	8	2	4	6
10	2	2	4	2
Sheet2

 

[excelNewbie22]

for joe: yes
for rick: Excel-lent!
for gordon: don't know what is power query