Need help working with very poorly formatted Report

inexperiencedOne

New Member
Joined
Aug 7, 2020
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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.
 
Last edited by a moderator:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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 ;)
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

inexperiencedOne

New Member
Joined
Aug 7, 2020
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,910
Messages
5,621,593
Members
415,846
Latest member
mauryanil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top