Transpose Multiple Team Data

[ccowman]

Hi everyone,

I am working with a data set that is giving me trouble with what I'm trying to accomplish and am looking for any guidance on how best to attack this. This data is received from a third party and they are unwilling to change the data structure, so I'm stuck with the data as is.

At a high-level, I have a requirement for multiple teams, but I'd like to track the requirement separately by team. Ex., Requirement #2 is the same for the Electricians and Landscapers, but the Electricians are done, but the Landscapers are not. So I would like to see separately, hopefully with minimal manual intervention, what tasks are not completed.

I have attached an example of what I'm trying to do in multiple steps. On the first tab is the RawData. The second tab, 'TransposedData', shows how the same Requirement interacts with multiple teams and how I need to report out based on each team. And the last tab is how I'd like to see the data finally, 'FinalData'. This last tab shows only the Requirements and the Team that has not completed their Requirement yet.

I'm open to other methods of seeing this final data, but would like not to have to click on various different filters and pivot charts to see the data, but would like it pulled in one central location. Any help or guidance would be greatly appreciated. Thanks!

ReqTestMatrix.xlsx

[arlu1201]

Is there any reason why you need the transposed data? What is the difference between sheet1 and sheet3?

[ccowman]

No, there is no reason that the TransposedData is needed, just wanted to show that I'm needing to essentially track each Requirement separately for each external team. The difference between sheet1 (RawData) and sheet3 (FinalData) is that in FinalData, I'd like to show only the Requirements that have yet to be completed. There may be an easier way and I'm just over-thinking things...

[arlu1201]

How are you arriving at Sheet 3 from Sheet 1? Which columns are you using to evaluate and arrive at the outcome in Sheet 3?

[ccowman]

I'm using colJ:N from sheet1 to arrive at sheet3. Basically, if colJ:N has an "N", then I'd like to see it on a line. I'm just a little lost whenever there may be multiple "N"'s on the same line how to see those separately. Thanks!

[arlu1201]

Why do you have multiple entries for requirements ids 7,8 and 9?

[ccowman]

Hi @arlu - it's because they have multiple "N"'s for the Requirement and the appropriate External Team. So I would like to see that for Requirement 7, the Electricians and Plumbers are not done. For Requirement 8, it's not completed because the Contractors and the Painters are not done. For Requirement 9, the Electricians and the Landscapers are not done. Hopefully, I'd be able to see these on separate lines like is shown in FinalData.

[arlu1201]

I didnt expect the code to be this easy, though it took some time to work it out -

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[ccowman]

Wow! Thanks, Arlette! That works perfectly! I'll mark this as complete, but have an "extra feature" that I wanted to see if it would be possible to work with your code on...

Would it be possible to add a button / dropdown to select which "Internal Team" (colC)? If so, how would that work?

[arlu1201]

Where do you want to put the dropdown?

[ccowman]

Potentially on the FinalData sheet? I'm just thinking in my head that if you select the drop-down for Internal Team = A, click a button to run the macro and will pull in only the Team A non-completed Requirements...Also, would like to have "All" in addition to all the Internal Teams. Thanks so much!

[arlu1201]

That will need a different code and this one will not work.

Also, its better if you create a new thread for it, since the request is different from this thread.

[ccowman]

Will do, thanks again, Arlette!

[ccowman]

Hi again, Arlette - I tried to translate this test spreadsheet into my actual sheet and am having some issues. It seems as though the macro isn't running at all...Would you be able to explain what some of the code does so that I can try to determine the issue? Specifically, I'm not sure what the "j = 10 To 14" is...

Not sure if it helps, but my original data tab is "Requirements", my final is "IncompleteRequirements". The data ranges from cells B:AT and the external team portion is from W:AT.

[arlu1201]

It would have been good if your sample file matched the original file with respect to the format, so you would not have to do double work.

Please Login or Register  to view this content.

In your sample file you wanted only columns J to N to be checked so that was the 10 to 14. Please change the above code to reflect it as per your file.

[ccowman]

Completely agree! I'm new to the forums, so need to think through that stuff ahead of time...Just got everything working and it's much appreciated for all the follow-up!

[arlu1201]

Am glad its working. Thanks for the rep.