Batch Macro seems to be dropping West Virginia data

Hey there, I'm importing a series of spreadsheets for each state in the US and using the file names to generate a column indicating their state. So far this has worked for every state except West Virginia. I have tried redownloading the spreadsheets, modifying the formula to just account for "West" in the filename, removed states from the code to account for some sort of "limit" and added Browse tools to see where the WV data gets removed. It appears as though the WV files go into the State Formula tool and do not come out. I've included screenshots of the workflow below and the summarized workflow. I'm not able to share the data, but I'm happy to provide more details.

Directory Tool where spreadsheets are stored

Macro that pulls all the spreadsheets together

Formula to create State column based on filename

Select tool to remove additional information

Join tool to combine the information from both spreadsheets (similar sheets but different information)

Formula to remove the name "County"

Select Statement to rename columns

Upload to database

Batch Macro

Accepted answers

DanielG

@aeberhart - Looks like I was right. Because of the order of the items in the formula. "West Virginia" is being captured by the line for "Virginia" because it contains "Virginia". So if you move the "West Virginia" above the "Virginia" one it will work properly.

All comments

binu_acs

@aeberhart Can you share your formula?

DanielG

@aeberhart - This is a guess because we dont know the formula, but I bet it it because you are catergorizing "Virginia" before it in the formula and since West Virginia contains Virginia it is just mislabeling them so they dont get looked at when the "West Virginia" part of the formula runs.

aeberhart

IF CONTAINS ([FileName], "Alabama") Then "Alabama"
ELSEIF CONTAINS ([FileName],"Alaska") THEN "Alaska"
ELSEIF CONTAINS ([FileName],"Arizona") THEN "Arizona"
ELSEIF CONTAINS ([FileName],"Arkansas") THEN "Arkansas"
ELSEIF CONTAINS ([FileName],"California") THEN "California"
ELSEIF CONTAINS ([FileName],"Colorado") THEN "Colorado"
ELSEIF CONTAINS ([FileName],"Connecticut") THEN "Connecticut"
ELSEIF CONTAINS ([FileName],"Delaware") THEN "Delaware"
ELSEIF CONTAINS ([FileName],"DC") THEN "DC"
ELSEIF CONTAINS ([FileName],"Florida") THEN "Florida"
ELSEIF CONTAINS ([FileName],"Georgia") THEN "Georgia"
ELSEIF CONTAINS ([FileName],"Hawaii") THEN "Hawaii"
ELSEIF CONTAINS ([FileName],"Idaho") THEN "Idaho"
ELSEIF CONTAINS ([FileName],"Illinois") THEN "Illinois"
ELSEIF CONTAINS ([FileName],"Indiana") THEN "Indiana"
ELSEIF CONTAINS ([FileName],"Iowa") THEN "Iowa"
ELSEIF CONTAINS ([FileName],"Kansas") THEN "Kansas"
ELSEIF CONTAINS ([FileName],"Kentucky") THEN "Kentucky"
ELSEIF CONTAINS ([FileName],"Louisiana") THEN "Louisiana"
ELSEIF CONTAINS ([FileName],"Maine") THEN "Maine"
ELSEIF CONTAINS ([FileName],"Maryland") THEN "Maryland"
ELSEIF CONTAINS ([FileName],"Massachusetts") THEN "Massachusetts"
ELSEIF CONTAINS ([FileName],"Michigan") THEN "Michigan"
ELSEIF CONTAINS ([FileName],"Minnesota") THEN "Minnesota"
ELSEIF CONTAINS ([FileName],"Mississippi") THEN "Mississippi"
ELSEIF CONTAINS ([FileName],"Missouri") THEN "Missouri"
ELSEIF CONTAINS ([FileName],"Montana") THEN "Montana"
ELSEIF CONTAINS ([FileName],"Nebraska") THEN "Nebraska"
ELSEIF CONTAINS ([FileName],"Nevada") THEN "Nevada"
ELSEIF CONTAINS ([FileName],"New Hampshire") THEN "New Hampshire"
ELSEIF CONTAINS ([FileName],"New Jersey") THEN "New Jersey"
ELSEIF CONTAINS ([FileName],"New Mexico") THEN "New Mexico"
ELSEIF CONTAINS ([FileName],"New York") THEN "New York"
ELSEIF CONTAINS ([FileName],"North Carolina") THEN "North Carolina"
ELSEIF CONTAINS ([FileName],"North Dakota") THEN "North Dakota"
ELSEIF CONTAINS ([FileName],"Ohio") THEN "Ohio"
ELSEIF CONTAINS ([FileName],"Oklahoma") THEN "Oklahoma"
ELSEIF CONTAINS ([FileName],"Oregon") THEN "Oregon"
ELSEIF CONTAINS ([FileName],"Pennsylvania") THEN "Pennsylvania"
ELSEIF CONTAINS ([FileName],"Rhode Island") THEN "Rhode Island"
ELSEIF CONTAINS ([FileName],"South Carolina") THEN "South Carolina"
ELSEIF CONTAINS ([FileName],"South Dakota") THEN "South Dakota"
ELSEIF CONTAINS ([FileName],"Tennessee") THEN "Tennessee"
ELSEIF CONTAINS ([FileName],"Texas") THEN "Texas"
ELSEIF CONTAINS ([FileName],"Utah") THEN "Utah"
ELSEIF CONTAINS ([FileName],"Vermont") THEN "Vermont"
ELSEIF CONTAINS ([FileName],"Virginia") THEN "Virginia"
ELSEIF CONTAINS ([FileName],"Washington") THEN "Washington"
ELSEIF CONTAINS ([FileName],"West Virginia") THEN "West Virginia"
ELSEIF CONTAINS ([FileName],"Wisconsin") THEN "Wisconsin"
ELSEIF CONTAINS ([FileName],"Wyoming") THEN "Wyoming"

ELSE "No State"
ENDIF

DanielG

This works because it doesnt process rows that have already been processed at a higher level in the IF statement

ELSEIF CONTAINS ([FileName],"West Virginia") THEN "West Virginia"
ELSEIF CONTAINS ([FileName],"Virginia") THEN "Virginia"

aeberhart

@DanielG thank you! That worked

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703