Generate Unique ID based on multiple related rows

[dtaylor646]

Hello Everyone,

To aggregate data in a file, I need to created a unique ID to perform aggregate calculations. What I have is a unique list from column (B) of Loan IDs, and associated loans written in column (D). When there is more than one related loan, it has a comma between the two loan id's. I imagine I first want to split column (D) into (D) and (E)...but then how can I create a formula to generate a 'Generated ID' to unrelated rows...while populating the same 'Generated ID' to the related rows? Below is an example of desired result in (A).

Thanks for any and all help!

(A) Generated ID	(B) Loan (unique)	(C) Loan Name	(D) Related loan1	Related loan2
100	1	Loan #1
101	2	Loan #2
102	3	Loan #3
103	4	Loan #4	9
104	5	Loan #5
105	6	Loan #6
106	7	Loan #7
107	8	Loan #8
103	9	Loan #9	4
109	10	Loan #10
110	11	Loan #11	14, 15
111	12	Loan #12
112	13	Loan #13
110	14	Loan #14	11, 15
110	15	Loan #15	11, 14
115	16	Loan #16
116	17	Loan #17
117	18	Loan #18	19
117	19	Loan #19	18
119	20	Loan #20
				Might want to split (D) here

 

[dtaylor646]

Oh, I may have found one solution. If I move the "Generated ID" to column (F), I think this works.

=IF(ISNA(VLOOKUP(A2,$E$2:F2,2,0)),IF(ISNA(VLOOKUP(A2,$D$2:F2,3,0)),100+ROWS($F$2:F2),VLOOKUP(A2,$D$2:F2,3,0)),VLOOKUP(A2,$E$2:F2,2,0))

Does anyone know if I can have the "Generated ID" moved to (A)?