Freq distribution problem

Hi Excel Forum,
I have a big Excel 2010 data set (a small fictious portion attached).

Question:

I need to find out how many different FSAs are there in this dataset.

I know in SAS we can use proc freq but in Excel how could we find it?

Thank you for the help.

Mirisage

Hi Mirisage,

Are all the FSA codes in column B or separated over multiple columns?

Hi English_Bloke82,

Actually they are separated over multiple columns.

Thanks

Mirisage

Sorry for my ignorance, but I'm not even sure what an FSA code is. Some type of a postal code, in Canada I think, but I'm probably mistaken.

Assuming an FSA code is always three digits in length, and assuming the middle digit is always numeric and the first and third digits are alphanumeric, (as your sample worksheet seems to indicate) then this macro will tell you how many different FSA codes are in your worksheet.

Change the max number of rows and the max number of columns as needed.

It seems to work for all of the test data I gave it. Your small worksheet, for example, contained five different FSA codes. (B7U is written twice.)

If I'm completely wrong about what an FSA code is, my apologies. But maybe you can still use and modify the macro to fit your needs.

Application.ScreenUpdating = False

Dim fsa_code(1 To 5000) As String   ' <--- change max size as needed.  Assumes you won't have more than 5000

For Row = 2 To 500         ' <--- change max number of rows as needed
  For Column = 1 To 50     ' <--- change max number of columns as needed
    Length = Len(Trim(Cells(Row, Column)))
    If Length = 3 Then
        If IsNumeric(Mid(Cells(Row, Column), 2, 1)) Then
           If Not IsNumeric(Mid(Cells(Row, Column), 1, 1)) And Not IsNumeric(Mid(Cells(Row, Column), 3, 1)) Then
              x = 1: found_it = 0
              Do Until fsa_code$(x) = ""
                 If Cells(Row, Column) = fsa_code$(x) Then
                     found_it = 1: Exit Do
                 End If
                 x = x + 1
              Loop
              If found_it = 0 Then
                 number_of_different_fsa_codes = number_of_different_fsa_codes + 1
                 fsa_code$(number_of_different_fsa_codes) = Cells(Row, Column)
              End If
           End If
        End If
     End If
  Next Column
Next Row

Application.ScreenUpdating = True
Message$ = "There are " & number_of_different_fsa_codes & " different FSA codes in this worksheet."
MsgBox Message$

Hi Ed_Collins,

Thank you very much for taking time to write this macro.

I should take time and try to learn the logic behind this macro.

Thanks again!

Mirisage