Data organization for multiple vendors

[Anonymous]

I am trying to figure out how to organize by excel data based off of states and what general contarctors work in those states.  I eventually want to create a map visual where I click on a state and the general contractors will be shown.  

[Anonymous]

I would start with two tables.

1, all the states with a column of the state name, a column of the state short code.

2, all the contractors with a column of their name/company name, their state short code, address, any other details specific to them.

I connected those tables in relationship view where StateShort and ContractorState (also short) are matched up.

Then added a map visual with StateShort, as well as some Matrixes to list their names. You can see each state with Contractors has a dot on the map. When I click New York, the tables filter to New York and show only those contractors.

Just a rough idea how to begin. Hope this helps.

[Anonymous]

Thank you for your quick reply.  For each contyractor, I ahve multiple states.  How do I account for that on the excel sheet?

[Anonymous]

For the sake of keeping it organized and easier to manage in the future (and allow one-to-many relationships to work), I would do a table that is just a unique list of contractors (like the one above). Then make another table (third one now) that is something like "ServiceAreas" which will have one contractor & state per row, and it is ok to have multiple rows of the same contractor, one for each state they service.