Creating Column Headers & Grouping based on matches

Question

Okay, so attached is a file.The tab that is called "Methods" hold the columns I want to have.

The other tab is an example of the data. So, each state files their income in a specific way. What I want to do is put each state and each line item in one column based on the method of filing. By doing this, I will be able to accomplish, for example - a consolidated state, I could sum all AL and come up with the number I want - I will need to create a different formula for each column. Unitary filing will not be summed, each number needs to stay separate. This is way over my head, so I REALLY need assistance figuring this out. Also, if you have a better idea of how to accomplish this task, let me know. Open to suggestions.

Thanks all!

Example.xlsx

NicoleJohnson · Accepted Answer

I'm not sure I completely followed the scenario, but I think this might help get you started in the right direction. See attached example... The key to this one is the Cross-Tab tool - by using the Filing Method in your Data Example tab as the header field in the Cross-Tab configuration, and grouping by the other fields you want to keep, your data will show the filling methods as headers and then list the state data & values below. You actually won't even need to bring in the second tab that listed all the filing methods! You can then use a Select tool to choose each filing method, filter out null values, and then perform your calculations. Repeat for each filing method, and you should have the flexibility you need to report your values in a variety of formats & methods!

Hope that helps...

Cheers,

NJ