Reference List to Concatenate Categories Uniquely

[T86157]

I attached a spreadsheet to help explain what I'm trying to do..

I'm trying to come up with a formula that will display the string in range I4:I5 and expand this further down column I. The criteria is if there are matches/duplicates under List, concatenate them by associating with their Category 1 and Category 2. Not sure if this needs to be broken down into two (or three?) helper columns.

Basically trying to display a linkage relationship between two data entries. There will be no duplicates within a category. For example, there will not be two AI3, each AI will have a unique number. Same with each AV, BI and AO. There can be an AV6 and an AI6, just no duplicate numbers within that specific two letter category.

ReferenceList.xlsx

I'm trying to do this while having to avoid integrating overly massive formulas. Otherwise I guess I can break them up into chunks in the name manager...

[FDibbins]

I have no idea what you are trying to do here

How does AV2 = AI1 (I4) or AV10 = AI2 (I5)?

[T86157]

Aaaaah, haha. Sorry AV12 = AI1 and AV10 = AI2. If you look at the List names there are two MAT and two SAT but they have different associations from Category 1 and Category 2. Thanks, I think I figured it out. I ended up making helper sheets to first look for duplicate List names, then list those uniquely. Then did an index match formula to find AV and AI separately. Then concatenated them in my "final" sheet. I think that might save computation and memory? usage rather than trying to come up with a formula that would make the string from I4 and I5 all in one go...

Thanks for getting back to me on this, though.