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:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,304
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

Well-known Member
Joined
Mar 22, 2020
Messages
708
Office Version
  1. 365
  2. 2016
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 7, 2020
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows

ADVERTISEMENT

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

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

inexperiencedOne

New Member
Joined
Aug 7, 2020
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
I got it to open, no vba, just used PQ and PP? Looking through the steps in the query to see what was done.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,518
Messages
5,636,807
Members
416,941
Latest member
shazzaxyz

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