+ Reply to Thread
Results 1 to 31 of 31

Consolidation and duplication of data help

  1. #1
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Post 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,

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    You should be able to attach a sample workbook.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Post Re: Consolidation and duplication of data help

    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
    Attached Files Attached Files

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    I can see what you want to do, but I don't see how you get there.

    Walk us through an example. How do you choose which items to put into a block such as the Detect Block. It appears that the last column in a group (such as Beyond the Perimeter) comes from the OR Template sheet, but where do the other numbers come from? Also explain the significance of the colors.

    Once we have the logic, then we can figure out how to automate it.

  5. #5
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Re: Consolidation and duplication of data help

    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.

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    I think it is time for the second round of questioning.

    On the recommendations template. You fill in the cells in column C, H, M, etc. manually but you want assistance in the form of a drop down list. Is this correct?

    I unhid the columns on the OR Template: columns E:G are filled in manually. Column H is computed.

    So what you want (I think) is if I were to select cell C16 (Beyond Perimeter - Detect), I would get a drop down list limiting me to the items in Cells D7:D12 on the OR template. And If I selected M6, columns D:G would be filled in with the appropriate numbers subject to the same conditional formatting.

    If this is so, I can easily see a "brute force" solution to the issue, but I'd like to come up with a more maintainable solution.

    One more question. I noticed that some items apply to different places such as M8 is available at all locations. It so happens that it always has the same ratings (3,3,3). Is it possible that an item can have a (3,3,3) within the site but be less effective somewhere else?

  7. #7
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Re: Consolidation and duplication of data help

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

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    You were in error when you said that I was slightly confused. I was totally confused. I think I am pointed in the right direction now. Between the two workbooks I think I see what you want to do.

    The Recommendations template is the source and you want to fill out the OR template. With this data. In the first workbook I see that for Beyond the Perimeter, M3. Access Control to the Park appears both with Deter and Detect. You only want to see this once on the OR Template in the Beyond Perimeter section. This brings up a question. It is possible that the user can fill in different values in columns D, E and F on rows 11 and 15. How do you want to handle this situation when transferring the ratings to the OR Template?

    I think I have a way of doing this.

  9. #9
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Re: Consolidation and duplication of data help

    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.

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    I think I have it. Here is how it works. Fill in the Recommendations Template as far as row 50 if you want (see below if you need to extend this limit). Click on the button at the top of the OR Template sheet.

    The program goes from column to column and down the rows and builds a database as shown on the database sheet. From there it the information is summarized in a pivot table and copied / pasted onto the OR template

    You can delete the old OR Template sheets and hide the Data and Pivot sheets.

    if you need more than 50 lines on the Recommendations Template change the line indicated in this code.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Re: Consolidation and duplication of data help

    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
    Attached Files Attached Files

  12. #12
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    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.
    On which sheet are you filling in the risks?

    The title 'OR TEMPLATE: site area.... always reverts back to cell A1 if I merge the cells and centralise it.
    Where are you doing this? Merged cells are bad news. I suggest you look at "Center Across Selection" instead.

    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
    You want it sorted by top score. I think I can do this.

    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.
    This one is my bad. I did not address the issue of conditional formatting.

    I am busy today, but I should be able to look at this this week.

  13. #13
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Re: Consolidation and duplication of data help

    - 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

  14. #14
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Re: Consolidation and duplication of data help

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

  15. #15
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    I do not see an issue with the conditional formatting. It seems to be working. I did notice on thing, however. You do NOT want column B on the OR Sheet to be merged, so I commented out the line of code that did that.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Re: Consolidation and duplication of data help

    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 .

  17. #17
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    This version preserves column B. However there is no assurance that when the rest of the sheet is generated that the old values will match up with the new values.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Re: Consolidation and duplication of data help

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

  19. #19
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    Did not attach to the above.
    Attached Files Attached Files

  20. #20
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    ****. i lost my explanation. The site logs me out and then loses stuff. I'll explain tomorrow.

  21. #21
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    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.
    I do not do anything with the conditional formating on the OR Template. So whatever you set, will stay set. I recommend that you "extend" the range on the conditional formatting to cover the maximum number of rows that you can reasonably expect to be returned.

    How do I get Column G to filter each score in size order (largest to smallest) per area for easier reviewing?
    This was an easy fix. I simply sorted the pivot table that helps generate this list on the Assess value.

    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?
    This falls in the same category as the conditional formatting. Extend the validation down as far as you think you'll need it.

    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?
    I add a checkbox for each row with data in Column J and link it to the adjacent cell in Column K.

    As far as the Dashboard goes, I'm sure I can collect the adopted recommendations on this sheet. Give me a mock-up of what you would like to see.

  22. #22
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Re: Consolidation and duplication of data help

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

  23. #23
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    Do not put in your own check boxes. The program does that automatically and gives them names so they can be removed.

    Here is what I think you want.
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Re: Consolidation and duplication of data help

    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?

  25. #25
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    I was able to make it fail. You have to add something that causes an item to be added to or dropped from the OR Table.

    Only for me, it fails on .LinkedCell = "$K$" & i_O.

    I added a line to select the OR Template sheet. That seems to make the problem go away.
    Attached Files Attached Files

  26. #26
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Re: Consolidation and duplication of data help

    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?

  27. #27
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    OK. I am going to have to rewind the tape (dating myself here) in my mind on this one. It's been almost a month and a lot of things have been thrown on the stack since then. I'll have to re-familiarize myself with the program.

  28. #28
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Re: Consolidation and duplication of data help

    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?

  29. #29
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,540

    Re: Consolidation and duplication of data help

    My apologies for the late response. I found myself suddenly in the middle of a job search which is more work than when I was employed!

    In this case I think a picture is worth a thousand words. Pivot tables are created by selecting the data source and then dragging the components into the various boxes. Sometimes you will also want to play with the report layout and do things like select tabular format ans turn off subtotals, but in this case. it is straightforward. The only caveat is that, per your direction, I am showing the maximum values of these fields. The default is SUM (for numerical data) and COUNT (for non-numeric data).
    Attached Images Attached Images

  30. #30
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Re: Consolidation and duplication of data help

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

  31. #31
    Registered User
    Join Date
    04-29-2019
    Location
    Hampshire, England
    MS-Off Ver
    2018
    Posts
    43

    Re: Consolidation and duplication of data help

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Data ‘Duplication’ from one Tab into another Tab?
    By VisionSmart in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-16-2018, 09:19 AM
  2. [SOLVED] Consolidation Without Using A Function On Consolidation Screen
    By zanshin777 in forum Excel General
    Replies: 3
    Last Post: 12-27-2015, 03:35 AM
  3. [SOLVED] Consolidation several tabs' data into a consolidation sheet via a loop?
    By Gti182 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-30-2015, 08:18 AM
  4. Duplication of data
    By Aretradeser in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-26-2014, 04:28 PM
  5. [SOLVED] Check existing data before copying in new data to avoid duplication
    By lordterrin in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-06-2013, 02:15 PM
  6. Duplication data every time macro is run, instead of updating newly found data only
    By Lostinxcel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-10-2010, 03:41 AM
  7. Excel 2007 : Data duplication
    By baloch in forum Excel General
    Replies: 2
    Last Post: 03-05-2009, 12:33 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1