Rename field as field header and then split the data into groups

Question

Hi,

This seems simple but I can't get my head around how to do it as there seems to be too many options, none of which seem as simple as I think they should be - any solution will be greatly appreciated and received!

I am trying to split the data table below into groups according to the columns which have a "Y" in their rows.

INPUT       Activity NameAMB&PBDCourse 1YY Course 2YY Course 3Y  Course 4Y YCourse 5Y Y                    DESIRED OUTPUT - 3 separate data streams/tables, as follows       CapabilityActivity Name  AMCourse 1  AMCourse 2  AMCourse 3  AMCourse 4  AMCourse 5      CapabilityActivity Name  B&PCourse 1  B&PCourse 2          CapabilityActivity Name  BDCourse 4  BDCourse 5

afv2688 · Accepted Answer

Hello @Andy_W ,

Would this workf for you?

Regards