How to decrypt multiple columns?

How do I count all possible values in multiple columns in Excel 2010?

• How do I count all possible values in multiple columns in Excel 2010? I have a huge spreadsheet with thousands of rows and columns. I need to be able to count how many times certain values show up in the spreadsheet (both row and columns), but there are literally tens of thousands of different values, and I need the calculations to appear in Column J. How do I do this? Macro perhaps? Please advise. Note: Columns are from A to F. In other words, I need to know there are, say, Column A x values of "A," y values of "B" z values of "C" Column B x values of "A," y values of "B" z values of "C" and so on, except there are tens of thousands of different values for different rows and columns. So the calculations can appear anywhere after Column F. I got this off another user, garbo7441, who did an excellent job in coding something similar for a single column A. However, I just need to include multiple columns (A to F). code: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim i, LastRowA, LastRowF LastRowA = Range("A" & Rows.Count).End(xlUp).Row Application.ScreenUpdating = False Application.EnableEvents = False Columns("F:G").ClearContents For i = 1 To LastRowA If Application.CountIf(Range("F:F"), Cells(i, "A")) = 0 Then Cells(i, "F").Offset(1, 0).Value = Cells(i, "A").Value End If Next Columns("F:F").Select Selection.Sort Key1:=Range("F1"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal LastRowF = Range("F" & Rows.Count).End(xlUp).Row For i = 2 To LastRowF Cells(i, "G").Value = Application.CountIf(Range("A:A"), Cells(i, "F")) Next i Range("F1").Value = "Value" Range("G1").Value = "Occurrences" Range("F1:G1").HorizontalAlignment = xlCenter Range("F1").Select Columns("F:G").AutoFit Application.EnableEvents = True End Sub Please help!

• Answer:

  Edit: Okay, Sam, I have created an event handler that does EXACTLY what you wish to do. However, Y/A has objected to the number of characters entered in my answer as 'too long'. Email me at [email protected] and I will send it to you. Aside to the person applying the 'Thumbs Down'.... what exactly about a response offering to assist someone engenders a negative response?