Automatic Data entry manipulation

Copper Contributor

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.

CWsChim_0-1682525837039.png

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...

CWsChim_1-1682526225215.png

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!

 

4 Replies

@CWsChim 

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.

power query.JPG

Thank you for providing the example file. It seems to work well with the example data, however, I have much different production data and have never used Power Query before. It sounds like I need to figure out how to apply power queries to my actual data!

@CWsChim 

Another Power Query option attached, @OliverScheurich shooted 1st though

@CWsChim 

Edited my previous post to upload to more straightforward (and scalable*) approach

 

* Refresh = 2-3sec with a 10k input table