How to Transpose via Formula?

[NicoleSager]

Hello everyone! 

I have a table with Contact IDs in column A, and portfolios in column B that are to be assigned to the contact ID. As you can see, there are duplicate contact IDs. I need to reduce the sheet where only ONE contact ID is present in column A and the portfolios assigned are transposed into columns.

My actual sheet has over 1000 contact IDs and having to manually transpose the portfolios in column B will take me a significant amount of time. I am curious if there is possibly a formula perhaps that can identify every instance where a specific ID is populated and grab the portfolio and place it in it's own column. Sort of like an expanded vlookup? Please see below sample (no confidential information shown), of what I am in need of:

Current sheet:

The output I need (column title isn't much of a concern I can easily add more, it's the transposing that is what will take a lot of effort):

Thank you!

[#2 · Accepted solution]

@NicoleSager 

If you're using Excel 365...

=DROP(
    REDUCE(
        "",
        UNIQUE(CID),
        LAMBDA(a, v, LET(filtered, FILTER(AID, CID = v), IFERROR(VSTACK(a, HSTACK(v, TOROW(filtered))), "")))
    ),
    1
)

[#3]

Wooooooww!!!!

I apologize for my unfamiliarity, but I am having trouble translating it to my actual sheet. If you have the time, would you mind explaining the steps needed to translate this? I would love to be able to understand this so that I can use it in the future. I really appreciate your help!

[#4]

Actually I believe I understand it now!!

=DROP(REDUCE("",UNIQUE(unique ID range goes here),LAMBDA(a,v,LET(filtered,FILTER(the IDs to be transposed goes here,unique id range goes here=v),IFERROR(VSTACK(a,HSTACK(v,TOROW(filtered))),"")))),1)

[#5]

@NicoleSager 

CID = Contact ID column

AID = Affiliate ID Column

=DROP(REDUCE("",UNIQUE(ContactIDColumn),LAMBDA(a,v,LET(filtered,FILTER(AffiliateIDColumn,ContactIDColumn=v),IFERROR(VSTACK(a,HSTACK(v,TOROW(filtered))),"")))),1)

REDUCE is essentially running each of the contact IDs through a FILTER.  The results are being tranposed with TOROW and then some stacking is done with VSTACK/HSTACK.

[#6]

Thank you so so much for this and for the explanation, you have saved me hours of my time.

[#7]

You're welcome! Glad to help.

The attached is taking the extra step to make this a function called 'Analyze'.  This function is stored as a named item.

=Analyze(CID,AID)