SOLVED

How to Transpose via Formula?

Copper Contributor

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:

NicoleSager_0-1682536131029.png

 

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

NicoleSager_1-1682536179782.png

 

Thank you!

6 Replies
best response confirmed by NicoleSager (Copper Contributor)
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
)
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)

@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.

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

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)

Patrick2788_0-1682540224157.png

 

1 best response

Accepted Solutions
best response confirmed by NicoleSager (Copper Contributor)
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
)

View solution in original post