Ah, wait. I'm mistaken again. It's not the entries within those columns which is a restriction on a formula solution, but the number of columns being queried.
I see that you have only 14 columns, so we can just do it.
Try this array formula** and get back to me. Otherwise we'll try the VBA!
=SUBSTITUTE(TEXT(SUM(10^(ROW(INDIRECT("1:"&COUNT(1/FREQUENCY(D7:Q7,D7:Q7))))-1)*COUNTIF(D7:Q7,LARGE(IF(FREQUENCY(D7:Q7,D7:Q7),TRANSPOSE(D7:Q7)),ROW(INDIRECT("1:"&COUNT(1/FREQUENCY(D7:Q7,D7:Q7))))))),REPT("# | ",COUNT(1/FREQUENCY(D7:Q7,D7:Q7))))," | ","",COUNT(1/FREQUENCY(D7:Q7,D7:Q7)))
Regards
Advanced Excel Techniques: http://excelxor.com/
Advanced Excel Techniques: http://excelxor.com/
Hi, motilulla
Try this:
Code:Sub a1082382a() 'https://www.mrexcel.com/forum/excel-questions/1082382-count-all-values-each-row-smaller-larger.htmlDim vso As Object Dim va, vb Dim i As Long, j As Long, k As Long, s As Long Dim d As Object, vso As Object, x As Variant va = Range("D7", Cells(Rows.count, "Q").End(xlUp)) ReDim vb(1 To UBound(va, 1), 1 To 1) For j = 1 To UBound(va, 1) Set vso = CreateObject("System.Collections.Sortedlist") Set d = CreateObject("scripting.dictionary") For k = 1 To UBound(va, 2) s = va(j, k) If Not d.Exists(s) Then d(s) = 1 Else d(s) = d(s) + 1 End If Next For Each x In d vso.Add x, d.Item(x) Next For i = 0 To vso.count - 1 vb(j, 1) = vb(j, 1) & "|" & vso.GetByIndex(i) Next vb(j, 1) = Right(vb(j, 1), Len(vb(j, 1)) - 1) Next Range("S7").Resize(UBound(vb, 1), 1) = vb End Sub
Hello Akuini, I am wondering this VBA were working perfect till last month, tried today it stop at the line belowand giving an error Run- time error'-2146232576 (80131700)': Automation error I goggled to find the solution but do not get the answerCode:Set vso = CreateObject("System.Collections.Sortedlist")
Please need help do you have any idea what is wrong? I am running excel 2000
Kind Regards,
Moti
Like this thread? Share it with others