Forum Discussion

NicoleSager's avatar
NicoleSager
Copper Contributor
Apr 26, 2023
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 redu...
excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Apr 26, 2023

    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
)
NicoleSager's avatar
NicoleSager
Copper Contributor
Apr 26, 2023
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!
NicoleSager's avatar
NicoleSager
Copper Contributor
Apr 26, 2023
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)
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Apr 26, 2023

    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.

    • NicoleSager's avatar
      NicoleSager
      Copper Contributor
      Apr 26, 2023
      Thank you so so much for this and for the explanation, you have saved me hours of my time.
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Apr 26, 2023

        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)

         

Resources