April 29, 2021
I'm looking for assistance. I've been working on trying to figure something out for a while and can't seem to get what I'm looking for. I have a big document that contains all the financials of the project that organization is working on. On this report, I have the cost centers along with the WBS for each of these project. The part that I'm trying to automate is this. I want excel to give me in another worksheet the full list of all the cost centers along with each WBS that that cost centers has, having a different row for each. I'm waiting to stay away from VBA but if there's a solution in Power query, that would be great. I was hoping a formula can do it for me though as others in the office don't know how to use power query.
Here's an example.
In one document, I have something like this with these heading. (these are all in different columns)
Project WBS Cost Centers
Project A C-00001 181000000
Project A C-00005 181000000
Project A C-00128 182200000
Project B C-00007 182200000
Project C C-00005 181000000
Project C C-00007 182200000
I want this to populate for me another worksheet where is would give me one line per unique entries with the WBS and Cost centers.
Like this (in different columns)
Once it does that I could get it to sum everything up and look up the values I need. I'm just wondering if it could do this for me automatically and that upon refreshing the document, it would refresh the output it gives me. This is just a sample above. It's a big document.
I'm sure there's a way and that's I'm just overthinking it, or have looked at it for too long!
Thanks in advance guys!