I am unsure how to go about a problem I am having with a fairly large data set. I have posted a simplified version in the linked picture below, but will try to explain it as best I can in words.
I have data that needs to be converted into an output of weighted data, based on multiple criteria (Area and State). However, I have multiple state sections that could be applied to each area. This is why I have a master crosswalk of the states and area that fit the criteria. Here are some steps I believe may help it explain it:
(1) Check if State_4 is blank/null
-If null, proceed to step (2)
-If filled, check State_4 cell to state in crosswalk
---If state matches, check if area in State_4 row (column B) matches an area in the crosswalk that is in the state
------If state and area match what is in the crosswalk, add the Data that is in the same row (column G) to the weighted average calculation and move to (2)
------If state and area do not match, proceed to next row and start over at state_4
(2) Check if State_3 is blank/null
...Repeat steps in (1), just this time for State_3 until you get to State_1, then proceed to next row and back at State_4
I honestly have no idea how to go about this since I have tried large formulas for index matching in Excel, matching criteria with different packages in r, and if-else statements in r as well.
Anyone have any suggestions how I should go about this?
The red filled cells in the output column represent State/Area combinations that are not in the crosswalk, and therefore do not exist
Aucun commentaire:
Enregistrer un commentaire