Microsoft Excel - Filter Cells by Format 5

[kjv1611]

I've come accross a particular need to be able to filter a column in Excel by the format. The specific problem consists of a field that is populated with various types of personal information from various consumers. It for a report showing what employees searched for with a particular application.

The current report only gives us what that person searched for. For instance, Agent Smith might have searched for "123 Main St", whereas Agent Jones might have searched for "123-45-6789", and Agent Miller searched for "555-555-1212" just as some examples as to the actual data in that column.

What I would like to do (and I can import it into Access if that would make it simpler) is to split all of this out into separate Excel Spreadsheets (or tables/queries in Access) based on what type of information is in that field.

I was not sure of a way to do this in Excel with an autofilter (it is possible that I just overlooked it, or am just not aware of the particular method, as of yet). So, I thought I could come up with a VBA method to loop through the cells in the one column, and based on the findings for each column, copy that record to the appropriate spreadsheet.

So, what I would want to eventually have (in whatever is the best format - Excel, Access, SQL, whatever) is probably up to 5 or 6 different files, and each would be for a different search criteria. One would be address, one = SSN, and one = telephone (there are a few others, but I think I can just take care of those rather easily, based on a different field).

I have done some VBA in Excel (haven't done much in the past year or so), but I can't think of what would be the best way to check for specific formatting in the cells. I am sure there is a way, and there may be an altogether better method than I am currently dealing with.

Here is what I've gotten started with so far:

Private Sub FilterSSN()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim x As Long [green]'Long Integer Variable used for counting rows[/green]
    Dim bolLoop As Boolean [green]'True if to continue Looping because there is still a record, or False if no record to check.[/green]
    
    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets(ActiveWorksheet)
    x = 2
    bolLoop = True
    Do While bolLoop = True
        If ws.Cells(x, 1) = vbNullString Then bolLoop = True
        Else
            Select Case ws.Cells(x, 3).Formula
             ~just psuedo-code here...
                Case Format1
                    Copy Row to Sheet1

                Case Format2
                    Copy Row to Sheet2

                Case Format3
                    Copy Row to Sheet3

                Case Format4
                    Copy Row to Sheet4

                Case Else
                    Copy Row to SheetUnknowns 'For manual review
            End Select
        End If
    Loop
End Sub

Of course, the error handling and every detail has not been worked out yet, but I'm trying to get the idea down pat before I worry about the extras.

Any suggestions would be greatly appreciated. Thanks in advance.

 

[PHV]

Have a look at the NumberFormat property and at the difference between the Text and Value properties of the Range object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

kjv,

You're referring to some kind of pattern rather than a FORMAT. FORMAT means something else in Excel -- numbers are formatted as dates or currency etc.

So first you need logic to recognize and ADDRESS versus a SSN. What other kinds of data will be evaluated?

I'd recommend a Category field that will identify the kind of data that was accesses.

Do you REALLY want each of these categories on separate sheets? It could really ALL be on one sheet -- either query the data by category OR return ALL the data and AutoFilter the data by Category. There much less maintenance with ONE sheet than a multitude.

Skip,

_[red][/red]

 

[kjv1611]

PHV,

I'll have a look at those. Thanks for the tips.

-----

Skip,

I'm not 100% sure, just yet. I do think that it would seem easier to have it on one sheet. However, using separate sheets would allow me to be able to import each sheet into SQL Server in order to query against one of the server database tables.

But, I guess, as you stated, I could query it all against the one sheet, based on an extra field category.

Here are the current fields in the Spreadsheet:
Login ID (ID of the company representative related to the specific record)
Activity (This is somewhat of a category as you suggest, but it doesn't go as far in detail as differentiating between SSN/ADDRESS/PHONE)
Search Criteria (This includes the data I need to filter)
Reference Code - I'm not sure what this is, as they are all blank in this field.
Date & Time (when the activity occurred)
Price (price paid for the activity)
And I've added 2 fields which are not relavent to the problem, but rather give me what I want to know in the end:
Name (associated with the SSN or ADDRESS or PHONE or whatever else was used to search)
Notes (Any other relevant info - anything special about the particular record that I find - such as if it seems the same data was searched on more than once)

So, if I were to have sort of a sub-category, or "sub-activity" field created via VBA or some other means (I'm guessing I'll be best sticking to VBA for this), then that would be perfect.

It also appears that all of the telephone listings are in this format:
(555) 555-5555

All of the SSN listings appear to be at least without any letters attached, at least under the "Search" Activity. There may be a couple that do not match that criteria under the other Activities, but that is the largest bulk of them all by far ("Search" Activity).

The addresses seem to be in typical address format for this Activity. There is at least one other format otherwise, and that is just the name and address for other Activities.

 

[SkipVought]

"However, using separate sheets would allow me to be able to import each sheet into SQL Server in order to query against one of the server database tables."

Why export/import? You can query your SQL Server right from this Excel workbook via MS Query -- Data/Get External Data...


Could you post some typical data that you would be analyzing - ie copy several rows and dummy any sensative information.



Skip,

_[red][/red]

 

[kjv1611]

Sure! Be just a minute!

 

[kjv1611]

The data in the worksheet:

LoginID Activity SearchCriteria DateTime
jsmith123 Search (555) 555-5555 Dec 14 2006 11:24AM
bwilson123 Search 123-45-6789 Dec 19 2006 10:54AM
tdavis Search 1 Main St Dec 21 2006 9:28AM
Dallas TX 12345
tdavis Search 555-55-5555 Dec 21 2006 9:45AM
jjohnson Search PO BOX 222 Dec 22 2006 1:42PM
New Yor NY 54321

**The addresses would of course just be in one cell, separated by spaces, not carriage returns, but I split them up in the example just to make it look a little cleaner in the example.**

Then, I would want to return the following data, based on the data entered:
AccountID Name
11111111 Wilson, David
22222222 Jackson, Oreo
33333333 Squarepants, Spongebob
NULL NULL
NULL NULL

Or just "NoMatch" where there is no match for a record. The main purpose, at least initially, for this is to see if there is any data being searched which is not related to a specific account on record.

 

[SkipVought]

How is the source data related to the return data? In other words, using your source example, what would be returned?

And what's the NULL records?

Skip,

_[red][/red]

 

[kjv1611]

Basically, we take the SearchCriteria and attempt to locate a matching AccountID and Name. If there is no AccountID and Name that match, then it would appear that there was no valid reason for the search. This is the main purpose for the reviewing of the data. It is currently being done manually, and I've been asked to attempt to make it at least a little more automated. So, I'm hoping to be able to somehow filter the records in order to query them against a MS SQL Server 2005 database.

So, in the example, the employee with ID jsmith123 looked for the search criteria, "(555) 555-5555", and when we manually queried in the system for that one telephone number, we found that accountID 11111111, and the name, "Wilson, David" matched that telephone number. Therefore, at least at first glance, it was a valid search.

This may not be important (yet), but it would be possible, also to have multiple AccountIDs and even names, or variations of the names. For instance, it could be that one customer has more than one account, or that one customer's name is spelled differently (such as with or without a middle initial).

 

[stevexff]

A user-written function that uses regex matching is far more flexible than trying to parse it yourself. Example:

Function SearchFormat(text As String) As String

    Const phonefmt As String = "(\d{3}\D+){2}\d{4}\D*"
    Const ssnfmt As String = "\d{3}\D+\d{2}\D+\d{4}\D*"
    
    SearchFormat = "ADDRESS"
    
    If regCheck(text, phonefmt) = True Then
        SearchFormat = "PHONE"
        Exit Function
    End If
    
    If regCheck(text, ssnfmt) = True Then
        SearchFormat = "SSN"
        Exit Function
    End If
    
End Function

Private Function regCheck(inText As String, pattern As String) As Boolean

    regCheck = True
    Dim re As VBScript_RegExp_55.RegExp
    Set re = New VBScript_RegExp_55.RegExp
    re.pattern = pattern
    regCheck = re.Test(inText)

End Function

Paste this into a Module under 'ThisWorkbook', and add a reference to the VBScript Regex 5.5 library. Then in another column of your sheet just code

=SearchFormat(A1)

to have it work it out for you. Note that the assumption is that if it's not a phone or an SSN, then it must be an address (no pattern really matches an address as they are too variable...)

After that, it's easy to apply filtering on the resulting values.

HTH

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[SkipVought]

If that's the case, then you ought to have another column in your return -- LoginID, AccountID, Name

That's how they are related.

Skip,

_[red][/red]

 

[kjv1611]

The Login ID will be from the source data (which is a report from a totally unrelated source - 3rd party). So, we will definitely want that data in the end, but it doesn't really have any relation to the particular search item or the AccountID. The LoginID would represent an internal employee, an AccountID woudl represent an account which should have been researched by an employee.

We will definitely want the original data used for this as well as the new data generated.

I'm going to give a shot at the Function as listed by Steve and post back.

Thanks all for your responses!

 

[kjv1611]

stevexff,

I tried just as you suggested. I tried placing the function under the PERSONAL workbook, and then I tried placing it in "this workbook" of the current workbook. I set the reference for each occasion (for whatever reason, I couldn't just set it in one location).

But, when I tried to run the formula, in both cases, I get the "#NAME?" Error, regardless of the text in the cell.

Any thoughts?

 

[stevexff]

Beats me. It worked fine on mine. Did you add it in a module?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[kjv1611]

I first put it into a Module under the PERSONAL macro workbook, and then, since I had the error, I decided to move it over to the ThisWorkbook module (not another one within it, I can try that next...

 

[kjv1611]

Okay, man that is a sensitive function!

I added a separate module into the current workbook, and it worked from there!

Is there a way to make it work from within the PERSONAL workbook? That would be great, so as to not have to recreate the module or export it to every workbook!

 

[stevexff]

Don't know. I never wrote a user function before this afternoon...

Glad to see you've got it working.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[SkipVought]

MsgBox Application.Run("personal.xls!SearchFormat", sString)

Skip,

_[red][/red]

 

[kjv1611]

Well, so it will work within the Personal Macro Workbook! Yay! I saw it work there. So, is there an easy way to make it work as a function, to where I can just enter:
=SEARCHFORMAT(A1) in a cell, and it will work (if the code is in PERSONAL.XLS as apposed to the current workbook? That would be wonderful.

If not, I'll just have to create another procedure, I assume, that would loop through the workbook, running the particular function for each record. That's not a terribly big deal - it's doable.

Thanks for all the help!

 

[SkipVought]

Yes. Insert/Function - User Defined Category -- and then select the function in your PERSONAL.xls workbook

I publish a MODULE to my users that they IMPORT into their PERSONAL.xls. It has a plethora of functions that they use on spreadsheets that return database and screenscrape parameters.

Skip,

_[red][/red]