Is this process doable or am I in clock cuckoo land?

[TheRedCardinal]

I run a team that inherited hugely manual processes from my predecessor. I want to automate these as much as possible.

I want to start by removing the manual steps in our Intrastat process. For those outside the EU, this is a statistical reporting requirement in the EU of goods moved between businesses.

I initially wanted to explain the current process but it is not helpful. So I have linked to the file I created this morning on how I would like this process to work.

Link to my file in Google Drive

There are a lot of individual challenges inside the workbook, and I'm having trouble conceptualising how to do some of the steps.

In particular I am unsure how to work with the Pivot tables and extract the data I need for the subsequent steps.

Also getting the process right for the removing duplicates is causing me some difficulties.

So at a nebulous level, is there any reason why I can't achieve the things I've set out in the attached workbook?

Thanks for all the help that may come my way!

 

[mole999]

you could add the steps you want in here as you have on line, and the file would serve as the visual, if each was number then people could comment on what might not be doable

 

[TheRedCardinal]

Thanks!

My main issue at the moment is working with the pivot tables. The tests that are on 2. Pivot Data are the ones I can't even start to work on. Also, extracting the data in the Final Pivot into the final CSV tab. I am not comfortable enough with pivot tables to fully understand it yet.

 

[mole999]

a pivot for showing data isn't actually that bad, it produces a table that comes from you data in grouped values. I get lost when I know I can add sums and totals in a structured way so I don't use it like that from my needs. You can't just refer to a cell for future data as you don't know where that cell (row/column) might be after you have added data. If its important to have that automatically then i use supplemental index/match or vlookup to do that, in which case it may have already been in the original data