need help with scripting dictionary

Hi I am fairly new to scripting dictionary and my attempt is below. Can anyone tell me why only 3 rows of data is being displayed and not running for the entire "DATA" sheet ?

From this to this layout example.xlsx
leanne.xlsx
Test data sheet for report.xls

Please Login or Register  to view this content.

Also does anyone know how to show the results a little differently that in cells A:E


Instead of this


Client Name Task Description Processor Due date
Any client 2 Client data received for payroll processing Philip McCrudden 03/10/2014
Any client 2 Draft payroll results submitted to the client Philip McCrudden 03/10/2014
Any client 2 FPS and EPS submitted to HMRC Philip McCrudden 03/10/2014




I need this

Client Name Processor Client data received for payroll processing Draft payroll results submitted to the client FPS and EPS submitted to HMRC
Any client 2 Philip McCrudden 03/10/2014 03/10/2014 03/10/2014

Hello leanne2011,

Would there ever be an occasion where a client would have more than one person assigned as the processor?

Hello leanne2011,

The macro below has been added to the attached workbook. A new sheet named "Report" was added also. Have a look and let me know what you think.

You can run the macro using ALT+F8 keys and clicking on CreateReport.

Create Report Macro

Please Login or Register  to view this content.

Hello Leanne,

Thanks for the feedback and the updated example. I will work on changing the code to match the updated requirements.

Hello Leanne,

This workbook creates a report with a maximum of three tasks and dates. Each line starts with "Client Processor" and a blank line is placed between each entry. This was just a guess on my part.

Have a look and let me know if I am closer to what you want.

Create Report Macro (revised)

Please Login or Register  to view this content.

Hi Leith

Almost - I need specifically "Client data received for payroll processing" in cell C1 and in D1 "Draft payroll results submitted to the client" and in E1 "FPS and EPS submitted to HMRC - no other tasks.

Also - these descriptions only need to be in row 1 - they do not heed repeating. All I want to see in row 2 onwards is the due dates for each task in each column in row 1 - so there will only be one row of data.

It is incredibly hard when its someone elses data but I really appreciate your efforts !
Please see attached the expected layout with a few more lines as I don't think I made it clear before

test script.xls

Thanks
Leanne

Hel;lo Leanne,

At this point, I am more confused than before. Here is sample of the data for "Any Client 1".

There are 3 processor names for this account: Sebastian, Ann, and Cathy. I had asked if there would ever be such a case and you said no.

How did you single out Sebastian and the following dates 6-Oct-2014, 9-Octt-2014 from the data? I don''t see those dates for him. The only date is 17-Oct-2014

The accuracy of my answers depend on the accuracy of your information. Don't be afraid your descriptions and examples will be too wordy or long. I'd rather have too much information than not enough.

test script.xls
Hi Leith


In anonymising the data I have very likely incorrectly named some clients. Only ever will one client have one processor - there will never be more than one. Yes the data for Sebastian is misleading so apologies and that's my mistake. Take the data for Fiona McIntosh - filter the data tab for Anyone place ltd. There are 4 tasks and due dates for each task of which I just need the 3 (exclude task Client approved PwC payroll results)

I just need the 3 task descriptions noted in my earlier response to you and the resultant due dates for each task under the column headers but in fixed columns. You are almost there with the data - I just want one row of data per client and processor showing the due dates for each of the 3 tasks. In yours the fields are shown horizontally but in 3 lines - I need the dates to one line with the task descriptions only shown once on row once and never again. So in Column A1 will be "Client" B1 will be processor and in C1 "Client data received for payroll processing" and in D1 "Draft payroll results submitted to the client" and in E1 "FPS and EPS submitted to HMRC. From row two onwards each client and processor will reflect only due dates under C2,D2,E2 -and repeated until all clients have been listed in this report

does that make any more sense

I attach in the script report again - this time take a look at sheet 1. There are lots of n/a's and its to do with the v lookups go wrong and very likely the naming has gone wrong when I have tried to change the client names. The precise layout is sheet 1 - I hope this helps
Leanne

Hello Leanne,

Okay, I think we have it. I revised the macro to place the due date under the proper heading it was found under. Everything is in a single row with a permanent header in row 1.

Try this out and let me know if there are anymore changes needed.

Create Report Macro (rev 4)

Please Login or Register  to view this content.

Hello Leanne,

You change easily change which sheet the output (Report) goes to. Enter the worksheet's name in the macro marked in blue font. In this example, it has been changed to "Sheet1".

Please Login or Register  to view this content.

Hello Leanne,

Can you show me what this layou would look like? Just a few lines is all I need.

Hello Leanne,

This should do it. The "Completed Date" column has been added to the macro. Have a look and let me know what you think.

Create Report Macro (rev 5)

Please Login or Register  to view this content.

Hello Leanne,

I changed the macro to ladd the completed only to entries with a due date in the "FPS and EPS submitted to HMRC" column.

Please review this change and let me know if it is correct.

Create a list of all other changes that need to be made and verify this list with your co-workers for completeness and accuracy before you post back. This will reduce the number of revisions to the project.

Create Report Macro (rev 5a)'

Please Login or Register  to view this content.