SOLVED

# How to Transpose via Formula?

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!

6 Replies
best response confirmed by NicoleSager (Occasional Contributor)
Solution

# Re: How to Transpose via Formula?

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
)``````

# Re: How to Transpose via Formula?

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!

# Re: How to Transpose via Formula?

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)

# Re: How to Transpose via Formula?

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.

# Re: How to Transpose via Formula?

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

# Re: How to Transpose via Formula?

``=Analyze(CID,AID)`` 