0

I need help creating a macro that will perform the following function, or something similar (I am not sure whether it is even possible to create such a macro) :

I have 60,000 + rows of data with two columns, ASSEMBLIES and COMPONENTS. An Assembly has multiple components ex:

ASSEMBLY/COMPS IMAGE

However, an Assembly can also be a component.

I need to make a tree of the relationships between assemblies and components, so basically find the highest level and then all its children assemblies and all the children of those children.

Please do let me know if there is a way I can do this as it would make things much easier!

Community
  • 1
  • 1
Shivster
  • 83
  • 1
  • 8
  • 2
    A few questions. First, is each component used in only one assembly? (I would generally not expect that to be true.) Second, I didn't understand the example above - what does ASSEMBLY/COMPS IMAGE mean? If an assembly has multiple components, are they shown on different rows of the file or somehow combined onto one row? Finally, are you able to model this in Access instead of Excel? It seems to me that it would much easier that way. – Don George May 21 '13 at 13:28
  • 1
    @DonGeorge 1)a component can be used in multiple assemblies 2)Its just an example of how one assembly can have multiple components, multiple components are shown as having the same assembly number. I essentially need something that can bring them all on one row to show the components of each assembly and see if that assembly is the component of any other assembly and thus create a cell that shows who its parent is 3)Im being asked to do it on excel 3) – Shivster May 21 '13 at 14:01
  • What do you expect the maximum depth of the tree to be? – Don George May 21 '13 at 14:13
  • @DonGeorge I have no information regarding that, some might just have a depth of one while others can be 10+ – Shivster May 21 '13 at 14:21
  • 1
    Sorry, one more question - are you looking for a new table with all of the tree information, or something that can look up a specific part number, list where it is used and generate the exploded parts list of components going into it? – Don George May 21 '13 at 14:22
  • @DonGeorge Either is okay, the second is much better but also seems much more difficult, so whatever is more manageable – Shivster May 21 '13 at 14:34
  • Can you post a sample (or a full zipped version) of the actual data somewhere? – Tim Williams May 21 '13 at 16:59

1 Answers1

0

OK, for the 2nd approach I think I would set up the following: Sheet 1 - your existing data table with 60,000 rows

Sheet 2 - "Goes into" analysis, with a specific part number in cell A1 (or wherever you want it), and cell A1 selected

A "Goes into" macro that does the following:

Starts in the current cell , increments the row number and sets an indent level of 1

Gets a list of all the matches in the data table for assemblies that the selected component goes into (see http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx#_Toc273640147)

Decrements the indent level and Ends if there are no entries in the resulting list, otherwise for each entry in the list, it puts the matching assembly number into the current cell (using the indent level to add leading spaces, or format as needed), increments the row number, increments the indent level and calls the "Goes into" macro recursively for the assembly number

This macro will basically produce an indented list of higher level assemblies for the part number placed in cell A1.

Sheet 3 - Then add another sheet for a "Contains" analysis and set it up the same way - or if you prefer, it could be on Sheet 2 in a different column. This second analysis is the same as the 1st, except that you start with list of components going into the assembly instead of a list of assemblies that the component goes into.

Don George
  • 1,328
  • 1
  • 11
  • 18
  • That sounds perfect! I think that having it in another sheet would be okay – Shivster May 21 '13 at 20:03
  • No - but when you try it yourself, if you have problems, post some specific questions along with the code you have so far. (This is a help-you-do-it site, not a do-it-for-you site.) – Don George May 24 '13 at 17:13
  • Okay, I understand. I am very new to vba/ excel scripting so I have no idea how to do basics. But thanks anyway, you showed me a new way to look at this problem, haha maybe I can work up to it someday – Shivster May 24 '13 at 17:36