Apr 26 2023 09:26 AM
I have a master sheet of data that my coworkers are using to input data from outreach efforts. It looks similar to the snippet below. This is only an example so I am not sharing confidential information.
As you can see I have a customer ID, and some customer information along with information about each outreach attempt that was made. only 3 outreach attempts are made for any customer. I collect information on who made the outreach call, what the result of the outreach was and the date of the outreach. I am needing to do some reporting on this data, but want it in a different structure. However, this information gets updated daily and I want my restructuring to be dynamic and encompass any new additions or modifications.
Instead of each outreach effort being clustered into their own columns, I am wanting them to be their own row. so instead of having outreach 1, outreach 2, and outreach 3 being their own columns, I am wanting outreach 1 to be row 1, outreach 2 to be row 2, and outreach 3 to be row three all while copying the member ID and member information into each row for each outreach effort. I want my end result to look similar to the snippet below...
Can anyone help me do this in an automated way and all I have to do is refresh the data whenever I want my report to show current information? Is this a VBA scenario?
Thank you to anybody who can help me!
Apr 26 2023 10:56 AM
You can try Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. You can place the green result table to the right of the blue table or in another worksheet.
Apr 26 2023 11:36 AM
Apr 26 2023 12:57 PM - edited Apr 28 2023 07:28 AM
Another Power Query option attached, @OliverScheurich shooted 1st though
Apr 28 2023 01:38 AM - edited Apr 28 2023 07:30 AM
Edited my previous post to upload to more straightforward (and scalable*) approach
* Refresh = 2-3sec with a 10k input table