Need help working with very poorly formatted Report

[inexperiencedOne]

have a very poorly designed report that i am finding near impossible to work with.


Poorly Designed Agent Schedule Report


I need to use this report as i cannot query the server directly due to permission restrictions.


I need to take all the data in this report to create a table that appears as such


Desired Result


I have one macro that i wrote to find all unique agents and the range where their data exists


Agent Ranges


The information is forever changing based on data i need for reporting but the one consistent thing is the columns in which the data resides. all Personally Identifiable Information has been scrubbed from the file.


My thoughts were that i would have to create the unique ranges as i did, then find the unique dates for each agent in the same manner on a new sheet, and then find the unique scheduled activity for each unique date on another sheet. I cant work through the logic properly it seems without creating an insane amount of work.


I am looking for direction and script examples on how to cleanly do this, i am not wanting you to do it for me (unless you have a simple way of doing it that i am completely over looking). Any and all help is appreciated, and i thank you for reviewing this question.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your report. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets.

 

[GraH]

Hi,

I see you have 365 and version 2016 at least. So You'd have PQ on board and then this is rather straightforward.
Try:

1. Open a new workbook.
2. On the data ribbon go for Get Data -> From File -> From Workbook
3. Browse for the file
4. Select the sheet in the navigator
5. then fill down (Columns 3, 4, 5, 6), he equivalent of C:F
6. Filter out "empty" in the column 7
7. Skip first 10 rows
8. Promote headers
9. Column3: double click the header, and rename "Agent"
10. Delete Columns 1/2 (Select, click right: Remove Selected)
11. Close and Load to Excel

You'd be close.

 

[Fluff]

Cross posted How can i work with this poorly formatted report to create a usable table?

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[inexperiencedOne]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your report. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets.

thank you for your input, a link to the file as requested: uploadSched.xlsb

Once the file is viewed i believe it is clear on what the desired result is.

 

[inexperiencedOne]

Hi,

I see you have 365 and version 2016 at least. So You'd have PQ on board and then this is rather straightforward.
Try:

1. Open a new workbook.
2. On the data ribbon go for Get Data -> From File -> From Workbook
3. Browse for the file
4. Select the sheet in the navigator
5. then fill down (Columns 3, 4, 5, 6), he equivalent of C:F
6. Filter out "empty" in the column 7
7. Skip first 10 rows
8. Promote headers
9. Column3: double click the header, and rename "Agent"
10. Delete Columns 1/2 (Select, click right: Remove Selected)
11. Close and Load to Excel

You'd be close.

This would work provided its only one agent, this particular file has 112 agents across 35000 rows all with varying date ranges, i included a link to the file for better understanding.

 

[sandy666]

see this

s666-PQ-uploadSched.xlsb

Microsoft Excel Workbook

1drv.ms

you can add AgentName with custom column

 

[inexperiencedOne]

see this

s666-PQ-uploadSched.xlsb

Microsoft Excel Workbook

1drv.ms

you can add AgentName with custom column

im actually not able to open this, taking me directly to the browser view only with no option to DL the file. "This workbook contains too man objects" and is unsupported for browser view

 

[sandy666]

as zip file

s666-PQ-uploadSched.zip

Compressed (zipped) Folder

1drv.ms

 

[inexperiencedOne]

I got it to open, no vba, just used PQ and PP? Looking through the steps in the query to see what was done.