Solved

Need VBA to verify state codes and identify outliers

Posted on 2007-03-22
8
343 Views
Last Modified: 2013-11-25
I need a macro to look at the active cells in column L  and convert the names of the states (and US protectorates) to their respective postal abbreviations and then identify any cells which do not contain a valid state abbreviation by appending a zz to the beginning and doing a sort on the used range of the worksheet.
The code I've got so far selects the range and Replaces the state names with their abbreviations one at a time.  I'm not sure how to check each cell for the valid values of AL, AK, AR etc. and if not valid append the zz.  I'm pretty sure I can figure out how to sort the whole worksheet, but if you know off hand, that'd great.
My current code looks like this:

    Range("l2", "l" & ActiveSheet.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row).Select
    With Selection
        .Replace What:="Alabama", Replacement:="AL", LookAt:=xlPart
        .Replace What:="Alaska", Replacement:="AK", LookAt:=xlPart
        .Replace What:="American Samoa", Replacement:="AS", LookAt:=xlPart
         { you get the idea}
        .Replace What:="Wisconsin", Replacement:="WI", LookAt:=xlPart
        .Replace What:="Wyoming", Replacement:="WY", LookAt:=xlPart
    End With

I guess the remainder could be done with 50-ish nested if's, but I'm not sure you can nest that many levels and there must be a better way.  Maybe a case statement?  Could there be something more eloquent like building an array or a recordset or something?
Please bear in mind that Excel can have as many as 65,536 rows and that it's not uncommon for me to have 40,000+ entries.
0
Comment
Question by:Rossamino
  • 4
  • 4
8 Comments
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 18774760
The better way would be to
1. Store all the state names like, Alabama, Alaska, etc in A column of separate sheet named "States"
2. If you have your states in L column, put below formula in M1 cell and copy this cell in all adjacent cells as in L column
=IF(ISERROR(VLOOKUP(L1,States!A:A,1,FALSE)),"Not Found","Found")
3. Select "Data -> Filter -> Auto Filter" and select "Not Found" to filter out.
0
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 18780344
If your intention is not to just have "Found" / "Not Found" and if you also want to translate each state to two letter abbreviation, use below
1. Store all the state names like, Alabama, Alaska, etc in A column of separate sheet named "States". Store corresponding abbreviation like AL, AK, etc in column B.
2. If you have your states in L column, put below formula in M1 cell and copy this cell in all adjacent cells as in L column
=IF(ISERROR(VLOOKUP(L1,States!A:B,2,FALSE)),"Not Found",VLOOKUP(L1,States!A:B,2,FALSE))
3. Select "Data -> Filter -> Auto Filter" and select "Not Found" to filter out.
0
 

Author Comment

by:Rossamino
ID: 18780592
Is there any way to store the vlookup range data in the VBA code instead of in a separate worksheet?
0
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 18780813
Vlookup data should be either stored in same sheet or another sheet. It cannot be read from VBA code. When you have intention to have 40000+ rows, your code will be slower to run if you replace each state.
If having separate sheet is problem you can either choose to have State look up date in same sheet or "Hidden" sheet.  (Sheet can be hidden by menu Format -> Sheet -> Hide)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Rossamino
ID: 18780874
Here's what I've come up with:

=if(iserror(vlookup(L1,States!B$1:B$59,1,0)), if(iserror(vlookup(L1,States!A$1:B$59,2,0)), concatenate("zz" & L1), vlookup(L1, States!A$1:B$59,2,0)), vlookp(L1, States!B$1:B$59,1,0))

If the cell already contains the postal abbreviation use that, else if the cell contains the name of the state use that, else append zz for later sort.

Hopefully performance will not be an issue
0
 

Author Comment

by:Rossamino
ID: 18781071
so if the States spreadsheet isn't already open, I can just append the path?  Instead of:
=if(iserror(vlookup(L1,States!B$1:B$59,1,0)), etc . . . I would use
=if(iserror(vlookup(L1,c:\States!B$1:B$59,1,0)), etc . . . if States was in C:\ ?
0
 
LVL 7

Accepted Solution

by:
Jignesh Thar earned 500 total points
ID: 18781215
So if you were to keep States.xls excel file having "States" worksheet with state data then formula that would work well is -> Let me know if this works for you

=IF(ISERROR(VLOOKUP(L1,'C:\[States.xls]States'!B$1:B$59,1,0)), IF(ISERROR(VLOOKUP(L1,'C:\[States.xls]States'!A$1:B$59,2,0)), CONCATENATE("zz" & L1), VLOOKUP(L1, 'C:\[States.xls]States'!$A$1:$B$59,2,0)), vlookp(L1, 'C:\[States.xls]States'!B$1:B$59,1,0))
0
 

Author Comment

by:Rossamino
ID: 18781616
The complete and final code edited slightly to account for the header row that works like a charm:
    Columns("M").Insert
    Range("M1").Value = "State"
    Range("M2").Value = "=if(iserror(vlookup(L2,'C:\[States.xls]States'!B$1:B$59,1,0)), if(iserror(vlookup(L2,'C:\[States.xls]States'!A$1:B$59,2,0)), concatenate(""zz"" & L2), vlookup(L2,'C:\[States.xls]States'!A$1:B$59,2,0)), vlookup(L2,'C:\[States.xls]States'!B$1:B$59,1,0))"
    Range("M2", "M" & ActiveSheet.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row).Select
    Selection.FillDown
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("L:L").Select
    Selection.Delete Shift:=xlToLeft
    Range("l2", "l" & ActiveSheet.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row).Select
    Selection.Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now