Forum Discussion
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!
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 )
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 )- 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!- 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)
