Identifying Unique Groups of Records from a List

[Simonc64]

OK guys this is a tough one but I have every confidence!

I am pulling a list of names from our client management system (CMS); each name will have a unique identifier, and therefore the report returns a simple 2 column list of 'identifier' and 'name'. In developing the report I want to identify any names in the CMS that are related ie siblings. Again easily done, the CMS has a relationship field of 'sibling' and therefore I now have a 4 column list of 'identifier' and 'name' for the original client and 'identifier' and 'name' for their sibling. Very simple no problem as per the example below. Assuming Cell A1 is the first ID title and cell D4 is the last Name.

ID Name ID Name
J709811 A Smith J784884 B Smith
J709811 A Smith J784892 C Smith
J709811 A Smith J732586 D Smith

Of course, because B, C and D Smith also have relationships with each other, the list continues

ID Name ID Name
J709811 A Smith J784884 B Smith
J709811 A Smith J784892 C Smith
J709811 A Smith J732586 D Smith
J784884 B Smith J709811 A Smith
J784884 B Smith J784892 C Smith
J784884 B Smith J732586 D Smith

and so on..........So now the report contains say 1500 such lines of id's and name's all with links to their respective siblings.

What I need to do however, is now identify each of the sibling groups from that list of 1500 lines/records by allocating a new unique id to each record that forms part of the sibling group ie the same unique id to A, B, C and D Smith.......and this is where Im coming unstuck and ANY advice would be gratefully received!

Thanks in advance

Simon

 

[BGY23]

Hi There,
If I understand you correctly you want to group all the Smiths together irrespective of the parent name ? Or is it only certain Smiths. If you have Smiths ABCD but want separate groups for say AB and CD then I suggest you create a lookup table with the original client ID and name and then a unique code for each client you want in a group i.e

J709811 A Smith Smith 1
J709812 B Smith Smith 1
J709813 C Smith Smith 2
J784884 D Smith Smith 2
J784884 E Smith Smith 3
J784884 F Smith Smith 3

Good luck


When you've completed the lookup tabel se a vlookup to put the unique code against the client id's and names you want in this new group.