Consolidation and duplication of data help

Hi, this is my first post so thanks in advance for your help/guidance.

I am currently working on a operational requirements spreadsheet and want to streamline the information that is inputted into one sheet but have it duplicate automatically and remove any duplicate entries onto another sheet within the relevant sections where it should sit. Is this something excel can achieve and if so how would I go about doing it? The spreadsheet i'm using is held on the CPNI website and search operational requirements if you want to see it, as I can't hyperlink or anything as new.

Many thanks,

Many thanks for your reply and advice on attaching. Hopefully I have attached the example workbook with what I am trying to do.


What I want to achieve is for the contents of the columns (heading beyond the perimeter, perimeter etc) within sheet 3 to be automatically consolidate and duplicates removed into the relevant section on sheet 2 and filtered into the highest 1st as shown.

If this isn't possible what would you suggest I can do to make it quicker.

I also want to use the information in a dash board, but how could this be achieved as it isn't a graph or table?

Thanks

For this example lets look at it as a criminal looking to break into an office building to steal laptops.

The 3rd sheet (recommendations template) is used firstly to identify the security recommendations that would mitigate/reduce the threat through the different areas (beyond the perimeter, Perimeter, within the site). E.g. CCTV, alarms, guards. This is further broken down into the different principles Detect, Deter etc. There isn’t a defined list of security recommendations, however I had it that I had a data validation list on a separate sheet which was easier for selecting the items.

The recommendation is then scored against the resources, organisation readiness and stakeholder Engagement. These are score between 1-3 (1=none, 2=Limited, 3=Readily/Good). These scores are added together to get the RAG score max 9 points, so the colours come into play to make it easier for the review to see what recommendations stand out more than the others and where they sit in someone’s risk appetite.

Once you have your recommendations across the areas, you don’t have to have something in every area. These then need to be consolidated and transferred to sheet 2 (OR Template) removing any duplicate entries, so it’s easier reading for stakeholders/managers to make a risk based decision on the outcome and what recommendations to take forward.

Hopefully you better understand how it works now.

Hi,

I have already managed to set up a drop down list using data validation that is held on a separate sheet (not included in this workbook) is this the correct way of doing things?

E:G fills would be helpful if it is replicated as well.

I think you are slightly confused with how the form is completed, so will go through it step by step to make it less confusing for us both. 😊
• We start completing the Rec Temp sheet first. Each column C, H, M etc cell is linked to a master dropdown list of recommendations which is pre-completed by the user on another sheet as the recommendation could be different depending on the project the form is being used for. (have attached updated sheet to show)
• User selects for example C9 (Deter) and uses the drop down to select the recommendation. Once the recommendation has been chosen they then self-complete cells D,E,F which auto fills cell G. Then move onto the cell e.g C10, C11.
• Once all the recommendations and D,E,F,G cells are completed for ‘beyond the perimeter’ I want them to auto consolidate and be placed onto the OR Temp sheet under the security recommendations for Beyond the perimeter, removing any duplicate recommendations that have transferred across in the other principles (Deter, Detect etc) under the column title.

Finally, you may have recommendations that appear in different columns (building, perimeter) with different scoring but this shouldn’t affect the overall display on the OR template, as they would be in different areas as it might not be as effective in one area then another.

Hope this helps.

You are correct that the recommendation template is the source data and I want it to be filled out on the OR template.

In regards to the question around points there shouldn't be an occurrence where the score is difference under a single column. Yes they maybe be scored differently across different columns due to areas but that wouldn't affect the scoring on the OR template as they would be in different groups. That said I would take the highest score forward if there were to be a duplicate under one section.

Thanks for your help, top job.

I have been playing with it this morning to see what it can do and appreciate the assistance. I do have a few questions though.

- How can I get the risks column/cells to stay populated with the data that I input into it? Every time I fill them in from the drop-down list I have assigned to it they disappear once I hit 'Make Template'. Each cell may have different risks associated to so this option has to be available.
- The title 'OR TEMPLATE: site area.... always reverts back to cell A1 if I merge the cells and centralise it.
- Is there also a way for the data to be auto displayed in order per area. e.g Everything from beyond the perimeter is scored by the RAG score with the largest being first. As this reverts back to the collated list when manually filtered.
- Could you just check the Conditional formatting I have added to the OR Template in columns D,E,F,G has it doesn't always seem to come across when the template is made.

I have added the updated sheet to this email.

Many thanks

- Risks are being added within column B 'OR TEMPLATE' as a drop down list that is data linked to column D 'Recommendations'

