Formula to compare two sets of numbers for different Invoices

**maacmaac** · 01-03-2012, 12:34 AM

I have a list of Invioce ID's in column A. For each invoice there is also two customer IDs assigned to each. I need to create relationships between invoices. For example if the customer two ID on invoice B is 120 and the customer ID on invoice D is also 120, but the customer one ID from both invoices are different, assign a new customer ID number.

I have attached a sample of data and expected results. Any assistance is greatly appreciated. Thanks.

**dilipandey** · 01-07-2012, 01:48 PM

Hi maac,

Little lengthy to understand but finally understood the logic behind but just one query which does not fit into the criteria to which others are fitting in :-

Below is the data in Row 19:-
R 18 66 38 360 987654

customer one Id is 66 and customer two id is 360 but still the "expected new number" is 987654 which should be some different number ?

Regards,
DILIPandey

<click on below 'star' if this helps>

**maacmaac** · 01-07-2012, 05:17 PM

To clarify
1. customer 61 is related to customer 340 (line 18)
2. customer 66 is related to customer 340 (line 20)
3. customer 66 is related to customer 360 (line 19)

This stands to say that if customer 66 is related to customer 360 and customer 340, then customer 61 must also be related to customer 360. As such, all 4 customers must be related, which is why they are all part of the same group. Thanks

**dilipandey** · 01-08-2012, 09:17 AM

Hi maac,

See the attached file where I have achieved the 1st level relationship.
I am seeing if I can achieve the 2nd level relationship as well, will update you once done.

Regards,
DILIPandey

<click on below 'star' if this helps>