Inter-related references ?

[theta]

Hi All,

Relatively new to VBA. Can edit it, adjust etc...but programming this has me stumped! May even use index array sumproduct formulae if needed. Overall what I am trying to do is get a VB macro or formula to check for relationships within numbers and append a unique ID. I am trying to sort out an inventory and label items that I know are truly the same item, and share references. Easier to see below :

TYPE..........ID1.....ID2.....ID3.....UNIQUE
============================================
WIDGET1.......101.....000.....000.....1
WIDGET1.......101.....000.....000.....1
WIDGET1.......101-----501.....000.....1
WIDGET2.......102-----501.....000.....1
WIDGET2.......102-----501.....000.....1
WIDGET2.......102-----502.....000.....1
WIDGET3.......000.....502-----913.....1
WIDGET4.......000.....601-----913.....1
WIDGET4.......000.....601-----913.....1
WIDGET5.......000.....000.....000.....2
WIDGET6.......102-----601.....000.....1
WIDGET6.......102-----601.....000.....1
WIDGET6.......102-----601.....000.....1
WIDGET7.......108.....000.....000.....3
WIDGET8.......108.....000.....000.....3

This would save me hours and days and weeks (on a yearly basis). Otherwise I have to sort by ID1...then give it a unique ID...then filter by ID2 and then check the unique ID given if there is an ID1. Always backtracking an open to error. Would like to build something to see the whole group based on the inter-relationships e.g. ID2 = 601 > links to ID3 913 which links to ID2 502 which links to ID1 102 which links to ID2 501 which links to ID1 101. Widget 5 has no inter-realtionships and so is unique and given its own unique ID. Widget 7 and 8 are linked to themselves only and so are given a unique ID

Arrays are not my strong suit, and so I am struggling on this. Macro is prob the best way to go?

[Cheeky Charlie]

I am sorry, I really have no idea what you're trying to achieve, could you try explaining a different way?

[theta]

Hi,

All of the products are linked by either ID1, ID2 or ID3....and all manner off cross over occures. I would like to have all related items one uinique ID number.

At present i have to sort by ID1...assign a unique ID...then sort by ID2...picking up any matches and also apply the inique ID...but then when there are overlaps I am having to back track and check all references.

Need a formula or code to provide a unique ID for all related items. WIDGET5 has not relation to anything. WIDGET6 has no other relationship other than to itself.

WIDGET1 links to another widget, which in turn links to another widget by a diff ID...so I need all 3/4 to be recognised as one as they are interlinked...

Many thanks

T

[Cheeky Charlie]

000 doesn't count then?

[theta]

000 doesn't count. Sorry I should have been more clear!

[theta]

I've tried to show it here also :

http://www.mrexcel.com/forum/showthr...=1#post2212696

[theta]

Any ideas for this anyone? Other than manually creating a (large) lookup matrix in excel?

Regards

T