- This is cell A1 'OR Template' that I want the title to show across the top. Not heard of 'Center Across Selection' before

- Yes, sort by top score per area eg. Beyond the perimeter shows the RAG scores as 3,5,9,6,4 it places them as 9,6,5,4,3 through the column. Then for perimeter etc. It can be done via custom field on each selection but just wondered if could be automated.

Many thanks

Hi Dflak,

Thank you so much with all you help so far with the consolidation spreadsheet. There is no way that I would have been able to achieve what you have done here.

Just wondered if you had managed to take a final look at the conditional formatting piece at all?

Also don't worry about the risk piece as my ideas for this column has changed now as I have added some colour conditioning formatting so it is linked back to the final Recommendations sheet. What would be helpful is that the cells don't delete the contents when you click make template as you have to put it back in again every time if you have made a slight change.

I have attached the latest draft of the doc so you can see it with what i'm talking about.

Thanks for checking this for me.

How can I get column B 'OR Template' not to be included on making the template up, as if I populate these cells before pressing 'make template' button it clears the cell .

Thanks for sorting Columns B out. I have just managed to see where you made the change and have been able to keep Column H stay as well. I’m starting to understand this programming lark.
On the OR Template

- Column G retains its formula from the ‘Recommendations Template’ so I’m unable to enforce click box in cell H1 conditioning to be able to switch on/off the text colour. Does Column G need to come across if a separate conditional formatting is implemented at this stage? The attached is currently saved in the situation I’d like it to be in.
- How do I get Column G to filter each score in size order (largest to smallest) per area for easier reviewing?
- Looking to have the dropdown list (column H) appear with each row and a check box appear in column J (as shown) if a recommendation is brought forward from the recommendation template. Can this be achieved for every row that is brought forward?
- Finally if the check box is to be ‘True’ (recommendation adopted) can the relevant cells from columns A,B,C and I be displayed on the ‘Dashboard’ sheet for an executive summary?

Appreciate your time and effort.

Thanks again.

I have attached the mock up of the Dashboard with how and what recommendations are adopted, then brought forward to only show the true values.

There is a slight debug that I don't seem to be able to fix within the script when you hit make table from the last revision, which is still embedded in this verison. Run-time error '1004': Unable to set the Text property of the Characters class. I think this is to do with the checkboxes as it highlights the following text Selection.Characters.Text = "" within the section.

Stop on Dflak with the bring of the recommendations forward. I tried to add a few more examples into the recommendations template and when i click make template there is a debug that prevent it from creating the table and when VBA opens it highlights the following text. Selection.Characters.Text = "" What do I need to do to correct this?

Thanks for all your help and time on this so far.

I have been developing the sheets further and have a few questions to ask if that is ok?

- On the “Dashboard” sheet how can I move the current Recommendations Adopted piece to start in column H? I have looked at the code and changed what I thought was right but unable to get it to move to this location when using the ‘Get recommendations’ button.
- ‘Make Template’ button on ‘Recommendation Template’ is working really well with consolidating the information and removing the duplicate mitigations. Is it possible to have a secondary button with a different code that just consolidates all the columns keeping the duplicates in it, so they can be seen?
- Is the checkbox on ‘OR Template’ assigned to a specific cell, as on another version I have developed when I increase the remarks column width it doesn’t retain the checkbox in column J. Is there anything that will make it stay or for me to look for?

Hi, Could I ask how you created the Pivot table and the Data sheet? I want to try some experiments with them but not 100% sure how they were created to start with or how I can amend the collection. Are you able to help with an explanation?

Hi Dflak,
Hope your job search has progressed? I have been there myself recently so know the stress.
Once again the spreadsheet has developed further the more I look at it and I’m stuck with where things need to change in the VBA code, if you can assist?
- I have had to add a few more columns into the Recommendations Template, which need to come across to the OR Template and so forth to get the correct visual result. This is due to the way it gets its recommendations as they have been broken down into subgroups, so the subgroup title needs to come forward and be displayed so you know what the recommendation is about.
- The Recommendations Template also doesn’t need to consolidate any of the repeated text, but will need to consolidate against the threat type, as a user will be looking at the recommendations across individuals threats and not multiple where duplicates were possible. Due to this it doesn’t need to take the highest score forward as it will need to be able to see the complete array of data.
- I have added 2 tabs ‘OR Template (view)’ and ‘Dashboard (view)’ so you can see visually what data needs to come across and be shown.

Is anyone able to help/assist me in making some changes to the code to achieve the outcome i'm looking for? Really appreciate your time.