EXAMPLE - How to Auto Assign Projects by Zip Code using a Spreadsheet

This example will go over how you can use a spreadsheet tool, like Excel or Google Sheets, to auto assign projects by zip code.  There are 3 major steps:

1. Export your projects to a spreadsheet
2. Apply the zip code lookup formula
3. Import the updates in to Pruvan

After completing these steps you'll have a simple solution for assigning projects by zip code without having to look up each one individually.  First we need to set up our lookup spreadsheet and formula.

Initial Setup

Here we'll be setting up our Auto Assignment spreadsheet.  It will consist of 2 sheets, one to perform the look up and another to store the zip code / username list.

1. Open a new spreadsheet
2. Go to the 2nd sheet
3. Rename the 2nd sheet 'Zip Index'
4. Title the first column 'Zip Code' (cell A1)
5. Title the second column 'Username' (cell B1)
6. List all of the zip codes you work in column A
7. Match the appropriate usernames in column B
8. Go to the 1st sheet
9. Rename the 1st sheet 'Auto Assign'
10. Title the first column 'Zips' (cell A1)
11. Title the second column 'Users' (cell B1)
12. In cell B2 write this formula:
    • =IFERROR(VLOOKUP(A2,'Zip Index'!A:B,2,FALSE),"")
    • What this formula does is:
      1. Looks at the value in cell A2
      2. Goes to column A of the 'Zip Index' sheet out of the range column A to column B
      3. Matches the value of A2 with the value of every cell in column A
      4. On 1st match, moves over 2 columns (column A is 1, column B is 2)
      5. Returns the value of the B column value that's on the same row as the column A match
      6. False means that it's looking for an exact match
      7. If the lookup does not get any matches in column A then it will return a blank cell instead of an error
13. Copy this formula down column B
14. Test your formula by typing a zip code that's in your index in to cell A2
15. Verify the username in cell B2 is the right user
16. Test your formula by typing a zip code that's NOT in your index in to cell A2
17. Verify that cell B2 is blank
18. Clear cell A2
19. Save your Auto Assign spreadsheet

    

Export Projects

We'll be searching for all unassigned projects in the Project Manager, then copying those to a spreadsheet so that we can apply our formula.

1. Log in to Pruvan Online
2. Click on Projects
3. Search for "unassigned"
4. Click the Status column header twice to verify that there are only unassigned projects listed
5. Click Select All
6. Click Excel (or CSV for another spreadsheet application)
7. Open the file

Lookup Usernames by Zip Code

Now we'll take the list of Zip Codes from our export, paste them in to our Auto Assign Spreadsheet, and then copy the resulting Usernames back to the Assign To column in our export.  Afterwards we'll change the Status column from 'unassigned' to 'assigned'.

1. Open your Auto Assignment spreadsheet
2. Highlight any previous zip code cells in the Zip Code column
3. Delete those zip codes
4. Return to your Exported spreadsheet
5. Highlight all of the Zip field cells
6. Copy the cells
7. Return to your Auto Assignment spreadsheet
8. Select the first blank cell in the Zip column
9. Paste the Zip Code cells
10. Verify that all of the User cells are filled in
    • If not, check the Zip Code for those cells and update your Zip Code index
11. Highlight all of the Username cells
12. Copy the cells
13. Return to your Exported spreadsheet
14. Select the first blank cell in the Assigned To column
15. Paste, values only, the Username cells
16. Change the Status field on all projects to "assigned"
17. Save your updated project spreadsheet

    

Import Updated Projects

Once our exported sheet is updated, we will import it back in to Pruvan and verify the updates were successful.

1. Return to Pruvan Online
2. Click Import / Export
3. Click Choose File
4. Select your updated project spreadsheet
5. Click Open
6. Click Upload
7. Verify your Column matching
8. Click Import
9. Verify your Import
   • The updated count should match the number of projects in your spreadsheet
   • The insert count should be zero
   • There should be no errors listed
10. Close the Import window
11. Click Refresh
12. Verify that there are no more Unassigned projects

    

Conclusion

You should now have a reliable and quick way to mass assign projects by Zip Code.  You can also see from this example how the same logic would apply for completing lookups by other values such as City or State.  When dealing with strings (text) as opposed to numbers, be careful of spelling.  If the spelling or number of spaces is not exactly the same then you will not get a match.  Some programs may even be case sensitive, although I know that MS Excel 2010 is not.  If there's something you'd like to automate with a spreadsheet, but you're not sure how to do it, ask Google.  Google results will generally pull up MS Excel help documentation and spreadsheet forum posts.  Most people have asked questions about how do I do this with this data and you can read the responses to their questions for your own answer.