Raw Data into outline/grouping

Sallie Francis

New member
Joined
Feb 23, 2012
Messages
2
Reaction score
0
Points
0
Hi,

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
etc.
2. Create Form (UID 546 | 11 occurances)
2a Assign Form Number (UID 244 | no children
2b
2c
etc.

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
Sal.
 

Attachments

  • Sample.xls
    90 KB · Views: 18

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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?
 

Attachments

  • Sample.xls
    85.5 KB · Views: 13

Sallie Francis

New member
Joined
Feb 23, 2012
Messages
2
Reaction score
0
Points
0
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.

Sal.
 

Attachments

  • Sample.xls
    104.5 KB · Views: 14
Top