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.

 

[sandy666]

no vba (Power Query doesn't support vba), no formulas, no Power Pivot just Power Query (only)
you can transform the result as you wish because you know what you want to achieve, I don't

 

[inexperiencedOne]

as zip file

s666-PQ-uploadSched.zip

Compressed (zipped) Folder

1drv.ms

It appears you only used PQ to clean the data up to put it into another table?

 

[inexperiencedOne]

How do i mark as answered to have them close this thread?

 

[sandy666]

It appears you only used PQ to clean the data up to put it into another table?

right

How do i mark as answered to have them close this thread?

this is not necessary on MrExcel

 

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

You were absolutely right after seeing it done by someone else i was able to see how PQ fills down each entry after the nulls instead of the filldown feature built into the cells in a sheet just stopping when it encounters a blank or another entry that breaks the chain.