Results 1 to 3 of 3

Thread: Raw Data into outline/grouping

  1. #1

    Raw Data into outline/grouping

    Register for a FREE account, and/
    or Log in to avoid these ads!


    I have a set of raw data that is essentially linked to each other by a UID and Parent. I need to find a code that looks up a value, inserts a number of rows based on that value, copies data (with indent for grouping) and then looks up the next value, counts how many times this item appears in the list and copies data (with indent for grouping) and repeat until all complete.

    For example
    Column A (UUID) e.g. 1147
    Column B (Parent UUID) e.g. O
    Column C (Name of Parent) e.g. Main Parent
    Column D (List Value) e.g. Maintain Records
    Column E (List Value Level) e.g. 1
    Column F (List Value Children UUID) e.g. 1147
    Column G (formula to count how many children list value (D) has)) e.g. 4 (looking up how many times 1147(F) appears in Parent UUID (B)

    This Tells me the group/outline view would look something like this;

    Main Parent
    Maintain Records (UID 1147 | 2 children)
    1. Amend Form (UID 91 | 12 children)
    1a Communicate Impacts
    1b Confirm Impact Type
    2. Create Form (UID 546 | 11 occurances)
    2a Assign Form Number (UID 244 | no children

    It is this view (with proposed indents and group + and - where numbering is) I am trying to receive however numbering is not essential.

    I have racked my brain trying to work out how to get this view and have played out a couple of manipulation codes to no avail. I have also noticed that what part procedures I have come up with like looking up value in column G and inserting # of rows copying values takes forever and is only one part of the process as I would need a code to include repeat of column G formula to look up the value based on the next line.

    Anyhelp on this would be greatly appreciated. I am self taught with VBA so maybe looking at this from comletly the wrong angle. Its important to note that the full file is over 5000 lines. I have attached a sample.

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    I'm curious...

    I've modified your data a bit to pull it into a PivotTable. Now the PivotTable can be modified to make it look better, but is this a starter/non-starter for you?
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    Still Struggling

    Hi Ken,

    Thanks for your post, the change of data and use of a pivot table was a start for me and I finally got somewhere making use of your sub level column however I used numbering to show the decomposition however because of the small amount of data in the sample file I could manually add this decomposition numbering and I am wondering if you have any ideas how I can do this automatically.

    The important things are;

    Main Parent is the highest level
    The UID in column G shows us the list value UID
    The # of matches shows us the number of UID matchs in column B Parent ID
    When you look up match in parent id it shows you the next level of processes that fall under this list value. It can go all the way to 6 levels.

    Attached is an updated sample file. Thanks again for taking the time to help me on this. I usually come up with complex requirements from excel but this one is annoying.

    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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